The database I am cloning to (auxiliary) does not exist yet. The source database is production database (target) and I have copies of its most recent backup. The backup is a warm backup which I've copied from the server and placed in a location which which the duplication will call upon in the duplication process.
The destination environment has a listener which all development database register with so there are no details on setting up the listener.
This note is comprised of the following steps:
1. Create directories for auxiliary.
2. Create the pfile for auxiliary.
3. Create the service for auxiliary.
4. Create the spfile for auxiliary.
5. Adjust auxiliary so that it does not run production jobs upon opening
6. Duplicate the target to auxiliary using a recent warm backup from production
-Specify the target and destination
-Specify the databse file name conversion parameters
-Specify the source backup location from which to duplicate from
-Specify the log group
7. Disable auxiliary jobs
8. Adjust auxiliary to allow for the running of jobs after disabling the production jobs
Below are the descriptions of the <TAGS> found through the note:
<AUXILIARY_INSTANCE> - This is the name of the destination I want to restore a database into
<TARGET_INSTANCE> - This is the name of the database contained within the backup files used in the duplication process
<TARGET_INSTANCE_FOLDER> - This is the folder which holds my backup files used in the duplication process
<CONDITIONS FOR JOBS I WANT TO DISABLE> - This is a substitute for the conditions of which jobs I want to disable after the duplication. It is quite unlikely that jobs within a production database are meant to be running in a non-production environment.
1. Create directories for auxiliary.
The database will make use of the following locations.
REM Place for audits
mkdir -p E:\Oracle\admin\<AUXILIARY_INSTANCE>\adump
REM Place for datafiles
mkdir F:\Oracle\oradata\<AUXILIARY_INSTANCE>
REM Place for archivelogs
mkdir G:\Oracle\oradata\<AUXILIARY_INSTANCE>\archive
2. Create the pfile for auxiliary.
In command, open up notepad with the following command and paste the parameters into the file and save. Adjust accordingly.
notepad E:\oracle\product\11.2.0\dbhome_1\database\init<AUXILIARY_INSTANCE>.ora
*.audit_file_dest='E:\Oracle\admin\<AUXILIARY_INSTANCE>\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL01.CTL','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL02.CTL','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL03.CTL'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='<AUXILIARY_INSTANCE>'#Reset to original value by RMAN
*.diagnostic_dest='E:\Oracle'
*.dispatchers='(protocol=TCP)'
*.fixed_date='none'
*.job_queue_processes=0
*.local_listener='LISTENER_LOCAL'
*.log_archive_dest_1='LOCATION=g:\oracle\oradata\<AUXILIARY_INSTANCE>\archive'
*.log_file_name_convert='F:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>'
*.log_archive_format='ARC%S_%R.%T'
*.memory_target=1063256064
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.shared_servers=2
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='*'
*NOTE: The destination machine has a listener titled "LISTENER_LOCAL" that services all the instance on the machine. This is what *.LOCAL_LISTENER is all about.
This tells the AUXILIARY database "When you startup - I want you to register yourself with the listener on this machine titled LISTENER_LOCAL". If the environment doesn't have a common listener which instance can register with, then a static entry must be made in the listener.ora file for our new database - I will not cover this here.
*NOTE: Also note that you should have the *.log_file_name_convert parameter in your parameter list, especially if the target and auxiliary are on the same server. Should db_file_name_convert be omitted in both parameter and RMAN script, RMAN will restore the BK files as was and try to write the datafiles back to the location of the source. Datafiles about to be overwritten will halt the duplication process and RMAN will warn you that the destination files are in use by another instance, but this is not the case for redolog files. Duplicate database has, for me in the past, accidentally overwritten the source database redologs if one plays fast and loose with the details of the log file name conversion parameter - the duplicate command will default to the target databases log file location during control file creation if an administrator has not specified log conversion in some form or another.
3. Create the service for auxiliary.
I create the Windows service to represent the new instance.
set ORACLE_SID=<AUXILIARY_INSTANCE>
set ORACLE_HOME=E:\oracle\product\11.2.0\dbhome_1
oradim -new -SID <AUXILIARY_INSTANCE> -startmode manual -pfile 'E:\oracle\product\11.2.0\dbhome_1\database\init<AUXILIARY_INSTANCE>.ora'
OUTPUT
C:\Windows\system32>set ORACLE_SID=<AUXILIARY_INSTANCE>
C:\Windows\system32>set ORACLE_HOME=E:\oracle\product\11.2.0\dbhome_1
C:\Windows\system32>oradim -new -SID <AUXILIARY_INSTANCE> -startmode manual -pfile 'E:\oracle\product\11.2.0\dbhome_1\database\init<AUXILIARY_INSTANCE>.ora'
Instance created.
4. Create the spfile for auxiliary.
Create an spfile from the pfile used to start the database service.
create spfile from pfile;
OUTPUT
C:\Windows\system32>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 17 08:57:50 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL>
5. Adjust auxiliary so that it does not run production jobs upon opening in the development environment
alter system set job_queue_processes=0 scope=spfile;
OUTPUT
C:\Windows\system32>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 17 09:06:20 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1060585472 bytes
Fixed Size 2261960 bytes
Variable Size 612371512 bytes
Database Buffers 440401920 bytes
Redo Buffers 5550080 bytes
SQL> alter system set job_queue_processes=0 scope=spfile;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
C:\Windows\system32>
6. Duplicate the target to auxiliary using a recent warm backup from production
This is the part where the disks are put to work.
Because I am not duplicating directly from the target database but rather duplicating from a backup (datafiles, control files, archivelogfiles), I do not specify the TARGET parameter when connecting to RMAN. I also do not specify NOCATALOG because RMAN will default to NOCATALOG mode when catalog information is not specific in the RMAN connection command.
DB_FILE_NAME_CONVERT is used to automatically rename the restored backup files to a new location: considering the auxiliary environment is NOT the production environment and the drives are not the same, I have provided the convert to suit the destination environments layout.
BACKUP LOCATION is a neat little parameter that tells RMAN where to look for the backup files of TARGET. This does away with the need to CATALOG the backup pieces prior to duplication.
LOGFILE tells RMAN where to create the redo logfiles and their sizes.
rman auxiliary /
run {
DUPLICATE DATABASE <TARGET_DATABASE> to "<AUXILIARY_INSTANCE>"
DB_FILE_NAME_CONVERT='G:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','H:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','E:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','D:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','F:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>'
BACKUP LOCATION='C:\BACKUP\<TARGET_INSTANCE_FOLDER>'
LOGFILE
'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo01.log' size 10M,
'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo02.log' size 10M,
'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo03.log' size 10M;
}
exit
OUTPUT
RMAN> run {
2> DUPLICATE DATABASE <TARGET_DATABASE> to "<AUXILIARY_INSTANCE>"
3> DB_FILE_NAME_CONVERT='G:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','H:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','E:\ORACLE\ORADATA\G
DATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>','F:\ORACLE\ORADATA\<TARGET_DATABASE>','F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>'
4> BACKUP LOCATION='C:\BACKUP\<TARGET_INSTANCE_FOLDER>'
5> LOGFILE
6> 'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo01.log' size 10M,
7> 'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo02.log' size 10M,
8> 'F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\redo03.log' size 10M;
9> }
Starting Duplicate Db at 17-JUN-16
contents of Memory Script:
{
sql clone "alter system set db_name =
''<TARGET_DATABASE>'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''<AUXILIARY_INSTANCE>'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from 'C:\BACKUP\<TARGET_INSTANCE_FOLDER>\<TARGET_DATABASE>_BRR7VP4C_1_1.RMAN';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''<TARGET_DATABASE>'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''<AUXILIARY_INSTANCE>'' comment= ''Modified by RMAN duplicate'' scope=spfile
executing Memory Script
sql statement: alter system set db_name = ''<TARGET_DATABASE>'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''<AUXILIARY_INSTANCE>'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1060585472 bytes
Fixed Size 2261960 bytes
Variable Size 612371512 bytes
Database Buffers 440401920 bytes
Redo Buffers 5550080 bytes
Starting restore at 17-JUN-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL01.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL02.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL03.CTL
Finished restore at 17-JUN-16
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 device type=DISK
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL01.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL02.CTL
output file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\CONTROL03.CTL
Finished restore at 17-JUN-16
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=129 device type=DISK
........................................................
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\USERS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00005 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FINDATADM1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00006 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FINDATAUDIT1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00007 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FINDATINT1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00008 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FININDADM1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00009 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\FININDINT1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00010 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\AUDIT_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00011 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATADMD1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00012 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATADMD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00013 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATADMS_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00014 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINTERFACE1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00015 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATOPSD1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00016 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATOPSD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00017 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPDATOPSS_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00018 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDADMD1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00019 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDADMD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00020 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDADMS_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00021 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDINTERFACE1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00022 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD1_1.DBF
channel ORA_AUX_DISK_1: restoring datafile 00023 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD2_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00024 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSS.DBF
channel ORA_AUX_DISK_1: restoring datafile 00025 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPUSERS1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00026 to F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\UNDOTBS02.DBF
channel ORA_AUX_DISK_1: reading from backup piece C:\BACKUP\<TARGET_INSTANCE_FOLDER>\<TARGET_DATABASE>_BQR7VNJU_1_1.RMAN
......................
input datafile copy RECID=20 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDINTERFACE1_01.DBF
datafile 22 switched to datafile copy
input datafile copy RECID=21 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD1_1.DBF
datafile 23 switched to datafile copy
input datafile copy RECID=22 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSD2_01.DBF
datafile 24 switched to datafile copy
input datafile copy RECID=23 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPINDOPSS.DBF
datafile 25 switched to datafile copy
input datafile copy RECID=24 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\GPUSERS1_01.DBF
datafile 26 switched to datafile copy
input datafile copy RECID=25 STAMP=914752023 file name=F:\ORACLE\ORADATA\<AUXILIARY_INSTANCE>\UNDOTBS02.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 17-JUN-16
RMAN>
While the database is restoring, I can monitor the duplication progress using v$session_longops using another database session. The RMAN process registers its block restoration effort in the v$session_longops dynamic view. How handy is that. In another cmd window, I run the following connected to the auxiliary instance
select message from v$session_longops;
OUTPUT
E:\oracle\glassfish4\bin>set ORACLE_SID=<AUXILIARY_INSTANCE>
E:\oracle\glassfish4\bin>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jun 17 09:43:06 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select message from v$session_longops;
MESSAGE
--------------------------------------------------------------------------------
RMAN: aggregate input: restore 421: 4915871 out of 0 Blocks done
RMAN: full datafile restore: Set Count 11642: 5212774 out of 5744384 Blocks done
SQL> /
MESSAGE
--------------------------------------------------------------------------------
RMAN: aggregate input: restore 421: 5260134 out of 0 Blocks done
RMAN: full datafile restore: Set Count 11642: 5306598 out of 5744384 Blocks done
SQL> /
MESSAGE
--------------------------------------------------------------------------------
RMAN: aggregate input: restore 421: 5260134 out of 0 Blocks done
RMAN: full datafile restore: Set Count 11642: 5319142 out of 5744384 Blocks done
SQL>
See how the block count increases, this is my proverbial progress bar for observation
7. Disable auxiliary jobs
Here I disable the production jobs before re-enabling the job parameter.
conn / as sysdba
BEGIN
FOR r_jobs IN (SELECT job
FROM dba_jobs
WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
)
LOOP
DBMS_JOB.BROKEN(r_jobs.job,TRUE);
END LOOP;
FOR r_scheduler_jobs IN (SELECT owner,
job_name
FROM SYS.dba_scheduler_jobs
WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
)
LOOP
DBMS_SCHEDULER.DISABLE('"' || r_scheduler_jobs.owner || '"."' || r_scheduler_jobs.job_name || '"',TRUE);
END LOOP;
END;
/
OUTPUT
SQL> BEGIN
2 FOR r_jobs IN (SELECT job
3 FROM dba_jobs
4 WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
5 )
6 LOOP
7 DBMS_JOB.BROKEN(r_jobs.job,TRUE);
8 END LOOP;
9
10 FOR r_scheduler_jobs IN (SELECT owner,
11 job_name
12 FROM SYS.dba_scheduler_jobs
13 WHERE <CONDITIONS FOR JOBS I WANT TO DISABLE>
14 )
15 LOOP
16 DBMS_SCHEDULER.DISABLE('"' || r_scheduler_jobs.owner || '"."' || r_scheduler_jobs.job_name || '"',TRUE);
17 END LOOP;
18
19 END;
20 /
PL/SQL procedure successfully completed.
SQL> select owner, job_name, enabled from dba_scheduler_jobs where enabled = 'TRUE';
OWNER JOB_NAME ENABL
------------------------------ ------------------------------ -----
SYS PURGE_LOG TRUE
SYS ORA$AUTOTASK_CLEAN TRUE
SYS DRA_REEVALUATE_OPEN_FAILURES TRUE
ORACLE_OCM MGMT_CONFIG_JOB TRUE
ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE
SYS BSLN_MAINTAIN_STATS_JOB TRUE
SYS RSE$CLEAN_RECOVERABLE_SCRIPT TRUE
SYS SM$CLEAN_AUTO_SPLIT_MERGE TRUE
EXFSYS RLM$EVTCLEANUP TRUE
EXFSYS RLM$SCHDNEGACTION TRUE
SYS AUDIT_PURGE TRUE
11 rows selected.
SQL>
Only the system jobs are left running.
8. Adjust auxiliary to allow for the runnign of jobs after disabling the production jobs
Now that the jobs are disabled, I can go ahead and allow the system to run jobs again.
alter system set job_queue_processes=5 scope=both;
OUTPUT
SQL> alter system set job_queue_processes=5 scope=both;
System altered.
SQL>
All done.
No comments:
Post a Comment