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#;