Detailed configuration method and steps for Oracle10g physical DG

-- Test environment:
OS: Redhat linux (64)
Primary:
IP: 192.168.94.198
SID: dgdb1
Hostname: dg1
DB_UNIQUE_NAME: dgdb1
Database: 10.2.0.1 (64)

Standby:
IP: 192.168.94.199
SID: dgdb1
Hostname: dg2
DB_UNIQUE_NAME: dgdb1_s
Database: 10.2.0.1 (64)


-- Preparations before implementing DG
-- Enable database logging and database archivelog
-- Enable database logging
SQL> alter database force logging;
-- Check whether archivelog is enabled for the database
SQL> archive log list;
-- If archivelog is not enabled for the database, you must enable archivelog.
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
-- Check whether archivelog is enabled for the database
SQL> archive log list;


-- Create a directory (based on the actual situation, the primary and standby terminals must be consistent)
-- Standby:
Mkdir-p/export/home/oracle/product/10.2.0/oradata/dgdb1
Mkdir-p/export/home/oracle/product/10.2.0/admin/dgdb1/adump
Mkdir-p/export/home/oracle/product/10.2.0/admin/dgdb1/bdump
Mkdir-p/export/home/oracle/product/10.2.0/admin/dgdb1/cdump
Mkdir-p/export/home/oracle/product/10.2.0/admin/dgdb1/udump
Mkdir-p/export/home/oracle/product/10.2.0/admin/dgdb1/dpdump
Mkdir-p/export/home/oracle/product/10.2.0/admin/dgdb1/pfile
Mkdir-p/export/home/oracle/archive
Mkdir-p/export/home/oracle/bak
-- Primary:
Mkdir-p/export/home/oracle/archive
Mkdir-p/export/home/oracle/bak


-- Modify or add listener. ora and tnsnames. ora (or use a GUI tool to configure)
-- Note: The added parts in listener. ora are in brackets rather than out of brackets.
-- Primary end:
-- Listener. ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/export/home/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = dgdb1)
(ORACLE_HOME =/export/home/oracle/product/10.2.0)
(SID_NAME = dgdb1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dg1) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0 ))
)
)


-- Tnsnames. ora
Dgdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.94.198) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgdb1)
)
)

Dgdb1_s =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.94.199) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgdb1)
)
)


-- Standby end:
-- Listener. ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/export/home/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = dgdb1)
(ORACLE_HOME =/export/home/oracle/product/10.2.0)
(SID_NAME = dgdb1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = dg2) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0 ))
)
)
-- Tnsnames. ora
Dgdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.94.198) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgdb1)
)
)
Dgdb1_s =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.94.199) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgdb1)
)
)


