Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to build dataguard". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to build dataguard.

This solution is suitable for database in production and operation. When the downtime of DB server is very harsh, or can not be maintained, such as payment, e-commerce and other services, Dataguard can be deployed online through this scheme, without any impact on the customer system.

I. basic information

Operating system: windows2008

Database: 11.2.0.3

Configuration Information:

Specify hostname IP address db_namesidDB_UNIQUE_NAMEService_name main library dg1192.168.16.14orclorclorclorcl standby library dg2192.168.16.15orclorclorclbakorclbak

II. Preparatory work

Create the corresponding primary database and standby database as required, and delete the data files and control files of the standby database. All paths of the two databases need to be the same.

Third, set up the database

Operation of the main library:

1. Confirm the main library parameters

SQL > select name,open_mode,database_role,log_mode,force_logging from v$database NAME OPEN_MODE DATABASE_ROLE LOG_MODE FOR--ORCL READ WRITE PRIMARY ARCHIVELOG YESSQL > show parameter nameNAME TYPE VALUE-- db_file_name_convert stringdb_name string Orcldb_unique_name string orclglobal_names boolean FALSEinstance_name string orcllock_name_space stringlog_file_name_convert stringprocessor_group_name stringservice_names string orcl

two。 Set the archiving mode of the database

SQL > archive log list; database log mode archiving mode automatic archiving enables archiving end point E:\ U01\ fast_recovery_area\ orcl earliest online log sequence 833 next archive log sequence 835 current log sequence 835

Settings for archive mode:

1 、 shutdown abort;2 、 alter database mount;3 、 alter database archive log;4 、 alter database open;3. Open force loggingSQL > alter database force logging;SQL > select name,DB_UNIQUE_NAME,database_role,log_mode,force_logging from v$database NAME DB_UNIQUE_NAME DATABASE_ROLE LOG_MODE FOR--ORCL orcl PRIMARY ARCHIVELOG YES

4. Main library configuration snooping

