Creating Standby Database using RMAN

For a convention primary db as ‘primary’ adn the standby db as ‘standby’

PRIMARY:

Make sure database is archive log mode, and enable force logging.

1-> Enable Force Logging(Already there)

SQL> ALTER DATABASE FORCE LOGGING;

PRIMARY > select log_mode from v$database;

LOG_MODE

------------

ARCHIVELOG

2 –>Setup Standby Redo Log:

In standby setup, archives log from primary shipped to standby and applied there. But if the primary database crashes, then the online redo logs will stay in primary side. As these logs hasn’t been archives, means those won’t be applied to standby, Which results in data loss. So if we add standby logfile, Data guard writes the Primary’s current redo log to a “standby redo log” allowing complete recovery in case of Primary site is lost.

SQL > select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'stdby_'||

regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||

''' size '||bytes||';' "Create Standby redo"

from v$logfile lf , v$log l

where l.group# = lf.group#

/ 2 3 4 5 6 7

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11  '/oradata/prim/redo/sbredo11.log' SIZE 100M;

3 –> Create the Password File for the Primary Database:
You have to create the password file since the user sys will be used by oracle to access the standby db and also be accessed by the standby db.
Make sure the password used for SYS on the primary database is the same used on the standby database.
# orapwd file=orapwSID password=your_pwd entries=5

4 – >Setup instance parameters for the primary database:

 *.DB_UNIQUE_NAME='primary'
 *.DB_NAME='primary'
 *.INSTANCE_NAME='primary'
 *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
 *.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
 *.LOG_ARCHIVE_DEST_2='SERVICE=standby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
 *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
 *.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
 *.LOG_ARCHIVE_FORMAT='arch_%r_%t_%s.arc'
 *.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
 *.SERVICE_NAMES='primary'
 *.STANDBY_FILE_MANAGEMENT='AUTO'
 *.DB_FILE_NAME_CONVERT='standby','primary' --Only use this parameter if you have different path in the standby server
 *.FAL_SERVER='standby'
 *.FAL_CLIENT='primary'

5.>Configure listener and tns entries:

STANDBY:

6 – pfile and password file creation in $ORACLE_HOME/db

7 – Backup Primary Database, Controlfile and Archivelogs
8 – Move Files to Standby Server
Steps for Both Databases
9 – Listener and tns setup  
– Restart the listener in both servers

Steps for Standby Database

10 – Setup instance parameters for the standby database.
*.DB_UNIQUE_NAME='standby'
*.DB_NAME='primary'
*.INSTANCE_NAME='standby'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradata/orcl/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='arch_%r_%t_%s.arc'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.SERVICE_NAMES='standby'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.DB_FILE_NAME_CONVERT='primary','standby'
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'

12 – Startup nomount the standby database
SQL> startup nomount;
13 – Recover database using RMAN
Start the automatically redo apply
SQL> alter database recover managed standby database disconnect from session;

14 – Checking if Standby database is working properly
To check if the database is working correctly, the current log sequence must be the same in both databases.

SQL> archive log list
Also check if logs are being applied
SQL> select sequence#, applied from v$archived_log order by sequence#;

=================

What are the Steps to create Physical Standby database?

1.Take a full hot backup of Primary database

2. Enable force logging to the database and Enable archiving

3. Prepare parameter file for primary database

4.Create standby control file

5.Transfer full backup, init.ora, standby control file to standby node.

6.Modify init.ora file on standby node.

7.Restore & Recover Standby database

8.Put Standby database in Managed Recover mode

Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)

Creating physical stanby: https://www.sultandba.com/2020/05/creating-single-instance-physical.html

Backgound process: https://www.sultandba.com/2021/11/oracle-dataguard-background-process.html