Search This Blog

Total Pageviews

Tuesday 28 November 2017

Rman active duplicate on same host

Rman  active duplicate on same host ...


Rman  active duplicate on same host from vihcdb1 to vihcdb2 database 


vihcdb1:/u01/app/oracle/product/12.1.0/db_2:N ------>>>> from 
vihcdb2:/u01/app/oracle/product/12.1.0/db_2:N To <<<<<----- 


Primary database   ( vihcdb1 )
Duplicate database ( vihcdb2 )


Must have password file 

http://anuj-singh.blogspot.co.uk/2012/04/how-to-create-oracle-password-file.html


[oracle@cloud-ora dbs]$ pwd
/u01/app/oracle/product/12.1.0/db_2/dbs

[oracle@cloud-ora dbs]$ ls -ltr ora*
-rw-r----- 1 oracle oinstall 6144 Nov 22 06:17 orapwvihcdb2
-rw-r----- 1 oracle oinstall 6144 Nov 23 06:41 orapwvihcdb1


Imp !!
Primary database ( vihcdb1 ) must be ruuning from spfile !!!!!!

SQL> def
DEFINE _DATE           = "23-NOV-17" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "vihcdb1" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE      = "1202000100" (CHAR)


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 /db_2/dbs/spfilevihcdb1.ora
For DB_FILE_NAME_CONVERT parameter run following sql and set value accordingly for duplicate database

select  distinct substr(name, 1, instr(name, '/',-1)) PATH 
                       from  (
                       select name  from v$datafile
                       union all
                       select NAME  from v$controlfile
                       union all
                       select MEMBER  name from v$logfile
                       union all
                       select name from v$tempfile
) order by 1;                   

PATH
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/vihcdb1/ <<<<<<<<<---- 
/u01/app/oracle/oradata/vihcdb1/ANUJ/
/u01/app/oracle/oradata/vihcdb1/pdbseed/



Parameter file for primary database .                                                                                         For Duplicate database 
                                                                                                                              Create all the dir !!!!

cat initvihcdb1.ora                                                                                                          cat initvihcdb2.ora
vihcdb1.__data_transfer_cache_size=0                                                                                         vihcdb2.__data_transfer_cache_size=0
vihcdb1.__db_cache_size=289406976                                                                                            vihcdb2.__db_cache_size=289406976
vihcdb1.__inmemory_ext_roarea=0                                                                                              vihcdb2.__inmemory_ext_roarea=0
vihcdb1.__inmemory_ext_rwarea=0                                                                                              vihcdb2.__inmemory_ext_rwarea=0
vihcdb1.__java_pool_size=4194304                                                                                             vihcdb2.__java_pool_size=4194304
vihcdb1.__large_pool_size=8388608                                                                                            vihcdb2.__large_pool_size=8388608
vihcdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment                                                     vihcdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
vihcdb1.__pga_aggregate_target=109051904                                                                                     vihcdb2.__pga_aggregate_target=109051904
vihcdb1.__sga_target=536870912                                                                                               vihcdb2.__sga_target=536870912
vihcdb1.__shared_io_pool_size=20971520                                                                                       vihcdb2.__shared_io_pool_size=20971520
vihcdb1.__shared_pool_size=197132288                                                                                         vihcdb2.__shared_pool_size=197132288
vihcdb1.__streams_pool_size=0                                                                                                vihcdb2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/vihcdb1/adump'                                                                      *.audit_file_dest='/u01/app/oracle/admin/vihcdb2/adump'
*.audit_trail='DB'                                                                                                           *.audit_trail='DB'
*.compatible='12.0.0'                                                                                                        *.compatible='12.0.0'
*.control_files='/u01/app/oracle/oradata/vihcdb1/ora_control1.ctl','/u01/app/oracle/oradata/vihcdb1/ora_control2.ctl'        *.control_files='/u01/app/oracle/oradata/vihcdb2/ora_control1.ctl','/u01/app/oracle/oradata/vihcdb2/ora_control2.ctl'
*.db_block_size=8192                                                                                                         *.db_block_size=8192
*.db_domain=''                                                                                                               *.db_domain=''
*.db_name='vihcdb1'                                                                                                          *.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest_size=4294967296                                                                                      *.db_recovery_file_dest='/u01/app/oracle/vihcdb2'
*.db_recovery_file_dest='/u01/app/oracle/vihcdb1'                                                                            *.db_securefile='PREFERRED'
*.db_securefile='PREFERRED'                                                                                                  *.db_name='vihcdb2'
*.db_unique_name='vihcdb1'                                                                                                   *.db_unique_name='vihcdb2'
*.diagnostic_dest='/u01/app/oracle'                                                                                          *.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=VIHCDBXDB)'                                                                           *.dispatchers='(PROTOCOL=TCP) (SERVICE=VIHCDBXDB)'
*.enable_pluggable_database=TRUE                                                                                             *.enable_pluggable_database=TRUE
vihcdb1.local_listener='(address=(protocol=tcp)(host=001.18.114.253)(port=1521))'                                            vihcdb2.local_listener='(address=(protocol=tcp)(host=001.18.114.253)(port=1521))'
*.log_archive_dest_1='location=/u01/app/Archive_Vihcdb1'                                                                     *.log_archive_dest_1='location=/u01/app/Archive_Vihcdb2'
*.open_cursors=1000                                                                                                          *.open_cursors=1000
*.processes=300                                                                                                              *.processes=300
*.remote_login_passwordfile='EXCLUSIVE'                                                                                      *.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912                                                                                                       *.sga_target=536870912
*.undo_tablespace='UNDOTBS1'                                                                                                 *.undo_tablespace='UNDOTBS1'


