ORACLE10G Physical DG Detailed configuration method and steps

--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)


--Preparation before DG implementation
--Open database logging and database Archivelog
--Open database logging
sql> ALTER DATABASE force logging;
--Check that the database is open Archivelog
sql> archive log list;
--If the database does not have archivelog turned on, Archivelog is turned on
sql> shutdown immediate;
sql> startup Mount;
sql> ALTER DATABASE Archivelog;
--Check that the database is open Archivelog
sql> archive log list;


--Create the appropriate directory (primary and standby are consistent depending on the situation)
--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 configure with GUI tools)
--Note: The new section in Listener.ora is enclosed in parentheses, not outside the 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)
)
)


--The steps to implement the DG specifically:
--Generate pfile on primary, and modify add parameters to generate standby required Pfile
--primary End:
Oracle$>sqlplus/as SYSDBA
sql> CREATE pfile= '/export/home/oracle/standby.ora ' from SPFILE;
--Edit the generated pfile file ('/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 to standby end after modification
Oracle$scp/export/home/oracle/standby.ora [Email protected]:/export/home/oracle/
--Start the instance using Pfile on the standby side
---Create the database password file with the command at the primary end and copy it to the same path on the standby side (if it already exists, can be copied directly)
--primary Manually create a database password file
Oracle$orapwd file= ... password= ...
--primary-side Copy Database password file to standby end
Oracle$scp/export/home/oracle/product/10.2.0/database/pwddgdb1.ora [Email protected]:/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 the corresponding 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 at 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 completed, copy the corresponding backup files to the corresponding directory of Concou (directory must be consistent)
oracle$scp/export/home/oracle/bak/* [Email protected]:/export/home/oracle/bak/

--Restore standby database using duplicate
--primary End:
Oracle$rman target/auxiliary Sys/[email protected]_s
Rman> duplicate target database for standby Nofilenamecheck dorecover;


--Finishing work and opening DG
The--standby side establishes standby log (at least one more group 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 50M;
sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo05.log ') size 50M;
sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo06.log ') size 50M;
sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo07.log ') size 50M;
--Open service (standby side):
sql> ALTER DATABASE recover managed standby database disconnect from session;


--Test whether the service is normal
--standby End (see Archive 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 (see Archive log number):
Sql> SELECT sequence#, First_time, next_time from V$archived_log ORDER by sequence#;
--standby End (see Application archive log number):
Sql> SELECT sequence#,applied from V$archived_log ORDER by sequence#;


--to achieve primary, standby switching:
--primary End:
Sql> SELECT switchover_status from V$database;
--primary end (The result of the previous step must be "to STANDBY")
Sql> ALTER DATABASE COMMIT to switchover to physical STANDBY;
--or (The previous step results in "SESSIONS ACTIVE" and cannot be resolved)
Sql>alter DATABASE COMMIT to switchover-physical STANDBY with SESSION SHUTDOWN;
--primary End:
Sql> SHUTDOWN IMMEDIATE;
Sql> STARTUP MOUNT;
--standby End:
Sql> SELECT switchover_status from V$database;
--standby end (The result of the previous step must be "to STANDBY"):
Sql> ALTER DATABASE COMMIT to switchover to PRIMARY;
--or (The previous step results in "SESSIONS ACTIVE" and cannot be resolved)
Sql>alter DATABASE COMMIT to switchover-PRIMARY with SESSION SHUTDOWN;
--Open the new primary:
Sql>alter DATABASE OPEN;
--or (opened in read only mode after the last boot)
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 sending redo data to the new standby end):
sql> ALTER SYSTEM SWITCH LOGFILE;

ORACLE10G Physical DG Detailed configuration method and steps