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.oraFor 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 rmanDUPLICATE 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.
Tuesday, 28 November 2017
Rman active duplicate on same host
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\] "
Subscribe to:
Posts (Atom)
