1. Environmental information:
System:
Oracle-linux 5.7
Database version:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
PL/SQL Release 11.2.0.3.0-production
CORE 11.2.0.3.0 Production
TNS for Linux:version 11.2.0.3.0-production
Nlsrtl Version 11.2.0.3.0-production
Main Library: 192.168.12.31 Sid:yoon Db_unique_name:yoon
Library: 192.168.12.231 Sid:yoon DB_UNIQUE_NAME:YOONDG
2. Check if DG is installed:
Sql> SELECT * from v$option where parameter = ' Oracle Data Guard ';
PARAMETER VALUE
-------------------- --------------------
Oracle Data Guard TRUE
True indicates that the installation can be configured or the appropriate software needs to be installed
3. Check whether the main library is open for archiving:
sql> archive log list;
Database log mode No Archive mode
Automatic Archival Disabled
Archive Destination Use_db_recovery_file_dest
Oldest online log sequence 4
Current Log sequence 6
4. Set the main library to archive mode:
sql> shutdown Immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Sql> Startup Mount
ORACLE instance started.
Total System Global area 2505338880 bytes
Fixed Size 2230952 bytes
Variable Size 553649496 bytes
Database buffers 1946157056 bytes
Redo buffers 3301376 bytes
Database mounted.
sql> ALTER DATABASE Archivelog;
Database altered.
sql> ALTER DATABASE open;
Database altered.
sql> archive log list;
Database Log Mode Archive mode
Automatic Archival Enabled
Archive Destination Use_db_recovery_file_dest
Oldest online log sequence 4
Next Log sequence to archive 6
Current Log sequence 6
5. Set the main library to force logging:
sql> ALTER DATABASE force logging;
Database altered.
6. Create Standy log files:
Sql> select Group#,member from V$logfile;
group# MEMBER
------- -----------------------------------
3/opt/oracle/oradata/yoon/redo03.log
2/opt/oracle/oradata/yoon/redo02.log
1/opt/oracle/oradata/yoon/redo01.log
Add to:
sql> ALTER DATABASE Add standby logfile Group 7 '/opt/oracle/oradata/yoon/standby07.log ' size 50m;
Database altered.
sql> ALTER DATABASE ADD standby logfile Group 8 '/opt/oracle/oradata/yoon/standby08.log ' size 50m;
Database altered.
sql> ALTER DATABASE ADD standby logfile Group 9 '/opt/oracle/oradata/yoon/standby09.log ' size 50m;
Database altered.
sql> ALTER DATABASE Add standby logfile Group of '/opt/oracle/oradata/yoon/standby10.log ' size 50m;
Database altered.
7. Create Standby Control File:
sql> shutdown Immediate
Sql> Startup Mount
sql> ALTER DATABASE create standby Controlfile as '/opt/oracle/oradata/yoon/standby_control01.ctl ';
Database altered.
Sql>alter database open;
8. Set Db_unique_name:
The default db_name and Db_unique_name are the same, and the main library is Yoon
In the Configuration DG, the primary and standby SIDs can be the same, but,db_unique_name cannot be the same, the standby is set to: YOONDG
Sql> Show parameter db_unique_name;
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
Db_unique_name String Yoon
-Note that although the default Db_unique_name and db_name are consistent, they need to be set explicitly, otherwise this parameter is not in SPFile
Sql> alter system set Db_unique_name=yoon Scope=spfile;
System altered.
9. Configure the main library Listener.ora:
# Listener.ora Network Configuration File:/opt/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
Sid_list_listener=
(Sid_list =
(Sid_desc =
(Global_dbname = yoon)
(Oracle_home =/opt/oracle/product/11.2.0/db_1/)
(Sid_name = yoon)
)
)
LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.12.31) (PORT = 1521))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
)
)
Adr_base_listener =/opt/oracle
Configure Tnsnames.ora
# Listener.ora Network Configuration File:/opt/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
YOON =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.12.31) (PORT = 1521))
)
(Connect_data =
(service_name = yoon)
)
)
YOONDG =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.12.231) (PORT = 1521))
)
(Connect_data =
(service_name = YOONDG)
)
)
10, copy the main library Listener.ora and Tnsnames.ora to the repository modification
[Email protected] admin]# SCP listener.ora tnsnames.ora [email protected]:/opt/oracle/product/11.2.0/db_1/ network/admin/
Configure Listener.ora
# Listener.ora Network Configuration File:/opt/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
Sid_list_listener=
(Sid_list =
(Sid_desc =
(Global_dbname = yoon)
(Oracle_home =/opt/oracle/product/11.2.0/db_1/)
(Sid_name = yoon)
)
)
LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.12.231) (PORT = 1521))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
)
)
Adr_base_listener =/opt/oracle
Configure Tnsnames.ora
# Listener.ora Network Configuration File:/opt/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
YOONDG =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.12.231) (PORT = 1521))
)
(Connect_data =
(service_name = YOONDG)
)
)
YOON =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.12.31) (PORT = 1521))
)
(Connect_data =
(service_name = yoon)
)
)
11, after the configuration after the master and the Oralce users under test whether it can ping through
Main:
[Email protected] ~]$ Tnsping Yoon
Used TNSNames Adapter to resolve the alias
Attempting to contact (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.12.31) (PORT = 1521)) (CON Nect_data = (service_name = yoon)))
OK (0 msec)
[Email protected] ~]$ tnsping YOONDG
Used TNSNames Adapter to resolve the alias
Attempting to contact (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.12.231) (PORT = 1521)) (CO Nnect_data = (service_name = YOONDG)))
OK (Ten msec)
Preparation:
[Email protected] ~]$ Tnsping Yoon
Used TNSNames Adapter to resolve the alias
Attempting to contact (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.12.31) (PORT = 1521)) (CON Nect_data = (service_name = yoon)))
OK (0 msec)
[Email protected] ~]$ tnsping YOONDG
Used TNSNames Adapter to resolve the alias
Attempting to contact (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.12.231) (PORT = 1521)) (CO Nnect_data = (service_name = YOONDG)))
OK (0 msec)
12. Redo Log Transfer configuration
A. Configure the archive log location:
Sql> alter system set log_archive_dest_1= ' Location=/opt/oracle/archive_bak valid_for= (all_logfiles,primary_roles ) Db_unique_name=yoon ' Scope=spfile;
System altered.
B. Configure the redo log to the backup library:
Sql> alter system set log_archive_dest_2= ' Service=yoondg lgwr sync valid_for= (online_logfiles,primary_role) db_ Unique_name=yoondg ' Scope=spfile;
System altered.
13, Configuration Fal_server
Main:
Sql> alter system set fal_server= ' YOONDG ';
System altered.
14. The name of another library in the Data Guard configuration
Sql> alter system set log_archive_config= ' dg_config= (YOON,YOONDG) ';
System altered.
Sql> alter system set log_archive_dest_state_1=enable Scope=spfile;
System altered.
Sql> alter system set log_archive_dest_state_2=enable Scope=spfile;
System altered.
Sql> create Pfile from SPFile;
File created.
Sql> create SPFile from Pfile;
File created.
15. View the main library Inityoon.ora information
yoon.__db_cache_size=1946157056
yoon.__java_pool_size=16777216
yoon.__large_pool_size=16777216
Yoon.__oracle_base= '/opt/oracle ' #ORACLE_BASE set from environment
yoon.__pga_aggregate_target=838860800
yoon.__sga_target=2516582400
Yoon.__shared_io_pool_size=0
yoon.__shared_pool_size=520093696
Yoon.__streams_pool_size=0
*.audit_file_dest= '/opt/oracle/admin/yoon/adump '
*.audit_trail= ' DB '
*.compatible= ' 11.2.0.0.0 '
*.control_files= '/opt/oracle/oradata/yoon/control01.ctl ', '/opt/oracle/fast_recovery_area/yoon/control02.ctl '
*.db_block_size=8192
*.db_domain= "
*.db_name= ' Yoon '
*.db_recovery_file_dest= '/opt/oracle/fast_recovery_area '
*.db_recovery_file_dest_size=4322230272
*.db_unique_name= ' YOON '
*.diagnostic_dest= '/opt/oracle '
*.dispatchers= ' (protocol=tcp) (SERVICE=YOONXDB) '
*.fal_server= ' YOONDG '
*.log_archive_config= ' dg_config= (YOON,YOONDG) '
*.log_archive_dest_1= ' Location=/opt/oracle/archive_bak valid_for= (all_logfiles,primary_roles) db_unique_name= Yoon
*.log_archive_dest_2= ' service=yoondg lgwr sync valid_for= (online_logfiles,primary_role) DB_UNIQUE_NAME=YOONDG '
*.log_archive_dest_state_1= ' ENABLE '
*.log_archive_dest_state_2= ' ENABLE '
*.open_cursors=300
*.pga_aggregate_target=837812224
*.processes=150
*.remote_login_passwordfile= ' EXCLUSIVE '
*.standby_file_management= ' AUTO '
*.sga_target=2514485248
*.undo_tablespace= ' UNDOTBS1 '
16. Copy the data files, control files, parameter files and password files of the main library to the directory corresponding to the repository.
[Email protected] oracle]# scp-r admin/fast_recovery_area/oradata/ [email protected]:/opt/oracle/
17. Modify the Repository parameter file
yoon.__db_cache_size=1946157056
yoon.__java_pool_size=16777216
yoon.__large_pool_size=16777216
Yoon.__oracle_base= '/opt/oracle ' #ORACLE_BASE set from environment
yoon.__pga_aggregate_target=838860800
yoon.__sga_target=2516582400
Yoon.__shared_io_pool_size=0
yoon.__shared_pool_size=520093696
Yoon.__streams_pool_size=0
*.audit_file_dest= '/opt/oracle/admin/yoon/adump '
*.audit_trail= ' DB '
*.compatible= ' 11.2.0.0.0 '
*.control_files= '/opt/oracle/oradata/yoon/control01.ctl ', '/opt/oracle/fast_recovery_area/yoon/control02.ctl '
*.db_block_size=8192
*.db_domain= "
*.db_name= ' Yoon '
*.db_recovery_file_dest= '/opt/oracle/fast_recovery_area '
*.db_recovery_file_dest_size=4322230272
*.db_unique_name= ' YOONDG '
*.diagnostic_dest= '/opt/oracle '
*.dispatchers= ' (protocol=tcp) (SERVICE=YOONXDB) '
*.fal_server= ' Yoon '
*.log_archive_config= ' dg_config= (yoondg,yoon) '
*.log_archive_dest_1= ' Location=/opt/oracle/archive_bak valid_for= (all_logfiles,primary_roles) db_unique_name= Yoondg '
*.log_archive_dest_2= ' Service=yoon lgwr sync valid_for= (online_logfiles,primary_role) Db_unique_name=yoon '
*.log_archive_dest_state_1= ' ENABLE '
*.log_archive_dest_state_2= ' ENABLE '
*.open_cursors=300
*.pga_aggregate_target=837812224
*.processes=150
*.remote_login_passwordfile= ' EXCLUSIVE '
*.standby_file_management= ' AUTO '
*.sga_target=2514485248
*.undo_tablespace= ' UNDOTBS1 '
18, in the standby library through Pfile create SPFile
19. Delete the control files Control01.ctl and Control02.ctl of the standby library
Replace the standby_control01.ctl with Control01.ctl and Control02.ctl
20. Start the Standby library
Sql> Startup Nomount
ORACLE instance started.
Total System Global area 2505338880 bytes
Fixed Size 2230952 bytes
Variable Size 553649496 bytes
Database buffers 1946157056 bytes
Redo buffers 3301376 bytes
Sql>
Sql> ALTER DATABASE mount standby database;
Database altered.
Enable Redo Apps
sql> ALTER database RECOVER MANAGED STANDBY database DISCONNECT from SESSION;
Stop standby
sql> ALTER database RECOVER MANAGED STANDBY database CANCEL;
Sql>shutdown immediate;
21, the standby server management mode and read-only mode
A. Booting to management mode
Sql>shutdown immediate;
Sql>startup Nomount;
Sql>alter database mount standby database;
Sql>alter database recover managed standby database disconnect from session;
B. Booting to read-only mode
Sql>shutdown immediate;
Sql>startup Nomount;
Sql>alter database mount standby database;
Sql> ALTER DATABASE open read only;
C. Under Manage recovery mode to read-only mode
sql> Recover managed standby database cancel;
Sql> ALTER DATABASE open read only;
D. From read-only mode to management recovery mode
sql> Recover managed Standby database disconnect from session;
22, DG Start:
Monitoring: Start the library and then start the main library
Database: Start-up repository and then main library
Off: Opposite to start.
oracle-11g-Configuration Dataguard