SID_LIST_LISTENER = (SID_LIST = (SID_NAME = CLRExtProc) (ORACLE_HOME = E:\ U01\ product\ 11.2.0\ dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:E:\ U01\ product\ 11.2.0\ dbhome_1\ bin\ oraclr11.dll")) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS)) = (PROTOCOL = TCP) (HOST = WIN-QS00S09CJ12) (PORT = 1521) (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521) ADR_BASE_LISTENER = E:\ U01

5. Configure the TNSNAMES.ORA of the main library and transfer it to the standby library together with the password file

# tnsnames.ora Network Configuration File: e:\ U01\ product\ 11.2.0\ dbhome_1\ NETWORK\ ADMIN\ tnsnames.ora# Generated by Oracle configuration tools.ORCL= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SID = ORCL) (UR=A)) ORCLBAK = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.14.15) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orclbak) (UR=A)-- remember to add this here Cannot connect to the standby library in nomount)

Transfer the listening file to the standby library:

6. Modify pfile parameters of the main library

-- provided that spfile is used to start and the business cannot be stopped, the following command can be used to set it dynamically:

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG= (orcl,orclbak) 'ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=' LOCATION=E:\ U01\ fast_recovery_area\ orcl VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orclbak LGWR ASYNC affirm VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclbak' ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE' ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='DEFER';-- set the shutdown ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO'; ALTER SYSTEM SET FAL_SERVER=orclbak here first ALTER SYSTEM SET FAL_CLIENT=orcl; is optional. If the path is the same, ignore the following actions: ALTER SYSTEM SET DB_FILE_NAME_CONVERT='E:\ U01\ oradata\ orcl','E:\ U01\ oradata\ orcl' scope=spfile;ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='E:\ U01\ oradata\ orcl','E:\ U01\ oradata\ orcl' scope=spfile

7. Full backup master library, and transfer the backup set to the same location as the standby library

Run {allocate channel c0 device type disk; allocate channel C1 device type disk; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'e:\ orabak\ rman/%F'; backup database format'e:\ orabak\ ora11g_full_db_%d_%T_%u.bak'; BACKUP ARCHIVELOG ALL FORMAT'e:\ orabak\ ora11g_arc_%s_%p_%t.bak';}

8. Modify the parameters of the slave library and start the slave library to the nomount state (refer to the main library, mainly the reverse of service_name)

DB_UNIQUE_NAME=orclbakLOG_ARCHIVE_CONFIG='DG_CONFIG= (orcl,orclbak) 'LOG_ARCHIVE_DEST_1=' LOCATION=E:\ U01\ fast_recovery_area\ orcl VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orclbak' LOG_ARCHIVE_DEST_2= 'SERVICE=orcl LGWR ASYNC affirm VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=orclFAL_CLIENT=orclbakSTANDBY_FILE_MANAGEMENT=AUTO

9. Start standby listening

# listener.ora Network Configuration File: e:\ U01\ product\ 11.2.0\ dbhome_1\ network\ admin\ listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = E:\ U01\ product\ 11.2.0\ dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:E:\ U01\ product\ 11 .2.0\ dbhome_1\ bin\ oraclr11.dll ")) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = WIN-4GH7UKQEEOC) (PORT = 1521)) (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521) SID_LIST_LISTENER = (SID_LIST = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = E:\ U01\ product) \ 11.2.0\ dbhome_1) (SID_NAME = orcl)) ADR_BASE_LISTENER = E:\ U01

10. Restore the reserve database

C:\ Users\ Administrator > rman target / recovery Manager: Release 11.2.0.3.0-Production on Friday February 26 17:35:52 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to target database: ORCL (DBID=1389423916) RMAN > connect auxiliary sys/*@orclbak connected to secondary database: ORCL (not loaded)

Start replicating the database:

RMAN > duplicate target database for standby nofilenamecheck; start Duplicate Db from 26 to February to 16 replace the channel assigned by the recovery directory with the target database control file: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=2091 device type = content of the DISK memory script: {restore clone standby controlfile } executing memory script to start restore on 26-February-16 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting to restore data file backup set channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: reading backup fragment D:\ ORABAK\ Cmai 1389423916-20160226-00 channel ORA_AUX_DISK_1: segment handle = D:\ ORABAK\ C Mel 1389423916-20160226-00 tag = TAG20160226T140701 channel ORA_AUX_DISK_1: backup fragment 1 channel ORA_AUX_DISK_1: restore complete Time: 00:00:04 output file name = E:\ U01\ ORADATA\ ORCL\ CONTROL01.CTL complete the contents of restore's 26-February-16 memory script: {sql clone 'alter database mount standby database' } executing memory script sql statement: contents of alter database mount standby database memory script: {set newname for tempfile 1 to "E:\ U01\ ORADATA\ ORCL\ TEMP01.DBF"; switch clone tempfile all; set newname for datafile 1 to "E:\ U01\ ORADATA\ ORCL\ SYSTEM01.DBF"; set newname for datafile 2 to "E:\ U01\ ORADATA\ ORCL\ SYSAUX01.DBF"; set newname for datafile 3 to "E:\ U01\ ORADATA\ ORCL\ UNDOTBS01.DBF" Set newname for datafile 4 to "E:\ U01\ ORADATA\ ORCL\ USERS01.DBF"; set newname for datafile 5 to "E:\ U01\ ORADATA\ ORCL\ GIS01.DBF"; set newname for datafile 6 to "E:\ U01\ ORADATA\ ORCL\ GIS02.DBF"; set newname for datafile 7 to "E:\ U01\ ORADATA\ ORCL\ GIS03.DBF"; set newname for datafile 8 to "E:\ U01\ ORADATA\ ORCL\ GIS04.DBF"; set newname for datafile 9 to "E:\ U01\ ORADATA\ ORCL\ GIS05.DBF" Set newname for datafile 10 to "E:\ U01\ ORADATA\ ORCL\ GIS06.DBF"; set newname for datafile 11 to "E:\ U01\ ORADATA\ ORCL\ GIS07.DBF"; set newname for datafile 12 to "E:\ U01\ ORADATA\ ORCL\ GIS08.DBF"; set newname for datafile 13 to "E:\ U01\ ORADATA\ ORCL\ GIS09.DBF"; set newname for datafile 14 to "E:\ U01\ ORADATA\ ORCL\ GIS10.DBF"; restore clone database } executing memory script executing command: SET NEWNAME temporary file 1 has been renamed to E:\ U01\ ORADATA\ ORCL\ TEMP01.DBF in the control file: SET NEWNAME is executing command: SET NEWNAME is executing command Execute command: SET NEWNAME is executing command: SET NEWNAME starts restore on 26-February-16 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting to restore data file backup set channel ORA_AUX_DISK_1: specifying restore from backup set Data file channel ORA_AUX_DISK_1: restore data file 00001 to E:\ U01\ ORADATA\ ORCL\ SYSTEM01.DBF channel ORA_AUX_DISK_1: restore data file 00002 to E:\ U01\ ORADATA\ ORCL\ SYSAUX01.DBF channel ORA_AUX_DISK_1: restore data file 00006 to E:\ U01\ ORADATA\ ORCL\ GIS02.DBF channel ORA_AUX_DISK_1: restore data file 00008 to E:\ U01\ ORADATA\ ORCL\ GIS04.DBF channel ORA_AUX_DISK_1: restore data file 00010 to E:\ U01\ ORADATA\ ORCL\ GIS06.DBF channel ORA_AUX_DISK_1: restore data file 00012 to E:\ U01\ ORADATA\ ORCL\ GIS08.DBF channel ORA_AUX_DISK_1: restore data file 00014 to E:\ U01\ ORADATA\ ORCL\ GIS10.DBF channel ORA_AUX_DISK_1: positive After reading backup fragment D:\ ORABAK\ ORCL_FULL_DB_ORCL_20160226_39QUSTI8.BAK channel ORA_AUX_DISK_1: segment handle = D:\ ORABAK\ ORCL_FULL_DB_ORCL_20160226_39QUSTI8.BAK tag = TAG20160226T105816 channel ORA_AUX_DISK_1: backup fragment 1 channel ORA_AUX_DISK_1: restore completed Time: 02:08:38 channel ORA_AUX_DISK_1: starting to restore data files backup set channel ORA_AUX_DISK_1: specifying data file channel to restore from backup set ORA_AUX_DISK_1: restore data file 00003 to E:\ U01\ ORADATA\ ORCL\ UNDOTBS01.DBF channel ORA_AUX_DISK_1: restore data file 00004 to E:\ U01\ ORADATA\ ORCL\ USERS01.DBF channel ORA_AUX_DISK_1: restore data file 00005 to E:\ U01\ ORADATA\ ORCL\ GIS01.DBF channel ORA_AUX_DISK_1: restore data file 00007 to E:\ U01\ ORADATA\ ORCL\ GIS03.DBF channel ORA_AUX_DISK_1: restore data file 00009 to E:\ U01\ ORADATA\ ORCL\ GIS05.DBF channel ORA_AUX_DISK_1: restore data file 00011 to E :\ U01\ ORADATA\ ORCL\ GIS07.DBF channel ORA_AUX_DISK_1: restore data file 00013 to E:\ U01\ ORADATA\ ORCL\ GIS09.DBF channel ORA_AUX_DISK_1: reading backup fragment D:\ ORABAK\ ORCL_FULL_DB_ORCL_20160226_38QUSTI8.BAK channel ORA_AUX_DISK_1: segment handle = D:\ ORABAK\ ORCL_FULL_DB_ORCL_20160226_38QUSTI8.BAK tag = TAG20160226T105816 channel ORA _ AUX_DISK_1: restored backup fragment 1 channel ORA_AUX_DISK_1: restore completed Time: 02:36:38 to complete the contents of the restore 26-February-16 memory script: {switch clone datafile all } executing memory script data file 1 converted to data file copy input data file copy RECID=2 STAMP=904861333 file name = E:\ U01\ ORADATA\ ORCL\ SYSTEM01.DBF data file 2 converted to data file copy input data file copy RECID=3 STAMP=904861334 file name = E:\ U01\ ORADATA\ ORCL\ SYSAUX01.DBF data file 3 converted to data file copy input data file copy RECID=4 STAMP=904861334 file name = E:\ U01\ ORADATA\ ORCL\ UNDOTBS01.DBF data file 4 converted to data file copy input data file copy RECID=5 STAMP=904861334 file name = E:\ U01\ ORADATA\ ORCL\ USERS01.DBF data file 5 converted to data file copy RECID=6 STAMP=904861334 file name = E:\ U01\ ORADATA\ ORCL\ GIS01.DBF data file 6 converted to data file copy input data file copy RECID=7 STAMP=904861334 file name = E:\ U01\ ORADATA\ ORCL\ GIS02.DBF data file 7 converted to data file copy input data file copy RECID=8 STAMP=904861334 file name = E:\ U01\ ORADATA\ ORCL\ GIS03.DBF data file 8 converted to data file copy RECID=9 STAMP=904861334 file name = E:\ U01\ ORADATA\ ORCL\ GIS04.DBF data file 9 converted to data file copy input data file copy RECID=10 STAMP=904861335 file name = E:\ U01\ ORADATA\ ORCL\ GIS05.DBF data file 10 converted to data file copy input data file copy RECID=11 STAMP=904861335 file name = E:\ U01\ ORADATA\ ORCL\ GIS06.DBF data file 11 converted to data file copy RECID=12 STAMP=904861335 file name = E:\ U01\ ORADATA\ ORCL\ GIS07.DBF data file 12 converted to data file copy input data file copy RECID=13 STAMP=904861335 file name = E:\ U01\ ORADATA\ ORCL\ GIS08.DBF data file 13 converted to data file copy input data file copy RECID=14 STAMP=904861335 file name = E:\ U01\ ORADATA\ ORCL\ GIS09.DBF data file 14 converted to data file copy input data file copy RECID=15 STAMP=904861335 file name = E:\ U01\ ORADATA\ ORCL\ GIS10.DBF completed Duplicate Db 26-February-16

twelve。 Set the LOG_ARCHIVE_DEST_STATE_2 of the main library to ENABLE

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE'

13. Add standby redo log-if standby redo log has been established in the main library, this step can be omitted

SQL > select member from v$logfile

# add STANDBY LOGFILE to the main library

ALTER DATABASE ADD STANDBY LOGFILE group 4 ('E:\ U01\ oradata\ standby_log\ slog1.rdo') SIZE 200m alter DATABASE ADD STANDBY LOGFILE group 5 ('E:\ U01\ oradata\ standby_log\ slog2.rdo') SIZE 200m alter DATABASE ADD STANDBY LOGFILE group 6 ('E:\ U01\ oradata\ standby_log\ slog3.rdo') SIZE 200m alter DATABASE ADD STANDBY LOGFILE group 7 ('E:\ U01\ oradata\ standby_log\ slog4.rdo') SIZE 200m

# add STANDBY LOGFILE to the repository

SQL > select member from v$logfile

ALTER DATABASE ADD STANDBY LOGFILE group 4 ('E:\ U01\ oradata\ standby_log\ slog1.rdo') SIZE 200m alter DATABASE ADD STANDBY LOGFILE group 5 ('E:\ U01\ oradata\ standby_log\ slog2.rdo') SIZE 200m alter DATABASE ADD STANDBY LOGFILE group 6 ('E:\ U01\ oradata\ standby_log\ slog3.rdo') SIZE 200m alter DATABASE ADD STANDBY LOGFILE group 7 ('E:\ U01\ oradata\ standby_log\ slog4.rdo') SIZE 200m

14. Finally, open active dataguard in the repository.

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL > ALTER DATABASE OPEN;SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; so far, I believe you have a deeper understanding of "how to build dataguard". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.