-- Specific steps for implementing DG:
-- Generate pfile on primary and add corresponding parameters to generate the pfile required by standby.
-- Primary end:
Oracle $> sqlplus/as sysdba
SQL> CREATE PFILE = '/export/home/oracle/standby. ora' FROM SPFILE;
-- Edit the generated pfile ('/export/home/oracle/standby. ora') and add the following parameters:
*. Db_unique_name = 'dgdb _ s'
*. Fal_server = 'dgdb1'
*. Fal_client = 'dgdb _ s'
*. LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (dgdb1, dgdb_s )'
*. Log_archive_dest_1 = 'location =/export/home/oracle/archive VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = dgdb_s'
*. LOG_ARCHIVE_DEST_2 = 'service = dgdb1 lgwr async = 40960 VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = dgdb1'
*. LOG_ARCHIVE_DEST_STATE_1 = 'enable'
*. LOG_ARCHIVE_DEST_STATE_2 = 'enable'
*. Standby_archive_dest = '/export/home/oracle/archive'
*. Standby_file_management = 'auto'
-- Copy the modification to the standby end.
Oracle $ Scp/export/home/oracle/standby. ora oracle@192.168.94.199:/export/home/oracle/
-- Start an instance using pfile on standby
-- Use commands on the primary side to create a database password file and copy it to the same path on the standby side (if it already exists, you can copy it directly)
-- Manually create a database password file on the primary side
Oracle $ orapwd file =... password =...
-- Copy the Database Password File to standby on the primary side.
Oracle $ Scp/export/home/oracle/product/10.2.0/database/PWDdgdb1.ora oracle@192.168.94.199:/export/home/oracle/product/10.2.0/database
-- Standby end:
Oracle $ set oracle_sid = dgdb1
Oracle $ sqlplus/as sysdba
SQL> startup nomount pfile = '/export/home/oracle/standby. ora'
SQL> CREATE SPFILE FROM PFILE = '/export/home/oracle/standby. ora ';
-- Modify parameters on the primary side
-- Primary end:
Oracle $ sqlplus/as sysdba
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO scope = both;
SQL> ALTER SYSTEM SET fal_server = 'dgdb1 _ s' scope = both;
SQL> ALTER SYSTEM SET fal_client = 'dgdb1 'scope = both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (dgdb1_s, dgdb1) 'scope = both;
SQL> ALTER SYSTEM SET log_archive_dest_1 = 'location =/export/home/oracle/archive VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = dgdb1 'scope = both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'service = dgdb1_s LGWR ASYNC = 40960 VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = dgdb1_s 'scope = both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = 'enable' scope = both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = 'enable' scope = both;
SQL> ALTER SYSTEM SET standby_archive_dest = '/export/home/oracle/archive' scope = both;


-- Start using rman to back up the database on the primary end
-- Primary end:
Oracle $ rman target/
RMAN> backup full format = '/u01/app/oracle/bak/ora10g _ % d _ % T _ % s' database include current controlfile for standby plus archivelog format ='/u01 /app/oracle/bak/arch _ % d _ % T _ % s ';


-- After the backup is complete, copy the corresponding backup file to the corresponding directory of the cluster database (the directories must be consistent)
Oracle $ Scp/export/home/oracle/bak/* oracle@192.168.94.199:/export/home/oracle/bak/

-- Use duplicate to restore the standby Database
-- Primary end:
Oracle $ rman target/auxiliary sys/system @ dgdb1_s
RMAN> duplicate target database for standby nofilenamecheck dorecover;


-- Finishing work and enabling dg
-- Standby creates a standby log (at least one group more than redo ):
Oracle $ sqlplus/as sysdba
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo04.log') size 50 M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo05.log') size 50 M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo06.log') size 50 M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo07.log') size 50 M;
-- Enable the Service (standby side ):
SQL> alter database recover managed standby database disconnect from session;


-- Test whether the service is normal
-- Standby end (view the archived log number ):
SQL> SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;
-- Primary end (force a log switch ):
SQL> alter system switch logfile;
-- Standby end (view the archived log number ):
SQL> SELECT SEQUENCE #, FIRST_TIME, NEXT_TIME FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;
-- Standby end (view the number of the application archive log ):
SQL> SELECT SEQUENCE #, APPLIED FROM V $ ARCHIVED_LOG ORDER BY SEQUENCE #;


-- Switch between primary and standby:
-- Primary end:
SQL> SELECT SWITCHOVER_STATUS FROM V $ DATABASE;
-- Primary (the result of the previous step must be "to standby)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
-- OR (the result in the previous step is "sessions active" and cannot be solved)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
-- Primary end:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
-- Standby end:
SQL> SELECT SWITCHOVER_STATUS FROM V $ DATABASE;
-- Standby (the result of the previous step must be "to standby ):
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
-- OR (the result in the previous step is "sessions active" and cannot be solved)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
-- Open the new primary:
SQL> ALTER DATABASE OPEN;
-- OR (opened in read only mode after the last startup)
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;


-- Restart log apply services on the new standby (if necessary ):
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
-- OR (background Mode)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
-- OR (Real-time application redo)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
-- New primary end (start to send redo data to the new standby end ):
SQL> ALTER SYSTEM SWITCH LOGFILE;