Physical Standby Database Creation and Simple Management

Compared with RAC environment, standby database is much less complicated to set up.
A process of standby database creation generally contains these steps:
  1.  Force logging on primary database
  2.  Enable primary archive log mode
  3.  Generate pfile and shutdown primary database
  4.  Transport data files to standby node
  5.  Prepare both primary and standby parameter files
  6.  Standby instance service and password file creation
  7.  Set ORACLE_SID variable properly
  8.  Configure listeners and TNS names for both kind of nodes
  9.  Standby database control file generation
  10.  Startups

Here come more detailed processes.
1. Force logging on primary database
Force logging makes database running in logging-everything state, and by pass nologging clause.
  SQL> ALTER DATABASE FORCE LOGGING;

2. Enable primary archive log mode
Archive log mode is required to primary node but this is not prerequisite of standby nodes.
  SQL> SHUTDOWN IMMEDIATE
  SQL> STARTUP MOUNT
  SQL> ALTER DATABASE ARCHIVELOG;
  SQL> ALTER DATABASE OPEN;

3. Generate pfile and shutdown primary database
  SQL> CREATE PFILE=’D:\INITSMART.ORA’ FROM SPFILE;
  SQL> SHUTDOWN IMMEDIATE

4. Transport data files to standby node
Here you could make use of OS utilities to copy data files from primary node to standby nodes.

5. Prepare both primary and standby parameter files
Parameter file configuration is key step of the whole standby environment set up. Each node should have known others’ name, archive destination, path name convert method and how many processes serve for log archiving etc. Open pfile which I took from step 3, and add it with entries like this:

DB_NAME='smart'
DB_UNIQUE_NAME='smart'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(smart,stb)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=E:\oracle\product\10.2.0\oradata\smart
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=smart'
LOG_ARCHIVE_DEST_2=
 'SERVICE=stb LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=stb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.remote_login_passwordfile='EXCLUSIVE'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=stb
FAL_CLIENT=smart
DB_FILE_NAME_CONVERT='stb','smart'
LOG_FILE_NAME_CONVERT='F:\oracle\product\10.2.0\oradata\stb\','E:\oracle\product\10.2.0\oradata\smart\'
STANDBY_FILE_MANAGEMENT=AUTO

On stb node, the scenario is quite similar but we still need some modification to show the critical difference.

DN_NAME='smart'
DB_UNIQUE_NAME='stb'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(smart,stb)'
LOG_ARCHIVE_DEST_1=
 'LOCATION=F:\oracle\product\10.2.0\oradata\stb
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=stb'
LOG_ARCHIVE_DEST_2=
 'SERVICE=smart LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=smart'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.remote_login_passwordfile='EXCLUSIVE'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=smart
FAL_CLIENT=stb
DB_FILE_NAME_CONVERT='smart','stb'
LOG_FILE_NAME_CONVERT='E:\oracle\product\10.2.0\oradata\smart','F:\oracle\product\10.2.0\oradata\stb'
STANDBY_FILE_MANAGEMENT=AUTO

6. Standby instance service and password file creation
On windows platform, an instance service and password file will be required.
F:\ORACLE\PRODUCT\9.2\DATABASE\> ORADIM –NEW –SID STB –INTPWD ORACLE –STARTMODE M
On platform other than windows, you will also need password file to permit other nodes in standby environment to access local database with sys privilege.
[oracle@database dbs]$ orapwd file=PWDstb.ora password=oracle entries=20 force=y
I once forgot to generate a password file for the “stb” node with the same sys password on “smart”, then “smart” node failed to coordinate with “stb” even it tried thousands times before I shut it down.

7. Set ORACLE_SID variable properly
Windows:
C:\> set oracle_sid=stb
UNIX:
$export ORACLE_SID=STB

8. Configure listeners and TNS names for both kind of nodes
Listener.ora example:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = whole)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (SID_NAME = whole)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = stb)
      (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
      (SID_NAME = stb)
    )
  )

C:\> lsnrctl stop
C:\> lsnrctl start

Modify tnsnames.ora and tnsping each other to make sure the oracle net service running okay.

9. Standby database control file generation
On the primary database.
  SQL> STARTUP MOUNT PFILE=’INITSMART.ORA’
  SQL> ALTER DATABASE CREATE STANDBY DATABASE CONTROLFILE AS ‘D:\CONTROL01.CTL’;
  SQL> ALTER DATABASE OPEN;

10. Startups
Copy the standby control file to stb node and:
  SQL> STARTUP MOUNT PFILE=’INITSTB.ORA’
  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Check out the result of our configuration on primary node with such command:
  SQL> ALTER SYSTEM SWITCH LOGFILE;
See if standby nodes have newly create archive log file.
  SQL> SELECT SEQUENCE# FROM V$ARCHIVED_LOG;

On the circumstance of primary node crash, we need a capable and functional database to take over the task as soon as possible. So, the first step is to finish managed recovery mode.
  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
  SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
  SQL> SHUTDOWN IMMEDIATE
  Cold backup here.
  SQL> STARTUP

On the circumstance to open the standby database in read-only mode, in order to query something in the stand by database, we need these steps to achieve our goal.
  SQL> RECOVER CANCEL / RECOVER MANAGED STANDBY DATABASE CANCEL;
  SQL> ALTER DATABASE OPEN READ ONLY;
  Bring back standby database to manual or managed recovery mode.
  SQL> RECOVER STANDBY DATABASE / RECOVER MANAGED STANDBY DATABASE TIME OUT 15;

Manual switch to standby database:
  Current primary node to standby node:
  SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
  SQL> SHUTDOWN IMMEDIATE
  SQL> STARTUP MOUNT
  SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

  Current standby node to primary node:
  SQL> ALTER DATABASE TO SWITCHOVER TO PRIMARY;
  SQL> SHUTDOWN IMMEDIATE
  SQL> STARTUP
  SQL> ALTER SYSTEM SWITCH LOGFILE;