Oracle Standby Configuration - Quick Reference

 Primary Database Parameters:

db_unique_name = PRIM

db_recovery_file_dest_size = 200g

db_recovery_file_dest = '/u01/app/oracle/oradata/RECO_DEST'

log_archive_dest_1= 'location=USE_DB_RECOVERY_FILE_DEST'
log_archive_dest_2 = "SERVICE=STBY LGWR ASYNC=20480 

DB_UNIQUE_NAME = STBY OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30"

log_archive_config = 'dg_config=(PRIM,STBY)'
log_archive_min_succeed_dest = 1
standby_file_management = AUTO
Remote_login_passwordfile = exclusive
log_archive_format = 'PRIM_%s_%t_%r.arc'
db_file_name_convert = '/u01/app/oracle/oradata/STBY','/u01/app/oracle/oradata/PRIM'
log_file_name_convert = '/u01/app/oracle/oradata/STBY','/u01/app/oracle/oradata/PRIM'
fal_server = STBY
fal_client = PRIM

Standby Database Parameters:

db_unique_name = STBY

db_recovery_file_dest_size = 200g

db_recovery_file_dest = '/u01/app/oracle/oradata/RECO_DEST'

db_flashback_retention_target = 2880

log_archive_dest_1 = 'location=USE_DB_RECOVERY_FILE_DEST'

log_archive_dest_2 = "SERVICE=PRIM LGWR ASYNC=20480 DB_UNIQUE_NAME=PRIM OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30"

log_archive_config = 'dg_config=(STBY,PRIM)'

log_archive_min_succeed_dest = 1

standby_file_management = AUTO

Remote_login_passwordfile = exclusive

log_archive_format = 'STBY_%s_%t_%r.arc'

db_file_name_convert ='/u01/app/oracle/oradata/PRIM','/u01/app/oracle/oradata/STBY'

log_file_name_convert = '/u01/app/oracle/oradata/PRIM','/u01/app/oracle/oradata/STBY'

fal_server = PRIM

service_names = STBY

fal_client = STBY

Standy Database Using RMAN:

 

$ rman TARGET sys/PASSWD@PRIM AUXILIARY sys/PASSWD@STBY

 

RMAN> DUPLICATE DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER nofilenamecheck;

Manage Physical Standby:

SQL> alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database cancel;

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

SQL> select sequence#, applied, to_char(first_time, 'mm/dd/yy hh24:mi:ss') first  from v$archived_log  order by first_time;

SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

Convert to Snapshot Standby [READ/WRITE] Validation:

SQL> alter database recover managed standby database cancel;
SQL> alter database flashback on;
SQL> select database_role, flashback_on from v$database;
SQL> alter database convert to snapshot standby;
SQL> shutdown immediate;
SQL> startup
SQL> select database_role, flashback_on from v$database;

set linesize 150
col name format a50
col scn format 99999999
col time format a32
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE/(1024*1024) storage_size_MB FROM V$RESTORE_POINT;
 (OR)

RMAN> LIST RESTORE POINT ALL;

SQL> startup mount force;
SQL> alter database convert to physical standby;
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;

SQL> select estimated_flashback_size/1024/1024 "Estimated Flbacklog Size mb", retention_target/60 "Hours of flback logs", flashback_size/1024/1024 "Current Flbacklog Size mb"

from v$flashback_database_log;

SQL> select oldest_flashback_scn "oldest flback SCN",
oldest_flashback_time "oldest flback time"
from v$flashback_database_log;

set lines 120
col NAME format a30
SQL> select CON_ID,CON_UID,GUID,NAME,OPEN_MODE from v$pdbs;
SQL> select DB_UNIQ_NAME,PDB_UID,NAME,VALUE$ from PDB_SPFILE$;