Backup Based Physical Standby Configuration

Backup Based Physical Standby Configuration Manually

Description:
         In this blog we are going to see step by step configuration and demo of oracle database primary to physical standby.

Backup Based Standby Configuration:
         Mostly we have configure standby database in active data guard method. using level 0 backup we configure physical standby database.

Overall Steps:

1. check the role in primary database.
2. Take level 0 backup using RMAN.
3. Primary database parameter configuration.
4. Control and Pfile creation for standby database.
5. Create directory in standby database.
6. Copy archivelogs,control file,pfile, backupsets to standby database.
7. Standby configuration.
8. Bounce database and set the standby control file.
9. Connect RMAN and restore ,recover the database.
10. Check the database can be created.
11. Start the MRP process.
12. Check the Log sync status.

Step 1:- Check the database role in primary database.
SQL> select name,open_mode,database_role from v$database;
Step 2:- Take level 0 backup using RMAN.
RMAN> backup incremental level 0 database plus archivelog;
Step 3:- Primary database parameter configuration.

SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(cloud,local)’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=cloud’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2=’service=local async valid_for=(online_logfiles,primary_role) db_unique_name=local’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET fal_server=’local’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET fal_client=’c’ SCOPE=both;

System altered.

SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both;

System altered.

Step 4:- Create control file and Pfile for standby database.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/u01/app/oracle/oradata/CLOUD/standby.ctl’;

Database altered.

SQL> CREATE PFILE=’$ORACLE_HOME/dbs/initcompany.ora’ FROM SPFILE;

File created.

