Creating a Physical Standby database using RMAN restore database from service
In this post I am sharing step by step method for instantiating a standby database using the RMAN “from service” clause to copy directly from the primary database through an Oracle Net connection.
- This method is valid for Oracle Database Server versions 12.1 to 19c and higher.
- SECTION SIZE support is available. The section size clause used with multiple RMAN channels enables parallelization of individual files by dividing large files into smaller pieces. This improves the overall efficiency of parallelization across channels.
- Encryption is supported.
- Compression is supported.
The RMAN ‘from service’ clause enables the restore and recover of primary database files to a standby database across the network. This functionality can be used to instantiate a standby database in lieu of the RMAN DUPLICATE DATABASE command and is more intuitive and less error prone thus saving time.
This ‘FROM SERVICE‘ method can be used to restore or recover an entire database, individual data files, control files, server parameter file, or tablespaces. This method is useful in synchronizing the primary and standby database.
Hosts and Databases Used in this Example | ||
Primary | Standby | |
Hosts | Funebsprim | Funebssdty |
Database Unique Name | BEE | BEE_CL |
Steps:
1) Put Primary Database in force logging mode
Database force logging is recommended so that all changes to the primary database are replicated to the standby database regardless of NOLOGGING settings. To enable force logging, use the following command on the primary:
[oracle@funebsprim]$ sqlplus / as sysdba
SQL> alter database force logging;
SQL>exit
2) On Primary Create Standby Redologs
Create standby redo logs on the primary database that are the exact same size as the online redo logs. Standby Redo Logs enable real time redo apply where the redo is applied as it is received rather than when a complete archived log is received. This improves standby currency and reduces potential data loss.
Check existing redo log members and their sizes.
[oracle@funebsprim]$ sqlplus / as sysdba
SQL> select thread#,group#,bytes,status from v$log;
THREAD# GROUP# BYTES STATUS
---------- ---------- -------------- ----------------
1 1 4294967296 CURRENT
1 2 4294967296 UNUSED
1 3 4294967296 UNUSED
2 4 4294967296 CURRENT
2 5 4294967296 UNUSED
2 6 4294967296 UNUSED
Create the Standby Redo Logs:
[oracle@funebsprim]$ sqlplus / as sysdba
SQL> alter database add standby logfile thread 1
group 7 (‘+RECO’) size 4294967296,
group 8 (‘+RECO’) size 4294967296,
group 9 (‘+RECO’) size 4294967296;
SQL> alter database add standby logfile thread 2
group 11 (‘+RECO’) size 4294967296,
group 12 (‘+RECO’) size 4294967296,
group 13 (‘+RECO’) size 4294967296;
3) Enable Standby File Management
On the primary database set STANDBY_FILE_MANAGEMENT=AUTO.
[oracle@funebsprim]$ sqlplus / as sysdba
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';
SQL>exit
4) Copy the password file from the primary database to the first standby host.
[oracle@funebsprim]$ srvctl config database -d BEE | grep 'Password file'
Password file: +DATA/BEE/PASSWORD/passwd <-- this file is what needs to be copied to /tmp and scp'd to the standby (result may differ)
[oracle@funebsprim]$ export ORACLE_SID=+ASM1
[oracle@funebsprim]$ export ORACLE_HOME=/u01/app/12.2.0.1/grid
[oracle@funebsprim]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@funebsprim]$asmcmd cp +DATA/BEE/PASSWORD/passwd /tmp/passwd.BEE
copying +DATA/BEE/PASSWORD/passwd -> /tmp/passwd.BEE
[oracle@funebsprim]$ scp /tmp/orapwBEE_CL1 oracle@sdty:/tmp/orapwBEE_CL
If Transparent Data Encryption (TDE) is enabled on the primary, the TDE wallet must be copied to the standby also.
5) Create a pfile from the spfile on the primary database and scp to standby
[oracle@funebsprim]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
[oracle@funebsprim]$ export ORACLE_SID=BEE
[oracle@funebsprim]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@funebsprim]$ sqlplus / as sysdba
SQL> create pfile='/tmp/primary.pfile' from spfile;
[oracle@funebsprim]$ scp /tmp/primary.pfile oracle@funebsstdy:/tmp/standby.pfile
6) Create net alias' for the primary and standby databases
TNS Aliases used in the Oracle Data Guard configuration |
BEE = BEE_CL = |
7) On Standby Database
- Create Audit Directory
On all standby hosts create the audit directory for the standby database.
[oracle@funebsstdy]$ mkdir -p /u01/app/oracle/admin/BEE_CL/adump - [oracle@<standbyhost2>]$ mkdir -p /u01/app/oracle/admin/BEE_CL/adump
8) Place the Standby Password File
First create the database directory in the DATA disk group then place the password file copied from the primary database to /tmp
[oracle@funebsstdy]$ export ORACLE_HOME=/u01/app/12.2.0.1/grid <- Grid Home
[oracle@funebsstdy]$ export ORACLE_SID=+ASM1
[oracle@funebsstdy]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@funebsstdy]$ asmcmd mkdir +DATA/BEE_CL
[oracle@funebsstdy]$ asmcmd mkdir +DATA/BEE_CL/PASSWORD
[oracle@funebsstdy]$ asmcmd pwcopy /tmp/orapwBEE_CL +DATA/BEE_CL/PASSWORD/orapwBEE_CL
9) Modify Parameter
Change the pfile copied to the standby in step 5
Parameters to be modified on the Standby as compared to the Primary | |
Primary - Only CONVERT parameters may change | Standby - changes to /tmp/standby.pfile |
BEE.undo_tablespace='UNDOTBS1' ………. ………. *.db_unique_name=BEE *.audit_file_dest=/u01/app/oracle/admin/BEE/adump *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) MAX_FAILURE=1 REOPEN=5 DB_UNIQUE_NAME=BEE ALTERNATE=LOG_ARCHIVE_DEST_10' *.log_archive_dest_10='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BEE ALTERNATE=LOG_ARCHIVE_DEST_1' *.log_archive_dest_state_10='ALTERNATE' *.control_files='+DATA/BEE/CONTROLFILE/control.ctl' # *CONVERT parameters are not dynamic and require a restart of the database. *.LOG_FILE_NAME_CONVERT='+DATA/BEE_CL','+DATA/BEE' *.DB_FILE_NAME_CONVERT='+DATA/BEE_CL','+DATA/BEE' | BEE_CL.undo_tablespace='UNDOTBS1' ………. ………. *.db_unique_name=BEE_CL *.audit_file_dest=/u01/app/oracle/admin/BEE_CL/adump *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) MAX_FAILURE=1 REOPEN=5 DB_UNIQUE_NAME=BEE_CL ALTERNATE=LOG_ARCHIVE_DEST_10' *.log_archive_dest_10='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BEE_CL ALTERNATE=LOG_ARCHIVE_DEST_1' *.log_archive_dest_state_10='ALTERNATE' *.control_files='+DATA/BEE_CL/CONTROLFILE/control.ctl' *.LOG_FILE_NAME_CONVERT=’+DATA/BEE’,’+DATA/BEE_CL’ *.DB_FILE_NAME_CONVERT=’+DATA/BEE’,’+DATA/BEE_CL’ |
NOTE: The database parameter db_name must be the same between primary and all standby database.
NOTE: The CONVERT parameters, log_file_name_convert and db_file_name_convert are not required for file name translation when Oracle Managed Files is used and the standby is on a different cluster than the primary. Setting LOG_FILE_NAME_CONVERT to some value enables online redo log pre-clearing which improves role transition performance.
NOTE: If disk group names are different between the primary and standby, change all disk group names accordingly.
10) Create the spfile
From the edited pfile, create the spfile for the standby database (the instance has not been started).
[oracle@funebsstdy]$ sqlplus “/ as sysdba”
SQL> create spfile='+DATA/BEE_CL/spfileBEE_CL.ora' from pfile='/tmp/standby.pfile';
File created.
11) Register the standby database with clusterware
Register the standby with clusterware and start the database nomount
As I have Single instance:
[oracle@funebsstdy]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
[oracle@funebsstdy]$ export ORACLE_SID=BEE_CL1
[oracle@funebsstdy]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@funebsstdy]$ srvctl add database -db BEE_CL -oraclehome /u01/app/oracle/product/12.2.0.1/dbhome_1 -dbtype SINGLE -instance BEE_CL1 -node funebsstdy -dbname BEE -diskgroup RECOC1,DATA -role physical_standby -spfile '+DATA/BEE_CL/spfileBEE_CL.ora' -pwfile '+DATA/BEE_CL/PASSWORD/orapwBEE_CL’
12) Restore the Standby Controlfile from the Primary
[oracle@funebsstdy]$ rman target sys/<password>
RMAN> restore standby controlfile from service 'BEE'; <- the service name is whatever connect descriptor points to the primary database
RMAN> alter database mount;
13) Configure degree of parallelism
To take advantage of parallelism during the restore, determine the number of cpu’s on your server by executing the following:
[oracle@funebsstdy]$ grep -c ^processor /proc/cpuinfo
Make the following RMAN configuration changes at the standby. Set the parallelism to match results of the network evaluation to achieve the best performance.
[oracle@funebsstdy]$ rman target /
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 8;
14) Restore the Standby Database from the primary database service
Restoring the datafiles from the primary to standby for instantiation is initiated on the standby cluster. Maximising the use of the channels configured for parallelisation by the previous step can be improved in some cases by using the RMAN SECTION SIZE clause.
Section Size
Parallelisation across channels is most efficient when the datafiles are all equally sized. Since each file by default is copied by only one channel, if one or two files are significantly larger than the rest, those files will take longer while other files have finished leaving some channels idle. When a small subset of files is larger than the rest, allowing RMAN to copy those large files in sections can utilize all channels to maximum efficiency. The section size can be set with the RMAN SECTION SIZE clause. RMAN testing has shown SECTION SIZE=64G to provide the best efficiency for files less than 16TB. If the data file is smaller than the section size chosen, it will not be broken into sections during the restore.
On the primary, query the largest datafile size to determine the section size to be used for the recover command.
SQL> select max(bytes)/1073741824 GB from v$datafile;
If the largest file is:
<15TB use section size of 64GB
>15TB and <30TB used section size of 128G
>30TB and <60TB used section size of 256G
>60TB use section size of 512G
RESTORE DATABASE FROM SERVICE - I have used this in my database
In order to initiate the copy of files, connect to the standby database and issue the restore command below using the descriptor in the tnsnames.ora for the primary database. In this example, that is the primary db_unique_name.
[oracle@funebsstdy]$ rman target /
RMAN> restore database from service BEE section size <section size>;
RMAN> backup spfile;
RMAN> switch database to copy; <- This may result in a no-op (nothing executes)
For larger databases, instead of the previous code block the following can be used.
[oracle@funebsstdy]$ srvctl stop database -db BEE_CL
[oracle@funebsstdy]$ srvctl start database -db BEE_CL -startoption mount
[oracle@funebsstdy]$ rman target sys/<password> <- It is necessary to connect with the password
RMAN > run {
allocate channel c1 type disk connect '/@<standby instance 1 SID_NAME>';
allocate channel c2 type disk connect '/@<standby instance 1 SID_NAME>';
allocate channel c3 type disk connect '/@<standby instance 1 SID_NAME>';
allocate channel c4 type disk connect '/@<standby instance 1 SID_NAME>';
allocate channel c5 type disk connect '/@<standby instance 2 SID_NAME>';
allocate channel c6 type disk connect '/@<standby instance 2 SID_NAME>';
allocate channel c7 type disk connect '/@<standby instance 2 SID_NAME>';
allocate channel c8 type disk connect '/@<standby instance 2 SID_NAME>';
restore database from service 'BEE' section size 64G;
}
RMAN> backup spfile;
RMAN> switch database to copy; <- This is not always necessary so may result in a no-op (nothing executes)
15) Clear Logfiles
Clear all Online Redo Logs and Standby Redo Logs
[oracle@funebsstdy]$ sqlplus / as sysdba
begin
for log_cur in ( select group# group_no from v$log )
loop
execute immediate 'alter database clear logfile group '||log_cur.group_no;
end loop;
end;
/
begin
for log_cur in ( select group# group_no from v$standby_log )
loop
execute immediate 'alter database clear logfile group '||log_cur.group_no;
end loop;
end;
/
16) Start Database in mount and enable MRP on Standby Side
Reference: Creating a Physical Standby database using RMAN restore database from service (Doc ID 2283978.1)
Post a Comment
Post a Comment