-
Oracle 11g DataGuard configuration
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
Red Hat Enterprise Linux Server release 5.3 (Tikanga)
1. Test environment preparation
1. The main database and the standby database have the same Oracle version and run on the same platform such as Linux, but the server hardware can be different.
2. The primary database must run in archive mode, and be sure to open FORCE LOGGING on the primary database to prevent users from writing redo through nologging and other methods, causing the corresponding operations to fail to be transmitted to the standby database.
3. Both primary and standby databases can be applied in single instance or RAC architecture, and the same data guard configuration can mix logical standby and physical standby.
4. It is recommended that the database must use the same storage architecture. For example, if ASM/OMF is used for storage, ASM/OMF must be used regardless of primary or standby.
5. The environment of the standby library is the same as that of the primary library.
2. Adjust the main library
1. Change the main database to force log mode
alter database force logging;
2. Create a password
orapwd file=$ORACLE_HOME/dbs/orapwtest password=test entries=3
3. Modify the initialization parameters of the main library
db_unique_name ='test01'
db_name specifies the name of the database, db_unique_name specifies the different names of the database (primary database and standby database), which is the unique identification of db_name. In fact, it is to distinguish databases with the same db_name to facilitate application and management. It is a parameter after 10g.
log_archive_config='DG_CONFIG=(test01,test02)'
test01, test02 is .db_unique_name
log_archive_dest_1='location=/u01/app/oracle/arch/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test01'
log_archive_dest_2='SERVICE=test_db_02 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test02'
Determine log_archive_dest_state_1
log_archive_dest_state_1= enable #The default is enable
log_archive_dest_state_2= enable
/*The following parts are used for switching the host to the standby database*/
fal_server=test_db_02
fal_client=test_db_01
standby_file_management=auto
#If the primary and secondary database files or paths are different
db_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test'
log_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test'
It is recommended that the parameter file use spfile, so that the parameters can be modified using commands in the non-stop library.
4. Make sure the database is running in archivelog mode
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Current log sequence 4
Adjust the database to archivelog mode
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
5. Create a standby log on the main database (for switching)
alter database recover managed standby database cancel;#Not executed
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
mkdir -p /u01/app/oracle/standby/test
SQL>alter database add standby logfile group 4('/u01/app/oracle/standby/test/stdby_redo04.log') size 50M;
SQL>alter database add standby logfile group 5('/u01/app/oracle/standby/test/stdby_redo05.log') size 50M;
SQL>alter database add standby logfile group 6('/u01/app/oracle/standby/test/stdby_redo06.log') size 50M;
SQL>alter database recover managed standby database disconnect from session;#Not executed
3. Create standby database
1. Create pwdfile in standby database
orapwd file=$ORACLE_HOME/dbs/orapwtest password=test entries=3
The password must be the same as the primary database
2. Modify the standby database initialization parameters that are different from those of the primary database as follows:
db_unique_name=test02
log_archive_dest_1='location=/u01/app/oracle/arch/test valid_for=(all_logfiles,all_roles) db_unique_name=test02'
log_archive_dest_2='service=test_db_01 ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=test01'
fal_client='test_db_02'
fal_server='test_db_01'
db_file_name_convert='/u01/app/oracle/oradata/TEST01/datafile','/u01/app/oracle/oradata/TEST02/datafile'
log_file_name_convert='/u01/app/oracle/oradata/TEST01/onlinelog','/u01/app/oracle/oradata/TEST02/onlinelog', '/u01/app/oracle/flash_recovery_area/TEST01/onlinelog','/u01 /app/oracle/flash_recovery_area/TEST01/onlinelog'
After modification, use the following statement to view
SQL> select * from V$DATAGUARD_CONFIG;
DB_UNIQUE_NAME
-------------------------------------------------- ----------------------------------------
test01
test02
4. Create the corresponding directories, dump file directory, data file directory, and archive directory
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/arch/test
5. Configure the listener.ora and tnsnames.ora files of the standby database to restart listening with the main database and check whether tnsping can pass.
6. Start the standby database to nomount state
SQL> startup nomount
7. Use the duplicate function of RMAN to create a standby database. 11g can copy it directly to the standby database without making a backup:
RMAN> rman auxiliary /
connect target sys/test@test_db_01
duplicate target database for standby dorecover nofilenamecheck;
Check the status of standby database
SQL> select database_role,protection_mode,protection_level from v$database;
SQL> select sequence# ,applied from v$archived_log order by sequence#;
8. Start the Redo application main library
SQL> alter system archive log current;
Standby database, 11g can open the database application log read-only
SQL> alter database open read only;
SQL> alter database recover managed standby database disconnect from session;
You can also use alter database recover managed standby database using current logfile disconnect from session; application log.
If there is no using current logfile, apply standby archived log.
Using current logfile is applied when the log is transferred to standby redo log. It is real-time and must have standby redo log.
4. Management mode and read-only mode of standby server
1. Boot into management mode
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session;
2. Boot to read-only mode
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database open read only;
(3) If you go to read-only mode in management recovery mode
SQL> recover managed standby database cancel;
SQL> alter database open read only;
At this time, you can add temporary data files to the database (this is not backed up during hot backup)
like
alter tablespace temp add tempfile '/u02/oradata/test/temp01.dbf' size 100M;
5. From read-only mode to managed recovery mode
SQL> recover managed standby database disconnect from session;
5. Switch between active and standby databases
lsnrctl stop
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database recover managed standby database disconnect;
lsnrctl start
Backup
SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup
6. The attachment below is the content of the configuration file:
listener.ora
LISTENER =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle6)(PORT = 1521))
)
tnsnames.ora
TEST_DB_02 =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.202.5)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test02)
)
)
TEST_DB_01 =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.202.6)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = test01)
)
)
pfile
test.__db_cache_size=1526726656
test.__java_pool_size=16777216
test.__large_pool_size=16777216
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=1342177280
test.__sga_target=2013265920
test.__shared_io_pool_size=0
test.__shared_pool_size=436207616
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/test/control01.ctl','/u01/app/oracle/oradata/test/control02.ctl','/u01/app/oracle/oradata/test/ control03.ctl'
*.db_block_size=8192
*.db_domain='greatsky.com'
*.db_name='test'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='test01'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_client='TEST_DB_01'
*.fal_server='TEST_DB_02'
*.log_archive_config='dg_config=(test01,test02)'
*.log_archive_dest_1='location=/u01/app/oracle/arch/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test01'
*.log_archive_dest_2='SERVICE=test_db_02 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test02'
*.memory_target=3347054592
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='test.greatsky.com','test01.greatsky.com'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'