In this post we will see 12.2.0.1 Physical Standby Database creation using RMAN
Primary Server Details :
Hostname : Primary
IP Address : 172.168.190.15
DB Unique Name : INFRADBP_PRIME
Standby Server Details :
Hostname : Standby
IP Address : 172.168.190.20
DB Unique Name : INFRADBP_STBY
NOTE : Please enable archive logs, if not enabled
We are assuming here that archive log is enabled
Also, always keep another terminal opened to check the alert log for each database
Please click on the INDEX and browse for more interesting posts.
To upgrade Physical standby database from 12c to 19c, clink on the below link
Steps in Primary database :
Force logging should be enabled
SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- INFRADBP READ WRITE SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- NO SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- YES
Add service name of standy in tnsnames.ora
INFRADBP_PRIME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.190.15)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = infradbp)
)
)
LISTENER_INFRADBP =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.190.15)(PORT = 1521))
INFRADBP_STBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.190.20)(PORT = 1521))
)
(CONNECT_DATA =
(SID = infradbp)
)
)
Set archive log destinations and enable archive_log_dest_2
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area/infradbp'; System altered. SQL> alter system set log_archive_dest_2='service=infradbp_stby async valid_for=(online_logfiles,primary_role) db_unique_name=infradbp_stby' scope=both; System altered. SQL> alter system set log_archive_dest_state_2=enable; System altered.
Build the Environment File
export ORACLE_SID=infradbp export ORACLE_HOME=/u01/app/oracle/product/19c export ORACLE_BASE=/u01/app/oracle export TNS_ADMIN=/u01/app/oracle/product/19c/network/admin export PATH=$PATH:$ORACLE_HOME/bin
Make DB unique name to be part of dataguard
SQL> alter system set log_archive_config='dg_config=(infradbp_prime,infradbp_stby)' scope=both; System altered.
Set remote login password to exclusive
SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile; System altered.
Set FAL Server and FAL client
SQL> alter system set fal_server='infradbp_stby' scope=both; System altered. SQL> alter system set fal_client='infradbp_prime' scope=both; System altered.
Add standby logfiles to the primary server
The logs on the standby server need to be the same size and the count needs to be 1 more than online logfiles in order for the primary redo to be applied to the standby redo logs.
SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/infradbp/redo03.log /u01/app/oracle/oradata/infradbp/redo02.log /u01/app/oracle/oradata/infradbp/redo01.log SQL> !ls -ltrh /u01/app/oracle/oradata/infradbp/redo03.log -rw-r-----. 1 oracle oinstall 201M Mar 28 11:58 /u01/app/oracle/oradata/infradbp/redo03.log SQL> alter database add standby logfile '/u01/app/oracle/oradata/infradbp/standby_redo01.log' size 200M; Database altered. SQL> alter database add standby logfile '/u01/app/oracle/oradata/infradbp/standby_redo02.log' size 200M; Database altered. SQL> alter database add standby logfile '/u01/app/oracle/oradata/infradbp/standby_redo03.log' size 200M; Database altered. SQL> alter database add standby logfile '/u01/app/oracle/oradata/infradbp/standby_redo04.log' size 200M; Database altered.
Backup using RMAN
rman target /
run{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 0
format '/u01/backup/al_%d_t%t_s%s_p%p'
(database);
release channel ch1;
release channel ch2;
allocate channel ch1 type disk;
allocate channel ch2 type disk;
crosscheck archivelog all;
backup
format '/u01/backup/al_arch_%d_t%t_s%s_p%p'
filesperset=100
(archivelog all);
BACKUP CURRENT CONTROLFILE for standby format '/u01/backup/ct_%d_t%t_s%s_p%p';
release channel ch1;
release channel ch2;
}
RMAN> run{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
backup incremental level 0
format '/u01/backup/al_%d_t%t_s%s_p%p'
(database);
release channel ch1;
release channel ch2;
allocate channel ch1 type disk;
allocate channel ch2 type disk;
crosscheck archivelog all;
backup
format '/u01/backup/al_arch_%d_t%t_s%s_p%p'
filesperset=100
(archivelog all);
BACKUP CURRENT CONTROLFILE for standby format '/u01/backup/ct_%d_t%t_s%s_p%p';
release channel ch1;
release channel ch2;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19>
allocated channel: ch1
channel ch1: SID=266 device type=DISK
allocated channel: ch2
channel ch2: SID=267 device type=DISK
Starting backup at 28-MAR-21
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/infradbp/system01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/infradbp/users01.dbf
channel ch1: starting piece 1 at 28-MAR-21
channel ch2: starting incremental level 0 datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/infradbp/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/infradbp/undotbs01.dbf
channel ch2: starting piece 1 at 28-MAR-21
channel ch2: finished piece 1 at 28-MAR-21
piece handle=/u01/backup/al_INFRADBP_t1068379807_s4_p1 tag=TAG20210328T121007 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:35
channel ch1: finished piece 1 at 28-MAR-21
piece handle=/u01/backup/al_INFRADBP_t1068379807_s3_p1 tag=TAG20210328T121007 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:45
Finished backup at 28-MAR-21
Starting Control File and SPFILE Autobackup at 28-MAR-21
piece handle=/u01/app/oracle/fast_recovery_area/infradbp/INFRADBP/autobackup/2021_03_28/o1_mf_s_1068379852_j6097ntj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-MAR-21
released channel: ch1
released channel: ch2
allocated channel: ch1
channel ch1: SID=266 device type=DISK
allocated channel: ch2
channel ch2: SID=267 device type=DISK
validation succeeded for archived log
archived log file name=/u01/app/oracle/fast_recovery_area/infradbp/INFRADBP/archivelog/2021_03_28/o1_mf_1_1_j6088p13_.arc RECID=1 STAMP=1068378862
validation succeeded for archived log
archived log file name=/u01/app/oracle/fast_recovery_area/infradbp/INFRADBP/archivelog/2021_03_28/o1_mf_1_2_j6088yyj_.arc RECID=2 STAMP=1068378871
Crosschecked 2 objects
Starting backup at 28-MAR-21
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=1068378862
channel ch1: starting piece 1 at 28-MAR-21
channel ch2: starting archived log backup set
channel ch2: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=2 STAMP=1068378871
input archived log thread=1 sequence=3 RECID=3 STAMP=1068379855
channel ch2: starting piece 1 at 28-MAR-21
channel ch2: finished piece 1 at 28-MAR-21
piece handle=/u01/backup/al_arch_INFRADBP_t1068379855_s7_p1 tag=TAG20210328T121055 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
channel ch1: finished piece 1 at 28-MAR-21
piece handle=/u01/backup/al_arch_INFRADBP_t1068379855_s6_p1 tag=TAG20210328T121055 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:08
Finished backup at 28-MAR-21
Starting backup at 28-MAR-21
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including standby control file in backup set
channel ch1: starting piece 1 at 28-MAR-21
channel ch1: finished piece 1 at 28-MAR-21
piece handle=/u01/backup/ct_INFRADBP_t1068379863_s8_p1 tag=TAG20210328T121103 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-MAR-21
Starting Control File and SPFILE Autobackup at 28-MAR-21
piece handle=/u01/app/oracle/fast_recovery_area/infradbp/INFRADBP/autobackup/2021_03_28/o1_mf_s_1068379865_j60981rw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-MAR-21
released channel: ch1
released channel: ch2
Change standby file management to auto
SQL> show parameter standby NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enabled_PDBs_on_standby string * standby_archive_dest string ?#/dbs/arch standby_db_preserve_states string NONE standby_file_management string MANUAL SQL> alter system set standby_file_management=AUTO scope=BOTH; System altered. SQL> show parameter standby NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enabled_PDBs_on_standby string * standby_archive_dest string ?#/dbs/arch standby_db_preserve_states string NONE standby_file_management string AUTO
Copy the pfile to standby server to make reflections as standby database
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/12.2.0
/dbhome_1/dbs/spfileinfradbp.o
ra
SQL> create pfile from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@primary backup]$ cd $ORACLE_HOME/dbs
[oracle@primary dbs]$ ls -ltr
total 10376
-rw-r-----. 1 oracle oinstall 24 Mar 28 11:34 lkINFRADBP
-rw-r-----. 1 oracle oinstall 3584 Mar 28 11:37 orapwinfradbp
-rw-rw----. 1 oracle oinstall 1544 Mar 28 11:40 hc_infradbp.dat
-rw-r-----. 1 oracle oinstall 4608 Mar 28 12:08 spfileinfradbp.ora
-rw-r-----. 1 oracle oinstall 10600448 Mar 28 12:11 snapcf_infradbp.f
-rw-r--r--. 1 oracle oinstall 1554 Mar 28 12:58 initinfradbp.ora
[oracle@primary dbs]$ sftp 172.168.190.20
oracle@172.168.190.20's password:
Connected to 172.168.190.20.
sftp> cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs
sftp> put initinfradbp.ora
Uploading initinfradbp.ora to /u01/app/oracle/product/12.2.0/dbhome_1/dbs/initinfradbp.ora
initinfradbp.ora 100% 1554 1.5KB/s 00:00
Copy the backup,standby controlfile, tnsnames, sqlnet, listener and password file to standby server
[oracle@primary dbs]$ sftp 172.168.190.20 oracle@172.168.190.20's password: Connected to 172.168.190.20. sftp> cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs sftp> put orapwinfradbp Uploading orapwinfradbp to /u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwinfradbp orapwinfradbp 100% 3584 3.5KB/s 00:00 sftp> cd /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/ sftp> put sqlnet.ora Uploading sqlnet.ora to /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora sqlnet.ora 100% 202 0.2KB/s 00:00 sftp> put tnsnames.ora Uploading tnsnames.ora to /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora tnsnames.ora 100% 614 0.6KB/s 00:00 sftp> put listener.ora Uploading listener.ora to /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora listener.ora 100% 492 0.5KB/s 00:00 [oracle@primary admin]$ cd /u01/backup/ [oracle@primary backup]$ ls -ltr total 1211784 -rw-r-----. 1 oracle oinstall 359989248 Mar 28 12:10 al_INFRADBP_t1068379807_s4_p1 -rw-r-----. 1 oracle oinstall 710975488 Mar 28 12:10 al_INFRADBP_t1068379807_s3_p1 -rw-r-----. 1 oracle oinstall 804864 Mar 28 12:10 al_arch_INFRADBP_t1068379855_s7_p1 -rw-r-----. 1 oracle oinstall 158426624 Mar 28 12:10 al_arch_INFRADBP_t1068379855_s6_p1 -rw-r-----. 1 oracle oinstall 10665984 Mar 28 12:11 ct_INFRADBP_t1068379863_s8_p1 [oracle@primary backup]$ sftp 172.168.190.20 oracle@172.168.190.20's password: Connected to 172.168.190.20. sftp> cd /u01/backup/ sftp> put * Uploading al_INFRADBP_t1068379807_s3_p1 to /u01/backup/al_INFRADBP_t1068379807_s3_p1 al_INFRADBP_t1068379807_s3_p1 100% 678MB 52.2MB/s 00:13 Uploading al_INFRADBP_t1068379807_s4_p1 to /u01/backup/al_INFRADBP_t1068379807_s4_p1 al_INFRADBP_t1068379807_s4_p1 100% 343MB 17.2MB/s 00:20 Uploading al_arch_INFRADBP_t1068379855_s6_p1 to /u01/backup/al_arch_INFRADBP_t1068379855_s6_p1 al_arch_INFRADBP_t1068379855_s6_p1 100% 151MB 75.5MB/s 00:02 Uploading al_arch_INFRADBP_t1068379855_s7_p1 to /u01/backup/al_arch_INFRADBP_t1068379855_s7_p1 al_arch_INFRADBP_t1068379855_s7_p1 100% 786KB 786.0KB/s 00:00 Uploading ct_INFRADBP_t1068379863_s8_p1 to /u01/backup/ct_INFRADBP_t1068379863_s8_p1 ct_INFRADBP_t1068379863_s8_p1 100% 10MB 10.2MB/s 00:01
Steps in Standby Database :
Build the directories in the standby :
[oracle@standby dbs]$ mkdir -p /u01/app/oracle/admin/infradbp/adump [oracle@standby dbs]$ mkdir -p /u01/app/oracle/oradata/infradbp/ [oracle@standby dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/infradbp
Make changes in the listener host to point to standby host
[oracle@standby admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.168.190.20)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = infradbp)
)
)
Make below changes in the pfile
*.audit_file_dest='/u01/app/oracle/admin/infradbp/adump' *.db_name='infradbp' *.db_unique_name='infradbp_stby' *.fal_client='infradbp_stby' *.fal_server='infradbp_prime' *.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=infradbp_stby' *.log_file_name_convert='dummy','dummy' --> Add this if the paths are identical to standby, or else mention the paths. This is required to avoid the error--ORA-19527: physical standby redo log must be renamed *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO'
Start the database using the modified pfile
[oracle@standby dbs]$ ls -ltr total 8 -rw-r-----. 1 oracle oinstall 3584 Mar 28 13:00 orapwinfradbp -rw-r--r--. 1 oracle oinstall 1503 Mar 28 13:05 initinfradbp.ora [oracle@standby dbs]$ pwd /u01/app/oracle/product/12.2.0/dbhome_1/dbs [oracle@standby dbs]$ sqlplus '/as sysdba' SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 28 13:14:37 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initinfradbp.ora'; ORACLE instance started. Total System Global Area 1795162112 bytes Fixed Size 8621760 bytes Variable Size 553648448 bytes Database Buffers 1224736768 bytes Redo Buffers 8155136 bytes
Restore the standby control file
[oracle@standby backup]$ ls -ltr total 1211784 -rw-r-----. 1 oracle oinstall 710975488 Mar 28 13:08 al_INFRADBP_t1068379807_s3_p1 -rw-r-----. 1 oracle oinstall 359989248 Mar 28 13:08 al_INFRADBP_t1068379807_s4_p1 -rw-r-----. 1 oracle oinstall 158426624 Mar 28 13:08 al_arch_INFRADBP_t1068379855_s6_p1 -rw-r-----. 1 oracle oinstall 804864 Mar 28 13:08 al_arch_INFRADBP_t1068379855_s7_p1 -rw-r-----. 1 oracle oinstall 10665984 Mar 28 13:08 ct_INFRADBP_t1068379863_s8_p1 [oracle@standby backup]$ pwd /u01/backup [oracle@standby backup]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Sun Mar 28 13:15:25 2021 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: INFRADBP (not mounted) RMAN> restore standby controlfile from '/u01/backup/ct_INFRADBP_t1068379863_s8_p1'; Starting restore at 28-MAR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=35 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/infradbp/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/infradbp/control02.ctl Finished restore at 28-MAR-21
Mount the database
RMAN> alter database mount; Statement processed released channel: ORA_DISK_1
Restore the database from the primary’s backup
RMAN> restore database; Starting restore at 28-MAR-21 Starting implicit crosscheck backup at 28-MAR-21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=35 device type=DISK Crosschecked 5 objects Finished implicit crosscheck backup at 28-MAR-21 Starting implicit crosscheck copy at 28-MAR-21 using channel ORA_DISK_1 Finished implicit crosscheck copy at 28-MAR-21 searching for all files in the recovery area cataloging files... no files cataloged 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 00003 to /u01/app/oracle/oradata/infradbp/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/infradbp/undotbs01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/al_INFRADBP_t1068379807_s4_p1 channel ORA_DISK_1: piece handle=/u01/backup/al_INFRADBP_t1068379807_s4_p1 tag=TAG20210328T121007 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 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/infradbp/system01.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/infradbp/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/backup/al_INFRADBP_t1068379807_s3_p1 channel ORA_DISK_1: piece handle=/u01/backup/al_INFRADBP_t1068379807_s3_p1 tag=TAG20210328T121007 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 28-MAR-21
Start listener
lsnrctl start listener
Start redo apply on standby
SQL> alter database recover managed standby database disconnect from session; Database altered.
Check the log gaps and applied logs
SQL> select process,sequence#,status from v$managed_standby;
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 9 CLOSING
DGRD 0 ALLOCATED
DGRD 0 ALLOCATED
ARCH 7 CLOSING
ARCH 10 CLOSING
ARCH 0 CONNECTED
RFS 11 IDLE
RFS 0 IDLE
RFS 0 IDLE
RFS 0 IDLE
RFS 0 IDLE
PROCESS SEQUENCE# STATUS
--------- ---------- ------------
MRP0 11 APPLYING_LOG
SELECT A.THREAD#,
B.LAST_SEQ,
A.APPLIED_SEQ,
to_char(A.LAST_APP_TIMESTAMP ,'DD-MON-YYYY HH24:MI:SS') "Applied Time",
B.LAST_SEQ - A.APPLIED_SEQ ARC_DIFF
FROM ( SELECT THREAD#,
MAX (SEQUENCE#) APPLIED_SEQ,
MAX (NEXT_TIME) LAST_APP_TIMESTAMP
FROM GV$ARCHIVED_LOG
WHERE APPLIED = 'YES'
GROUP BY THREAD#) A,
( SELECT THREAD#, MAX (SEQUENCE#) LAST_SEQ
FROM GV$ARCHIVED_LOG
GROUP BY THREAD#) B
WHERE A.THREAD# = B.THREAD#;
SQL> SELECT A.THREAD#,
B.LAST_SEQ,
A.APPLIED_SEQ,
to_char(A.LAST_APP_TIMESTAMP ,'DD-MON-YYYY HH24:MI:SS') "Applied Time",
B.LAST_SEQ - A.APPLIED_SEQ ARC_DIFF
FROM ( SELECT THREAD#,
MAX (SEQUENCE#) APPLIED_SEQ,
MAX (NEXT_TIME) LAST_APP_TIMESTAMP
FROM GV$ARCHIVED_LOG
WHERE APPLIED = 'YES'
GROUP BY THREAD#) A,
( SELECT THREAD#, MAX (SEQUENCE#) LAST_SEQ
FROM GV$ARCHIVED_LOG
GROUP BY THREAD#) B
WHERE A.THREAD# = B.THREAD#; 2 3 4 5 6 7 8 9 10 11 12 13 14 15
THREAD# LAST_SEQ APPLIED_SEQ Applied Time ARC_DIFF
---------- ---------- ----------- ----------------------------- ----------
1 10 10 28-MAR-2021 16:15:12 0
Switch logfile in primary and check whether it is transported and applied in standby or not