In this post, we will see how to build a Standalone Physical Standby Database
If you are looking for creating a 2 Node Physical Standby RAC database, then click on this LINK
Please click on the INDEX and browse for more interesting posts.
Resources :
Primary Host : infraDB-scan.infraxpertzz.com
Standby host : infradr.infraxpertzz.com
Primary DB : INFRA
Standby DB : INFRASTBY
Primary DB Steps :
1. Take Backup from Primary with backup of Standby control File
2. Force logging needs to be enabled
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
3. Archive log needs to be enabled
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 14
Current log sequence 14
4. Check DB Unique name
5. Add service name of standby in tnsnames.ora
infra =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = infraDB-scan.infraxpertzz.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = infra)
)
)
infraSTBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = infradr.infraxpertzz.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = infraSTBY)
)
)
6. Set archive log destinations and enable archive_log_dest_2
SQL> alter system set log_archive_dest_2='service="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=infradr.infraxpertzz.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=infraSTBY)(SERVER=DEDICATED)))"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="infraSTBY" net_timeout=30','valid_for=(online_logfile,all_roles)' scope=both;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
7. Make DB unique name to be part of dataguard
SQL> alter system set log_archive_config='DG_CONFIG=(infra,infraSTBY)' scope=both;
System altered.
8. Set remote login password to exclusive
SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
System altered.
9. Set FAL Server and FAL client
SQL> alter system set fal_server='infraSTBY' scope=both;
System altered.
SQL> alter system set fal_client='infra' scope=both;
System altered.
10. 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>
alter database add standby logfile THREAD 1 group 5 ('+DATA','+FRA') SIZE 200M;
alter database add standby logfile THREAD 1 group 6 ('+DATA','+FRA') SIZE 200M;
alter database add standby logfile THREAD 1 group 7 ('+DATA','+FRA') SIZE 200M;
alter database add standby logfile THREAD 2 group 8 ('+DATA','+FRA') SIZE 200M;
alter database add standby logfile THREAD 2 group 9 ('+DATA','+FRA') SIZE 200M;
alter database add standby logfile THREAD 2 group 10 ('+DATA','+FRA') SIZE 200M;
11. 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
12. Copy the pfile, backup, standby controlfile, tnsnames, sqlnet, listener and password file to standby server
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.
[oracle@primary dbs]$ asmcmd
ASMCMD> cd +DATA
ASMCMD> ls -ltr
Type Redund Striped Time Sys Name
Y ASM/
N infra/
Y infraPROD-cluster/
N _MGMTDB/
PASSWORD HIGH COARSE APR 28 08:00:00 N orapwasm => +DATA/ASM/PASSWORD/pwdasm.256.1071043317
PASSWORD HIGH COARSE APR 28 08:00:00 N orapwasm_backup => +DATA/ASM/PASSWORD/pwdasm.257.1071043611
ASMCMD> cd infra/
ASMCMD> ls -ltr
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y PASSWORD/
Y TEMPFILE/
ASMCMD> cd PASSWORD/
ASMCMD> ls -ltr
Type Redund Striped Time Sys Name
PASSWORD HIGH COARSE JUN 12 02:00:00 Y pwdinfra.285.1074997989
ASMCMD> cp pwdinfra.285.1074997989 /oracle/product/19c/dbs/orapwinfra
copying +DATA/infra/PASSWORD/pwdinfra.285.1074997989 -> /oracle/product/19c/dbs/orapwinfra
ASMCMD> exit
Listener.ora
LISTENER_infraSTBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = infradr.infraxpertzz.com)(PORT = 1521))
)
)
)
SID_LIST_LISTENER_infraSTBY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = infraSTBY_DGMGRL)
(ORACLE_HOME = /oracle/product/19c)
(SID_NAME = infra)
)
)
SFTP/SCP them to desired locations in Standby server after installing the DB binaries on Standby server
Standby Database steps :
1. Install Oracle 19c Binaries
2. Build the directories in the standby
mkdir -p /oracle/base/admin/infra/adump
mkdir -p /oracle/infra/oradata1
mkdir -p /oracle/infra/admin/fast_recovery_area/
mkdir -p /oracle/infra/oradata2/
mkdir -p /oracle/infra/oraarch
mkdir -p /oracle/infra/redologa/
mkdir -p /oracle/infra/redologb/
3. Make changes in the listener host to point to standby host
4. 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'
Example :
infra.__data_transfer_cache_size=0
infra.__db_cache_size=6895435776
infra.__java_pool_size=16777216
infra.__large_pool_size=150994944
infra.__oracle_base='/oracle/base'#ORACLE_BASE set from environment
infra.__pga_aggregate_target=2298478592
infra.__sga_target=8388608000
infra.__shared_io_pool_size=0
infra.__shared_pool_size=1308622848
infra.__streams_pool_size=0
*.audit_file_dest='/oracle/base/admin/infra/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='19.0.0'
*.control_files='/oracle/infra/oradata1/control01.ctl','/oracle/infra/admin/fast_recovery_area/control02.ctl','/oracle/infra/oradata2/control03.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='infra'
*.db_recovery_file_dest='/oracle/infra/admin/fast_recovery_area'
*.db_recovery_file_dest_size=4785m
*.db_unique_name='infraSTBY'
*.dg_broker_start=TRUE
*.diagnostic_dest='/oracle/base'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=infraXDB)'
*.fal_client='infraSTBY'
*.fal_server='infra'
*.log_archive_config='DG_CONFIG=(infra,infraSTBY)'
*.log_archive_dest_1='LOCATION=/oracle/infra/oraarch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=infraSTBY'
*.log_archive_dest_2=''
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='infra%t_%s_%r.arc'
infra.log_archive_format='infra%t_%s_%r.arc'
infra.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=2179m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.service_names='infraSTBY,infra'
*.sga_target=8000m
*.standby_file_management='AUTO'
infra.thread=1
infra.undo_tablespace='UNDOTBS2'
*.db_file_name_convert='+DATA/infra/DATAFILE/','/oracle/infra/oradata1/','+DATA/infra/DATAFILE/','/oracle/infra/oradata2/'
*.log_file_name_convert='+DATA/infra/ONLINELOG/','/oracle/infra/redologa/','+FRA/infra/ONLINELOG/','/oracle/infra/redologb/'
5. Start the database using the modified pfile
startup nomount pfile='/oracle/product/19c/dbs/initinfra.ora';
6. Restore the standby control file
RMAN> restore standby controlfile from '/RMAN/12072021/standby_ctrl.ctl';
Starting restore at 12-JUL-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=355 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/oracle/infra/oradata1/control01.ctl
output file name=/oracle/infra/admin/fast_recovery_area/control02.ctl
output file name=/oracle/infra/oradata2/control03.ctl
Finished restore at 12-JUL-21
7. Mount the database
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
8. Catalog the backup files
9. Restore the database from the primary’s backup
RUN
{
allocate channel ch1 type disk;
allocate channel ch2 type disk;
SET NEWNAME FOR DATAFILE '+DATA/infra/DATAFILE/system.297.1074998013' TO '/oracle/infra/oradata1/system01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/infra/DATAFILE/sysaux.290.1074998047' TO '/oracle/infra/oradata1/sysaux01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/infra/DATAFILE/undotbs2.294.1074998513' TO '/oracle/infra/oradata1/undotbs02.dbf';
SET NEWNAME FOR DATAFILE '+DATA/infra/DATAFILE/users.296.1074998073' TO '/oracle/infra/oradata1/users01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/infra/DATAFILE/bpi.286.1075001991' TO '/oracle/infra/oradata1/infra01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/infra/DATAFILE/undotbs1.293.1074998073' TO '/oracle/infra/oradata1/undotbs01.dbf';
SET NEWNAME FOR tempfile '+DATA/infra/TEMPFILE/temp.289.1074998157' TO '/oracle/infra/oradata1/temp01.dbf';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
switch tempfile all;
release channel ch1;
release channel ch2;
}
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=355 device type=DISK
allocated channel: ch2
channel ch2: SID=367 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 12-JUL-21
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00002 to /oracle/infra/oradata1/infra.286.1075001991
channel ch1: restoring datafile 00004 to /oracle/infra/oradata1/undotbs1.293.1074998073
channel ch1: restoring datafile 00005 to /oracle/infra/oradata1/undotbs2.294.1074998513
channel ch1: reading from backup piece /RMAN/12062021/df_infra_t1075004116_s4_p1
channel ch2: starting datafile backup set restore
channel ch2: specifying datafile(s) to restore from backup set
channel ch2: restoring datafile 00001 to /oracle/infra/oradata1/system.297.1074998013
channel ch2: restoring datafile 00003 to /oracle/infra/oradata1/sysaux.290.1074998047
channel ch2: restoring datafile 00007 to /oracle/infra/oradata1/users.296.1074998073
channel ch2: reading from backup piece /RMAN/12062021/df_infra_t1075004116_s3_p1
channel ch1: piece handle=/RMAN/12062021/df_infra_t1075004116_s4_p1 tag=TAG20210612T041516
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch2: piece handle=/RMAN/12062021/df_infra_t1075004116_s3_p1 tag=TAG20210612T041516
channel ch2: restored backup piece 1
channel ch2: restore complete, elapsed time: 00:00:15
Finished restore at 12-JUL-21
renamed tempfile 1 to /oracle/infra/oradata1/temp01.dbf in control file
released channel: ch1
released channel: ch2
10. Start listener
11. Start redo apply on standby
SQL> alter database recover managed standby database disconnect from session;
Database altered.
12. Add standby log files
alter database add standby logfile group 5 '/oracle/infra/redologa/standby_redo01.log' size 200M;
alter database add standby logfile group 6 '/oracle/infra/redologa/standby_redo02.log' size 200M;
alter database add standby logfile group 7 '/oracle/infra/redologa/standby_redo03.log' size 200M;
alter database add standby logfile group 8 '/oracle/infra/redologb/standby_redo04.log' size 200M;
alter database add standby logfile group 9 '/oracle/infra/redologb/standby_redo05.log' size 200M;
alter database add standby logfile group 10 '/oracle/infra/redologb/standby_redo06.log' size 200M;
13. Check applied logs
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#;