Create  script for rman
  
DUPLICATE TARGET DATABASE TO VIHCDB2 FROM ACTIVE DATABASE 
SPFILE PARAMETER_VALUE_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2' 
SET DB_FILE_NAME_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2' 
SET LOG_FILE_NAME_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2' 
SET DB_CREATE_FILE_DEST '/u01/app/oracle/oradata/vihcdb2' 
set db_create_online_log_dest_1 '/u01/app/oracle/oradata/vihcdb2' 
NOFILENAMECHECK; 



vihcdb1:/u01/app/oracle/product/12.1.0/db_2:N
vihcdb2:/u01/app/oracle/product/12.1.0/db_2:N
[oracle@cloud-ora dbs]$ . oraenv
ORACLE_SID = [vihcdb1ora] ? vihcdb2

Imp !!
This database must not running from spfile (Duplicate One ) 

/u01/app/oracle/product/12.1.0/db_2/dbs

[oracle@cloud-ora dbs]$ ls -ltr init*
-rw-r----- 1 oracle oinstall 1256 Nov 22 01:44 initvihcdb2.ora


[oracle@cloud-ora dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 23 06:30:41 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount ;
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  8794848 bytes
Variable Size             306187552 bytes
Database Buffers          213909504 bytes
Redo Buffers                7979008 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64


rman target sys/vihaan*23@001.18.114.253:1521/vihcdb1 auxiliary sys/vihaan*23 |tee  /tmp/rman_clone_vihcdb1.log


[oracle@cloud-ora dbs]$  rman target sys/vihaan*23@001.18.114.253:1521/vihcdb1 auxiliary sys/vihaan*23 |tee  /tmp/rman_clone_vihcdb1.log

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Nov 23 06:32:24 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: VIHCDB1 (DBID=2963737589)
connected to auxiliary database: VIHCDB2 (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO VIHCDB2 FROM ACTIVE DATABASE
2> SPFILE PARAMETER_VALUE_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2'
3> SET DB_FILE_NAME_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2'
4> SET LOG_FILE_NAME_CONVERT '/u01/app/oracle/oradata/vihcdb1', '/u01/app/oracle/oradata/vihcdb2'
5> SET DB_CREATE_FILE_DEST '/u01/app/oracle/oradata/vihcdb2'
6> set db_create_online_log_dest_1 '/u01/app/oracle/oradata/vihcdb2'
7> NOFILENAMECHECK;

Starting Duplicate Db at 23-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=254 device type=DISK
current log archived

contents of Memory Script:
{
   restore clone from service  '001.18.114.253:1521/vihcdb1' spfile to
 '/u01/app/oracle/product/12.1.0/db_2/dbs/spfilevihcdb2.ora';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_2/dbs/spfilevihcdb2.ora''";
}
executing Memory Script

Starting restore at 23-NOV-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/product/12.1.0/db_2/dbs/spfilevihcdb2.ora
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 23-NOV-17

sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0/db_2/dbs/spfilevihcdb2.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''VIHCDB2'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''VIHCDB2'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  control_files =
 ''/u01/app/oracle/oradata/vihcdb2/ora_control1.ctl'', ''/u01/app/oracle/oradata/vihcdb2/ora_control2.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u01/app/oracle/oradata/vihcdb1'', ''/u01/app/oracle/oradata/vihcdb2'' comment=
 '''' scope=spfile";
   sql clone "alter system set  LOG_FILE_NAME_CONVERT =
 ''/u01/app/oracle/oradata/vihcdb1'', ''/u01/app/oracle/oradata/vihcdb2'' comment=
 '''' scope=spfile";
   sql clone "alter system set  DB_CREATE_FILE_DEST =
 ''/u01/app/oracle/oradata/vihcdb2'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_online_log_dest_1 =
 ''/u01/app/oracle/oradata/vihcdb2'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''VIHCDB2'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''VIHCDB2'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/u01/app/oracle/oradata/vihcdb2/ora_control1.ctl'', ''/u01/app/oracle/oradata/vihcdb2/ora_control2.ctl'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u01/app/oracle/oradata/vihcdb1'', ''/u01/app/oracle/oradata/vihcdb2'' comment= '''' scope=spfile

sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ''/u01/app/oracle/oradata/vihcdb1'', ''/u01/app/oracle/oradata/vihcdb2'' comment= '''' scope=spfile

sql statement: alter system set  DB_CREATE_FILE_DEST =  ''/u01/app/oracle/oradata/vihcdb2'' comment= '''' scope=spfile

sql statement: alter system set  db_create_online_log_dest_1 =  ''/u01/app/oracle/oradata/vihcdb2'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     8794848 bytes
Variable Size                306187552 bytes
Database Buffers             213909504 bytes
Redo Buffers                   7979008 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''VIHCDB1'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''VIHCDB2'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  '001.18.114.253:1521/vihcdb1' primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''VIHCDB1'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''VIHCDB2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     8794848 bytes
Variable Size                306187552 bytes
Database Buffers             213909504 bytes
Redo Buffers                   7979008 bytes

Starting restore at 23-NOV-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=254 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/vihcdb2/ora_control1.ctl
output file name=/u01/app/oracle/oradata/vihcdb2/ora_control2.ctl
Finished restore at 23-NOV-17

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/vihcdb2/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/vihcdb2/users01.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf";
   set newname for datafile  8 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf";
   set newname for datafile  9 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf";
   set newname for datafile  10 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf";
   set newname for datafile  11 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf";
   set newname for datafile  12 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf";
   set newname for datafile  14 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf";
   set newname for datafile  15 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf";
   restore
   from service  '001.18.114.253:1521/vihcdb1'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 23-NOV-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/vihcdb2/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/vihcdb2/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/vihcdb2/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:10
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/vihcdb2/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:19
Finished restore at 23-NOV-17

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  '001.18.114.253:1521/vihcdb1'
           archivelog from scn  3240340;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 23-NOV-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=220
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service 209.18.114.253:1521/vihcdb1
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=221
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 23-NOV-17

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=21 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=22 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=23 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=24 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=25 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=26 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=27 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=28 STAMP=960791790 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf

contents of Memory Script:
{
   set until scn  3240505;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 23-NOV-17
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 220 is already on disk as file /u01/app/Archive_Vihcdb1/1_220_955461173.dbf
archived log for thread 1 with sequence 221 is already on disk as file /u01/app/Archive_Vihcdb1/1_221_955461173.dbf
archived log file name=/u01/app/Archive_Vihcdb1/1_220_955461173.dbf thread=1 sequence=220
archived log file name=/u01/app/Archive_Vihcdb1/1_221_955461173.dbf thread=1 sequence=221
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-NOV-17
Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     8794848 bytes
Variable Size                306187552 bytes
Database Buffers             213909504 bytes
Redo Buffers                   7979008 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''VIHCDB2'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set  db_name =  ''VIHCDB2'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     8794848 bytes
Variable Size                306187552 bytes
Database Buffers             213909504 bytes
Redo Buffers                   7979008 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "VIHCDB2" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES     1
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u01/app/oracle/oradata/vihcdb2/redo01a.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u01/app/oracle/oradata/vihcdb2/redo02a.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u01/app/oracle/oradata/vihcdb2/redo03a.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/vihcdb2/system01.dbf',
  '/u01/app/oracle/oradata/vihcdb2/pdbseed/system01.dbf',
  '/u01/app/oracle/oradata/vihcdb2/ANUJ/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/vihcdb2/temp01.dbf";
   set newname for tempfile  2 to
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/temp01.dbf";
   set newname for tempfile  3 to
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/users01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf",
 "/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/vihcdb2/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/vihcdb2/pdbseed/temp01.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/vihcdb2/ANUJ/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf RECID=1 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf RECID=2 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf RECID=3 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/users01.dbf RECID=4 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf RECID=5 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf RECID=6 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf RECID=7 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf RECID=8 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf RECID=9 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf RECID=10 STAMP=960791822
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf RECID=11 STAMP=960791822

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/sysaux01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=6 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/pdbseed/usertbs01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=7 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=8 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/users01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=9 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/usertbs01.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=10 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/scott01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=11 STAMP=960791822 file name=/u01/app/oracle/oradata/vihcdb2/ANUJ/master01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
   sql clone "alter pluggable database all open";
}
executing Memory Script

sql statement: alter pluggable database all open
Finished Duplicate Db at 23-NOV-17

RMAN>


***************************************************************************************************************

Check for temp file 

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Nov 23 07:15:03 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> def
DEFINE _DATE           = "23-NOV-17" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "vihcdb2" (CHAR)  <<<<<<<<<----- 
DEFINE _USER           = "SYS" (CHAR)


Now database is running spfile . 

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 /db_2/dbs/spfilevihcdb2.ora


             
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/Archive_Vihcdb1  <<<<<<If destination is old data then change it
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1



SQL> alter system set log_archive_dest_1='location=/u01/app/Archive_Vihcdb2' scope=both;

System altered.


SQL>  archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/Archive_Vihcdb2
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1



Create temp file if required 

SQL> 
set numf 999.99 linesize 200 pagesize 200
col FILE_NAME format a50
SELECT tablespace_name, file_name, bytes/1024/1024/1024 Gb FROM dba_temp_files WHERE tablespace_name like '%TEMP%' ;

                                                                *
ERROR at line 1:
ORA-01187: cannot read from file  because it failed verification tests
ORA-01110: data file 201: '/u01/app/oracle/oradata/vihcdb2/temp01.dbf'

alter database tempfile '/u01/app/oracle/oradata/vihcdb2/temp01.dbf' drop;

SQL> alter database tempfile '/u01/app/oracle/oradata/vihcdb2/temp01.dbf' drop;

Database altered.

SQL>

 select TABLESPACE_NAME from dba_tablespaces where CONTENTS='TEMPORARY' ;

TABLESPACE_NAME
------------------------------
TEMPTS


set long 5000 linesize 300 pagesize 200
select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb  where CONTENTS='TEMPORARY' ;

DBMS_METADATA.GET_DDL('TABLESPACE',TB.TABLESPACE_NAME)
--------------------------------------------------------------------------------

  CREATE TEMPORARY TABLESPACE "TEMPTS" TEMPFILE
  '/u01/app/oracle/oradata/vihcdb2/temp01.dbf' SIZE 1073741824
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576



ALTER TABLESPACE TEMPTS add tempfile '/u01/app/oracle/oradata/vihcdb2/temp01.dbf' size 1g reuse ;

SQL> ALTER TABLESPACE TEMPTS add tempfile '/u01/app/oracle/oradata/vihcdb2/temp01.dbf' size 1g reuse ;

Tablespace altered.



SQL> DROP TABLESPACE TEMPTS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TEMPTS INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

         


set linesize 300 pagesize 500  verify off
col tablespace      for a29             heading 'tablespace name / file class'
col filename        for a64             heading 'filename'
col filesize        for 99,999,999,999  heading 'file size'
col autoextensible  for a4              heading 'auto'
col increment_by    for 99,999,999,999  heading 'next'
col maxbytes        for 99,999,999,999  heading 'max'
break on report
compute sum of filesize on report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM sys.dba_data_files d , v$datafile v , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
WHERE (d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace 
, d.file_name filename
, d.bytes filesize
, d.autoextensible autoextensible
, d.increment_by * e.value increment_by
, d.maxbytes maxbytes
FROM sys.dba_temp_files d , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e
UNION
SELECT '[ ONLINE REDO LOG ]' , a.member , b.bytes , null , TO_NUMBER(null), TO_NUMBER(null) FROM v$logfile a , v$log b
WHERE a.group# = b.group#
UNION
SELECT '[ CONTROL FILE ]' , a.name , TO_NUMBER(null) , null , TO_NUMBER(null), TO_NUMBER(null)
FROM v$controlfile a ORDER BY 1,2
/



Tablespace Name / File Class  Filename                                                               File Size Auto            Next             Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
SYSAUX                        /u01/app/oracle/oradata/vihcdb2/sysaux01.dbf                         608,174,080 YES       10,485,760  34,359,721,984
SYSTEM                        /u01/app/oracle/oradata/vihcdb2/system01.dbf                         734,003,200 YES       10,485,760  34,359,721,984
TEMPTS                        /u01/app/oracle/oradata/vihcdb2/temp01.dbf                         1,073,741,824 NO                 0               0
UNDOTBS1                      /u01/app/oracle/oradata/vihcdb2/undotbs01.dbf                        466,616,320 YES        5,242,880  34,359,721,984
USERS                         /u01/app/oracle/oradata/vihcdb2/users01.dbf                          524,288,000 YES            8,192  34,359,721,984
[ CONTROL FILE ]              /u01/app/oracle/oradata/vihcdb2/ora_control1.ctl
[ CONTROL FILE ]              /u01/app/oracle/oradata/vihcdb2/ora_control2.ctl
[ ONLINE REDO LOG ]           /u01/app/oracle/oradata/vihcdb2/redo01a.log                           52,428,800
[ ONLINE REDO LOG ]           /u01/app/oracle/oradata/vihcdb2/redo02a.log                           52,428,800
[ ONLINE REDO LOG ]           /u01/app/oracle/oradata/vihcdb2/redo03a.log                           52,428,800
                                                                                               ---------------
sum                                                                                              3,564,109,824

10 rows selected.

sqlplus command prompt (sqlprompt)

sqlplus command prompt (sqlprompt) ... 


Useful for Standby 

SET TERMOUT OFF 
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT '[-'||sys_context('USERENV', 'DATABASE_ROLE')||'-]'||LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON
output .. 
[-PRIMARY-]sys@vihcdb1>

 set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> "
===
Sqlprompt with colour !!!!!!!!!
with colour

SET TERMOUT OFF 
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
--select   chr(27)||'[32m'||chr(27)||'[1'||to_char(0)||'m'|| --GREEN  COLOR
select   chr(27)||'[33m'||chr(27)||'[1'||to_char(0)||'m'|| --YELLOW COLOR
--SELECT  chr(27)||'[35m'||chr(27)||'[1'||to_char(0)||'m'|| --PINK   COLOR
 '[-'||sys_context('USERENV', 'DATABASE_ROLE')||'-]'||LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON

=====



for putty title 

 PROMPT_COMMAND='printf "\033]0;%s@%s - %s\007" "`whoami`" "`hostname`" "`uname -s -r`"'

PROMPT_COMMAND='printf "\033]0;%s@%s - %s\007" "`whoami`" "`hostname`" "`uptime`"'


-- bold yellow color prompt 
export PS1="\e[0;33;1m[\u@\h \W]\$ \e[m"

export PS1="\e[0;33;1m[\u\e[m@\e[0;31;1m\h\e[m \W]\$"


export PS1="\e[0;33;97m[\u\e[m@\e[0;31;1m\h\e[m \W]\$"


                     Red                 Green
$export PS1="\e[0;31;31m[\u\e[m@\e[0;31;92m\h\e[m \W]\$"
  
  Red  @ Green 
[oracle@irac01 ~]$



30 Black | 31 Red | 32 Green | 33 Yellow | 34 Blue| 35 Magenta| 36 Cyan| 37 Light gray |90 Dark gray |91 Light red |92 Light green |93 Light yellow |94 Light blue |95 Light magenta |96 Light cyan |97 White


Changing Color of Linux Terminal Directory and Dir
PS1="\\[\\e]0;\\u@\\h:\\w\\a\\]\\[\\e[1;32m\\]\\u@\\h\\[\\e[1;34m\\] [\\w] \\\$\\[\\e[0m\\] "


export PS1="\[\e[32m\][\[\e[m\]\[\e[31m\]\u\[\e[m\]\[\e[33m\]@\[\e[m\]\[\e[32m\]\h\[\e[m\]:\[\e[36m\]\w\[\e[m\]\[\e[32m\]]\[\e[m\]\[\e[32;47m\]\\$\[\e[m\] "

Oracle DBA

anuj blog Archive