Navigation Bar

Wednesday, October 2, 2024

Oracle Dataguard physical standby setup steps

Primary DatabaseStandby Database
DB Unique nameorclalpha
DB Nameorclorcl
IP Address192.168.56.101192.168.56.102
Oracle Database version19.3.0.019.3.0.0
Oracle Linux version8.98.9
Hostnamealphabeta

The configuration for the primary and standby database is as shown above.

Scripts to be run on the Primary database

SHUT IMMEDIATE;
STARTUP MOUNT;

SHOW PARAMETER NAME;
orcl

SHOW PARAMETER DB_UNIQUE_NAME;
orcl

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

ALTER DATABASE FORCE LOGGING;

SQL> SELECT FORCE_LOGGING,LOG_MODE FROM v$database;
YESARCHIVELOG
ALTER SYSTEM SET LOCAL_LISTENER='DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST = 192.168.56.101)(PORT = 1522))' SID='orcl';
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='ora_%t_%s_%r.arc' SCOPE=SPFILE; 
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/oradata/orcl/fra/'  SCOPE=BOTH;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 60 SCOPE=BOTH;
ALTER DATABASE FLASHBACK ON;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
CREATE PFILE FROM SPFILE;

SHUTDOWN IMMEDIATE;
STARTUP;
ARCHIVE LOG LIST;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log') SIZE 200M; 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') SIZE 200M; 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 200M; 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log') SIZE 200M;

SQL> SELECT MEMBER FROM V$LOGFILE; 
SQL> SELECT MEMBER FROM V$LOGFILE ORDER BY GROUP#;
SQL> SELECT THREAD#, GROUP#, SEQUENCE#, STATUS, BYTES FROM V$STANDBY_LOG; 
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/oradata/ORCL/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
'SERVICE=alpha LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=alpha' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,alpha)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER SYSTEM SET FAL_CLIENT='orcl';
ALTER SYSTEM SET FAL_SERVER='alpha';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SHUTDOWN IMMEDIATE;
STARTUP;
CREATE PFILE FROM SPFILE;
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; 
SQL> SELECT NAME, DB_UNIQUE_NAME, SWITCHOVER_STATUS, DATABASE_ROLE, OPEN_MODE FROM V$DATABASE; 
SQL> SELECT DEST_NAME, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE STATUS='ERROR';

We create the standby database by connecting rman to the primary and the auxiliary database.

For this we need to transfer the initorcl.ora file to the standby database. 
cd /u01/app/oracle/dbs
scp initorcl.ora oracle@192.168.56.102:/u01/app/oracle/dbs

Make the following changes in this file.
db_unique_name=alpha
db_name-orcl

Copy the password from the primary to the standby database
cd /u01/app/oracle/dbs
scp orapworcl oracle@192.168.56.102:/u01/app/oracle/dbs

export ORACLE_SID=alpha
start the database on the standby in nomount mode


SQL> sqlplus sys as sysdba
SQL> startup nomount pfile='/u01/app/oracle/dbs/initorcl.ora'

start the listener on both the primary and standby database with the lsnrctl start command
$lsnrctl start
$lsnrctl status

rman TARGET SYS/oracle@orcl AUXILIARY SYS/oracle@alpha
connected to target database: ORCL (DBID=1789945194) connected to auxiliary database: ORCL (not mounted) Important point to note is that the auxiliary database should get connected and be in NOT MOUNTED mode 

RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 25-MAY-24
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
...
...
...
Finished restore at 25-MAY-24
sql statement: alter system archive log current
contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1169870158 file name=/u01/app/oracle/oradata/ORCL/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1169870158 file name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1169870158 file name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1169870158 file name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1169870158 file name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1169870158 file name=/u01/app/oracle/oradata/ORCL/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1169870158 file name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1169870158 file name=/u01/app/oracle/oradata/ORCL/orclpdb1/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1169870158 file name=/u01/app/oracle/oradata/ORCL/orclpdb1/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1169870159 file name=/u01/app/oracle/oradata/ORCL/orclpdb1/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1169870159 file name=/u01/app/oracle/oradata/ORCL/orclpdb1/users01.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=15 STAMP=1169870159 file name=/u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf
datafile 15 switched to datafile copy
input datafile copy RECID=16 STAMP=1169870159 file name=/u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf
datafile 16 switched to datafile copy
input datafile copy RECID=17 STAMP=1169870159 file name=/u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf
Finished Duplicate Db at 25-MAY-24

Scripts to be run on the Standby database 

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 8 ('/u01/app/oracle/oradata/ORCL/redo04.log') SIZE 200M; 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 9 ('/u01/app/oracle/oradata/ORCL/redo05.log') SIZE 200M; 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 23 ('/u01/app/oracle/oradata/ORCL/redo06.log') SIZE 200M; 
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/u01/app/oracle/oradata/ORCL/redo07.log') SIZE 200M;
SQL> SELECT MEMBER FROM V$LOGFILE; 
SQL> SELECT THREAD#, GROUP#, SEQUENCE#, STATUS, BYTES FROM  V$STANDBY_LOG; 
SQL> SELECT MEMBER FROM V$LOGFILE ORDER BY GROUP#; 

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/oracle/oradata/ORCL/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=alpha' scope=spfile;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
'SERVICE=orcl LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='ora_%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,alpha)'; -- this should be in same order on both db
ALTER SYSTEM SET FAL_CLIENT='alpha';
ALTER SYSTEM SET FAL_SERVER='orcl';
alter system set local_listener='DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST = 192.168.56.102)(PORT = 1522))' SID='orcl';

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
ALTER DATABASE FLASHBACK ON;

