RMAN CLONE ########### ## orapwd file=orapwstandby password=oracle orapwd file=orapwstddb password=oracle ### db_file_name_convert='+DG00/sanfrancisco/datafile','+DG00/standby/datafile' log_file_name_convert='+DG00/sanfrancisco/onlinelog','+DG00/standby/onlinelog' ## rman connect target sys/oracle@primary connect auxiliary sys/oracle@pridb duplicate target database to pridb from active database nofilenamecheck; ### 1. Back up the primary database. You must take a full backup and include all the archive logs as well as the control file. [oracle@linux] rman target=/ catalog rman/rman@catdb RMAN> run { allocate channel d1 type disk; backup format '/backups/PROD/df_t%t_s%s_p%p' database; backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all; release channel d1; } 2. Ensure that the source database backups are available for the duplication process. You can get a complete list of the necessary backups by running the list backup command, as shown here: RMAN>list backup; 3. Create the necessary directories for the duplicate database, and then create the initialization parameter file for the duplicate database, as shown in the following example: audit_file_dest =/oradata/AUX/adump background_dump_dest =/oradata/AUX/bdump core_dump_dest =/oradata/AUX/cdump user_dump_dest =/oradata/AUX/udump db_name ="AUX" instance_name =AUX control_files =('/oradata/AUX/control01.ctl', '/oradata/AUX/control02.ctl','/oradata/AUX/control03.ctl') #the following sets the source and target location for data files db_file_name_convert =("/oradata/PROD/", "/oradata/AUX/") #the following sets the source and target location for redo log files log_file_name_convert =("/oradata/PROD/", "/oradata/AUX/") #the following lines must be the same as those on the target instance undo_management =AUTO undo_retention =10800 undo_tablespace =UNDOTBS1 # Last update: 14-Mar-2013 # connect target / sql 'alter system archive log current'; sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''"; RUN { configure controlfile autobackup on; set command id to 'ORCLOnlineBackupFull'; ALLOCATE CHANNEL c1 DEVICE TYPE disk; ALLOCATE CHANNEL c2 DEVICE TYPE disk; ALLOCATE CHANNEL c3 DEVICE TYPE disk; ALLOCATE CHANNEL c4 DEVICE TYPE disk; backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/opt/oracle/backups/ORCL/%d_%T_%s_%p_FULL' ; sql 'alter system archive log current'; backup tag ORCL_ARCHIVE format '/opt/oracle/backups/ORCL/%d_%T_%s_%p_ARCHIVE' archivelog all delete all input ; backup tag ORCL_CONTROL current controlfile format '/opt/oracle/backups/ORCL/%d_%T_%s_%p_CONTROL'; release channel c1; release channel c2; release channel c3; release channel c4; } SQL> create table test(id number(2),name varchar2(10)); db_file_name_convert='+DG00/sanfrancisco/datafile','+DG00/standby/datafile' log_file_name_convert='+DG00/sanfrancisco/onlinelog','+DG00/standby/onlinelog' 6. Connect to the duplicate database using the keyword auxiliary through a SQL*Net connection: ## -- Show Redo Logs info set linesize 300 column REDOLOG_FILE_NAME format a50 SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#, a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME, (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a JOIN v$logfile b ON a.Group#=b.Group# ORDER BY a.GROUP# ASC; ALTER DATABASE DROP STANDBY LOGFILE GROUP 4; ### SQL> col member for a40 SQL> select group#,type, member from v$logfile; GROUP# TYPE MEMBER ---------- ------- ---------------------------------------- 3 ONLINE +DG00/primary/onlinelog/group_3.282.8859 20055 3 ONLINE +DG00/primary/onlinelog/group_3.283.8859 20057 2 ONLINE +DG00/primary/onlinelog/group_2.285.8859 20053 2 ONLINE +DG00/primary/onlinelog/group_2.284.8859 20055 GROUP# TYPE MEMBER ---------- ------- ---------------------------------------- 1 ONLINE +DG00/primary/onlinelog/group_1.287.8859 20051 1 ONLINE +DG00/primary/onlinelog/group_1.286.8859 20053 # alter database add standby logfile group 4 ('+DG00/primary/standbylog/') size 50M; alter database add standby logfile '+DG00/primary/standbylog/' group 5 size 50M alter database add standby logfile '+DG00/primary/standbylog/' group 6 size 50M alter database add standby logfile '+DG00/primary/standbylog/' group 7 size 50M ################################ alter system switch logfile; alter database drop logfile member '+DG00/primary/onlinelog/group_1.287.885920051'; ## SQL> select group#,type, member from v$logfile; GROUP# TYPE MEMBER ---------- ------- ---------------------------------------- 3 ONLINE +DG00/primary/onlinelog/group_3.282.8859 20055 2 ONLINE +DG00/primary/onlinelog/group_2.285.8859 20053 1 ONLINE +DG00/primary/onlinelog/group_1.286.8859 20053 4 STANDBY +DG00/primary/standbylog/group4.log 5 STANDBY +DG00/primary/standbylog/group5.log GROUP# TYPE MEMBER ---------- ------- ---------------------------------------- 6 STANDBY +DG00/primary/standbylog/group6.log 7 STANDBY +DG00/primary/standbylog/group7.log 7 rows selected. ##primary pfile ## [root@primary tmp]# more primary.pfile primary.__db_cache_size=301989888 primary.__java_pool_size=4194304 primary.__large_pool_size=12582912 primary.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment primary.__pga_aggregate_target=251658240 primary.__sga_target=473956352 primary.__shared_io_pool_size=0 primary.__shared_pool_size=142606336 primary.__streams_pool_size=0 #### #add fro primary db *.db_unique_name='primary' *.log_archive_config='dg_config=(primary,standby)' *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=primary' *.log_archive_dest_2='service=standby valid_for=(online_logfile,primary_role) db_unique_name=standby' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.standby_file_management='auto' *.fal_server='standby' ## *.audit_file_dest='/u01/app/oracle/admin/primary/adump' *.audit_trail='DB_EXTENDED' *.compatible='11.2.0.4.0' *.control_files='+DG00/primary/controlfile/current01.ctl','+DG00/primary/controlfile/current02.ctl' *.db_block_size=8192 *.db_create_file_dest='+DG00' *.db_domain='' *.db_name='primary' *.db_recovery_file_dest='+DG00' *.db_recovery_file_dest_size=4385144832 *.db_unique_name='primary' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=724566016 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.shared_servers=0 *.undo_tablespace='UNDOTBS1' ## [oracle@primary ~]$ rman Recovery Manager: Release 11.2.0.4.0 - Production on Tue Mar 1 12:44:29 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. RMAN> connect target/ connected to target database: PRIMARY (DBID=1723402736) RMAN> connect auxiliary sys/oracle@standby connected to auxiliary database: STANDBY (DBID=1697642564, not open) duplicate target database to 'standby' from active database spfile parameter_value_convert = '+DG00/primary','+DG00/standby' set log_file_name_convert = '+DG00/primary/onlinelog','+DG00/standby/onlinelog' db_file_name_convert = '+DG00/primary/datafile','+DG00/standby/datafile' nofilenamecheck; #### standby = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = server2.domain.com)(Port = 1541)) ) (CONNECT_DATA = (SERVICE_NAME = rac2.world)(UR=A) (SERVER = DEDICATED) ) ) ########################## [oracle@primary tmp]$ vi rmanduplicate.txt connect target / sql 'alter system archive log current'; sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''"; connect auxiliary sys/oracle@standby run { allocate channel primary1 type disk; allocate channel primary2 type disk; allocate auxiliary channel standby1 type disk; duplicate target database for standby from active database spfile parameter_value_convert = '+DG00/primary','+DG00/standby' set log_file_name_convert = '+DG00/primary/onlinelog','+DG00/standby/onlinelog' set db_file_name_convert = '+DG00/primary/datafile','+DG00/standby/datafile' set db_unique_name='standby' set standby_file_management='auto' set log_archive_config='dg_config=(primary,standby)' set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=standby' set log_archive_dest_2='service=primary valid_for=(online_logfile,primary_role) db_unique_name=primary' set log_archive_dest_state_1=enable set log_archive_dest_state_2=enable set fal_server='primary' nofilenamecheck; } # ## standby### SQL> startup pfile='/tmp/init.ora' nomount ################################################ ## SQL> SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS; ## $ nohup rman @bk.rman > rman.log 2>&1 & ## Above, the backup is interrupted by pressing Ctrl+C: ## select session_key, input_type, status, input_bytes, output_bytes, to_char(start_time, 'dd.mm.yyyy hh24:mi:ss') start_time, to_char(end_time, 'dd.mm.yyyy hh24:mi:ss') end_time, output_device_type, elapsed_seconds from v$rman_backup_job_details; # select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key; ## select sid, row_type, operation, status, to_char(start_time,'dd-mm-yyyy hh24:mi:ss') start_time, to_char(end_time,'dd-mm-yyyy hh24:mi:ss') end_time from v$rman_status where sid=7; ### SQL> select group#,type, member from v$logfile; GROUP# TYPE ---------- ------- MEMBER -------------------------------------------------------------------------------- 3 ONLINE +DG00/standby/onlinelog/group_3.292.885907083 3 ONLINE +DG00/standby/onlinelog/group_3.293.885907083 2 ONLINE +DG00/standby/onlinelog/group_2.290.885907081 GROUP# TYPE ---------- ------- MEMBER -------------------------------------------------------------------------------- 2 ONLINE +DG00/standby/onlinelog/group_2.291.885907081 1 ONLINE +DG00/standby/onlinelog/group_1.288.885907079 1 ONLINE +DG00/standby/onlinelog/group_1.289.885907079 6 rows selected. ## -- Displays Space Usage for Each Datafile. -- SET PAUSE ON SET PAUSE 'Press Return to Continue' SET PAGESIZE 60 SET LINESIZE 300 COLUMN "Tablespace Name" FORMAT A20 COLUMN "File Name" FORMAT A80 SELECT Substr(df.tablespace_name,1,20) "Tablespace Name", Substr(df.file_name,1,80) "File Name", Round(df.bytes/1024/1024,0) "Size (M)", decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)", decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)", decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used" FROM DBA_DATA_FILES DF, (SELECT file_id, sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E, (SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f WHERE e.file_id (+) = df.file_id AND df.file_id = f.file_id (+) ORDER BY df.tablespace_name, df.file_name / ### Alter database add standby logfile group 4 '+DG00/PRIMARY/STANDBYLOG/' size 50M ### Alter database add standby logfile group 4 '+DG00/STANDBY/standbylog/group4.log' size 50m; ######################################### RMAN> connect target / connected to target database: SANFRANC (DBID=1806064982) RMAN> connect auxiliary sys/oracle@standby connected to auxiliary database: STANDBY (not mounted) export ORACLE_SID=standby 7. Issue the duplicate target database command to start the database duplication process: RMAN> duplicate target database to 'standby' from active database spfile parameter_value_convert = '+DG00/primary','+DG00/standby' set log_file_name_convert = '+DG00/sanfrancisco/onlinelog','+DG00/standby/onlinelog' db_file_name_convert = '+DG00/sanfrancisco/datafile','+DG00/standby/datafile' nofilenamecheck; duplicate target database to standby from active database spfile parameter_value_convert = '+DG00/primary','+DG00/standby' set log_file_name_convert = '+DG00/primary/onlinelog','+DG00/standby/onlinelog' db_file_name_convert = '+DG00/primary/datafile','+DG00/standby/datafile' nofilenamecheck; ############################################## duplicate target database to newdb from active database spfile parameter_value_convert '/u01/app/oracle/oradata/sourcedb/','+DISK1' set db_create_file_dest = +DISK1; ### alter tablespace mybigts offline; select file_id,file_name , tablespace_name from dba_data_files order by 3; col file_name for a30; RMAN> backup as copy datafile 6 format '+DG00'; RMAN> switch datfile 6 to copy; RMAN>delete datafilecopy all device type disk; +DG00/sanfrancisco/spfileprimary.ora## standby.__db_cache_size=306184192 standby.__java_pool_size=4194304 primary.__large_pool_size=12582912 primary.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment primary.__pga_aggregate_target=251658240 primary.__sga_target=473956352 primary.__shared_io_pool_size=0 primary.__shared_pool_size=138412032 primary.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/primary/adump' *.audit_trail='DB_EXTENDED' *.compatible='11.2.0.4.0' *.control_files='+DG00/primary/controlfile/current01.ctl','+DG00/primary/controlfile/current02.ctl' *.db_block_size=8192 *.db_create_file_dest='+DG00' *.db_domain='' *.db_name='primary' *.db_recovery_file_dest='+DG00' *.db_recovery_file_dest_size=4385144832 *.db_unique_name='primary' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='' *.fal_server='standby' *.log_archive_config='dg_config=(primary,standby)' *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=primary' *.log_archive_dest_2='service=standby valid_for=(online_logfile,primary_role) db_unique_name=standby' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=724566016 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.shared_servers=0 *.standby_file_management='auto' *.undo_tablespace='UNDOTBS1'