Step 5:- Create directory in standby database.
[oracle@standby ~]$ mkdir -p /u01/app/oracle/oradata/COMPANY/controlfile
[oracle@standby ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
mkdir -p /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[oracle@standby ~]$ mkdir -p /u01/app/oracle/admin/COMPANY/adump
/u01/app/oracle/fast_recovery_area/CLOUD/backupset/

Step 6:- Copy archivelogs,control file,pfile, backupsets to standby database.
Pfile and password file:
[oracle@primary dbs]$ scp orapwcloud [email protected]:$ORACLE_HOME/dbs
[email protected]'s password:
orapwcloud 100% 2048 2.1MB/s 00:00
[oracle@primary dbs]$ scp initcompany.ora [email protected]:$ORACLE_HOME/dbs
[email protected]'s password:
initcompany.ora 100% 1512 1.3MB/s 00:00
[oracle@primary dbs]$ cd
[oracle@primary ~]$ cd /u01/app/oracle/fast_recovery_area/
[oracle@primary fast_recovery_area]$ ls
Archivelogs:
/u01/app/oracle/fast_recovery_area/CLOUD/archivelog/2021_12_17
[oracle@primary 2021_12_17]$ ll
total 24288
-rw-rw----. 1 oracle oracle 22827008 Dec 17 07:38 o1_mf_1_2_jvqw80t4_.arc
-rw-rw----. 1 oracle oracle 2004480 Dec 17 07:39 o1_mf_1_3_jvqwcpbc_.arc
-rw-rw----. 1 oracle oracle 35840 Dec 17 07:41 o1_mf_1_4_jvqwglmx_.arc
[oracle@primary 2021_12_17]$ scp o1_mf_1_2_jvqw80t4_.arc [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[email protected]'s password:
o1_mf_1_2_jvqw80t4_.arc 100% 22MB 42.4MB/s 00:00
[oracle@primary 2021_12_17]$ scp o1_mf_1_3_jvqwcpbc_.arc [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[email protected]'s password:
Permission denied, please try again.
[email protected]'s password:
o1_mf_1_3_jvqwcpbc_.arc 100% 1958KB 63.2MB/s 00:00
[oracle@primary 2021_12_17]$ scp o1_mf_1_4_jvqwglmx_.arc [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17
[email protected]'s password:
o1_mf_1_4_jvqwglmx_.arc 100% 35KB 20.1MB/s 00:00
[oracle@primary 2021_12_17]$ cd ..
[oracle@primary archivelog]$ cd ..
Backup sets:
[oracle@primary backupset]$ cd 2021_12_17
[oracle@primary 2021_12_17]$ ll
total 2049948
-rw-rw----. 1 oracle oracle 519864832 Dec 17 07:40 o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp
-rw-rw----. 1 oracle oracle 196301312 Dec 17 07:40 o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp
-rw-rw----. 1 oracle oracle 24836608 Dec 17 07:40 o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp
-rw-rw----. 1 oracle oracle 38912 Dec 17 07:41 o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp
-rw-rw----. 1 oracle oracle 1358094336 Dec 17 07:41 o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp 100% 496MB 30.3MB/s 00:16 
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp 100% 187MB 37.4MB/s 00:05 
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp 100% 24MB 42.7MB/s 00:00 
[oracle@primary 2021_12_17]$ scp o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp 100% 38KB 2.3MB/s 00:00 
[oracle@primary 2021_12_17]$ scp o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp [email protected]:/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/
[email protected]'s password: 
o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp 100% 1295MB 37.2MB/s 00:34 
[oracle@primary 2021_12_17]$ cd ../..
[oracle@primary CLOUD]$ ls

Step 7:- Standby Configuration.

1. Pfile parameter changes:
*.audit_file_dest='/u01/app/oracle/admin/cloud/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/CLOUD/controlfile/o1_mf_jvbn3nsp_.ctl','/u01/app/oracle/fast_recovery_area/CLOUD/controlfile/o1_mf_jvbn3nxt_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='cloud'
*.db_unique_name='company'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cloudXDB)'
*.fal_client='company'
*.fal_server='cloud'
*.log_archive_config='dg_config=(cloud,company)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=cloud'
*.log_archive_dest_2='service=company async valid_for=(online_logfiles,primary_role) db_unique_name=cloud'
*.log_archive_dest_state_2='ENABLE'
*.open_cursors=300
*.pga_aggregate_target=379m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1136m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

2. Start the Database using spfile:

[oracle@local dbs]$ export ORACLE_SID=company
[oracle@local dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Dec 17 08:17:06 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> CREATE SPFILE FROM PFILE=’initcompany.ora’;

File created.

Step 8:- Bounce database and set the standby control file.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1191181696 bytes
Fixed Size 8895872 bytes
Variable Size 318767104 bytes
Database Buffers 855638016 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL> exit

SQL> alter system set control_files=’/u01/app/oracle/oradata/COMPANY/standby.ctl’ scope=spfile;

System altered.

Step 9:-Connect RMAN and restore ,recover the database.

RMAN> restore database;

Starting restore at 17-DEC-21
Starting implicit crosscheck backup at 17-DEC-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
Crosschecked 18 objects
Finished implicit crosscheck backup at 17-DEC-21

Starting implicit crosscheck copy at 17-DEC-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-DEC-21

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwcqrw_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwdkp0_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T073959_jvqwds4r_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_annnn_TAG20211217T074130_jvqwgls4_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_2_jvqw80t4_.arc
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_3_jvqwcpbc_.arc
File Name: /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_system_jvbmzxk8_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_sysaux_jvbn1c3g_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_undotbs1_jvbn24dz_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/CLOUD/datafile/o1_mf_users_jvbn25hj_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/COMPANY/backupset/2021_12_17/o1_mf_nnnd0_TAG20211217T074035_jvqwdvrz_.bkp tag=TAG20211217T074035
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 17-DEC-21

RMAN> recover database;

Starting recover at 17-DEC-21
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/COMPANY/archivelog/2021_12_17/o1_mf_1_4_jvqwglmx_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-DEC-21

RMAN> exit

Step 10:- Check the database can be created.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
CLOUD MOUNTED

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
CLOUD MOUNTED PHYSICAL STANDBY

Step 11:- Start the MRP process.

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

Step 12:- Log sync status.

switch logs in primary, defer and enable the dest.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL>

check the status in standby.

SQL> alter database recover managed standby database disconnect nodelay;

Database altered.

SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 2 3 4

Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1                              9                                    9                       0

SQL>

 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8