SHUTDOWN IMMEDIATE;
STARTUP;
When turning flashback database on if you get below error
"ORA-38706: Cannot turn on FLASHBACK DATABASE logging"
"ORA-38788: More standby database recovery is needed"
shutdown the database and restart it.
The standby database will open in real only mode. Now issue the command 
ALTER DATABASE FLASHBACK ON;

tnsnames and listener entry on the primary database 


orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

alpha =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
	  (INSTANCE_NAME = orcl)
      (SERVICE_NAME = ALPHA)
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = /u01/app/oracle/dbhome/orcl)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:/u01/app/oracle/dbhome/orcl/bin/oraclr19.dll")
    )
	
   (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/dbhome/orcl)
      (SID_NAME = orcl)
    )
  )

tnsnames and listener entry on the standby database 


alpha =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
	  (INSTANCE_NAME = orcl)
      (SERVICE_NAME = ALPHA)
    )
  )

orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = /u01/app/oracle/dbhome/orcl)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:/u01/app/oracle/dbhome/orcl/bin/oraclr19.dll")
    )
	
	(SID_DESC =
      (GLOBAL_DBNAME = alpha)
	  (ORACLE_HOME = /u01/app/oracle/dbhome/orcl) 
      (SID_NAME = orcl)
    )	
  )
tnsnames and listener entries on both the primary and the standby database must be checked and verified as any incorrect entry will cause error in data replication on the standby database. 
This can be verified by connecting to both the primary and the standby databases with sqlplus.
$ sqlplus system/oracle@orcl

$ sqlplus system/oracle@alpha
Once these setups and checks are done on both primary and standby databases we are good to start the data replication process. For this, once the database is restarted, we will issue the following command 
  
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
On both primary and standby databases we can run the following commands to verify that the setup is correct 

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED   FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

  
SELECT NAME, DB_UNIQUE_NAME, SWITCHOVER_STATUS, DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

On the standby database the output should be as follows
 
NAMEDB_UNIQUE_NAMESWITCHOVER_STATUSDATABASE_ROLEOPEN_MODE
orclalphaNOT ALLOWEDPHYSICAL STANDBYREAD ONLY WITH APPLY

On the primary database the output should be as follows
 
NAMEDB_UNIQUE_NAMESWITCHOVER_STATUSDATABASE_ROLEOPEN_MODE
orclorclTO STANDBYPRIMARYREAD WRITE

Query output as above on the primary and the standby database indicates that datagaurd setup is correct and data replication on standby will happen normally. If for any configuration or setup releated issues you do not get an output as above you can query some log tables and check as below.
   
SELECT DEST_NAME, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE STATUS='ERROR';
DEST_NAMESTATUSERROR
LOG_ARCHIVE_DEST_2ERRORORA-16047: DGID mismatch between destination setting and target database
LOG_ARCHIVE_DEST_2ERRORORA-16191: Primary log shipping client not logged on standby
LOG_ARCHIVE_DEST_2ERRORORA-01034: ORACLE not available

For
ORA-16047: DGID mismatch between destination setting and target database
Check if the LOG_ARCHIVE_DEST_2 configuration is correct on primary and standby

On primary it should be
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
'SERVICE=alpha LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=alpha' SCOPE=SPFILE;
On standby it should be
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
'SERVICE=orcl LGWR ASYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' SCOPE=SPFILE;

For 
ORA-16191: Primary log shipping client not logged on standby
A possible reason could be the password file. 
Make sure the orapworcl file on the primary and standby databases are similar. 
Otherwise need to use the scp command (as mentioned in post above) to copy the file from the primary to the standby database.
Another reason could be the connection from primary to the standby database and vice versa. Make sure you can connect to both the databases from the primary and the standby servers with sqlplus.

For
ORA-01034: ORACLE not available
Check the connection from primary to the standby database and vice versa. Make sure you can connect to both the databases from the primary and the standby servers with sqlplus.

For any other errors will need to investigate further like checking the MRP0 process etc 

Once the setup is ready you are ready to test your physical standby
On the primary database you can connect as system user and run the following scripts 
$sqlplus system/oracle@orcl
CREATE TABLE TEST1
(
ID   NUMBER,
VAL  VARCHAR2(1000)
);

INSERT INTO test1 VALUES (1, 'YIPEEEE!!!! IT IS WORKING'); 
INSERT INTO test1 VALUES (2, 'YIPEEEE!!!! IT IS STILL WORKING');
On the standby you can verify that the data is replicated with the following query 
$sqlplus system/oracle@alpha
SQL> SELECT * FROM TEST1;

and you should get the output
1YIPEEEE!!!! IT IS WORKING
2YIPEEEE!!!! IT IS STILL WORKING

References 
Youtube video dataguard physical standby database configuration

Thought for the day
"The tongue has the power of life and death, 
  and those who love it will eat its fruit".
Proverbs 18:21

"Set a guard over my mouth O LORD; 
 keep watch over the door of my lips!" 
Psalms 141:3
at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)