Shulou(Shulou.com)06/01 Report--

Oracle Data Guard is a set of high availability database solution provided by Oracle company. Data Guard ensures high availability of enterprise data and enables data protection and disaster recovery. Data Guard provides a comprehensive set of services to create, maintain, manage and monitor one or more standby databases, making it easy for users to deal with disasters and data corruption in Oracle production databases. Data Guard maintains the standby database to be consistent with the data and things of the primary database (production database). When the primary database is unexpectedly down or unavailable, Data Guard can switch any standby database to the primary database, thus minimizing the downtime of the database server.

Data Guard principle

The redo log is used to realize the real-time backup from the production database (master) to the standby database (the standby database keeps the data synchronized by applying the data changes on the primary database), and the active and standby servers can be switched with each other (that is, one of them can be promoted to the primary server).

Data Guard architecture

Primary Database (master database): a production database whose primary role in Data Guard is the database accessed by most applications.

Standby Database (standby database): the Standby database is a backup copy of the primary data, and the standby database can be a physical standby database or a logical standby database, namely, the following two types

-Physical standby database: physical standby database (using Redo Apply technology), mainly used for disaster recovery.

-Logical standby database: logical standby database (using SQL Apply technology), which can not only be used for disaster recovery, but also provide data query, analysis and other services

Data Guard data protection mode

Data Guard can be run in three different modes.

Maximum protection (maximum protection): ensures that data loss does not occur in the event of a failure of the primary database. Transaction commit is not allowed until all redo data is written to the local online redo log and the standby redo log of at least one standby database. If the redo log of the primary database cannot be written to the standby redo log of at least one standby database due to a failure, the primary database will be shut down.

Maximum availability (highest availability): provides the highest level of data protection without affecting the availability of the primary database. As in maximum protection mode, the transaction does not commit until the redo log required to recover the thing is written to the local online redo log and the standby redo log of at least one standby database. The difference in the maximum protection mode is that if the primary database fails, its redo log is not written to the redo log of the standby database. Instead, the primary database runs in maximum performance mode.

Maximum performance (maximum performance): default mode. Provides the highest level of data protection without affecting the performance of the primary database.

Environmental preparation

In my test environment, I prepared two CentOS7.4 virtual machines and installed Oracle11gR2 11.2.0.4.0 enterprise version of database software at the same time, in which only the primary server created a database instance and the standby server only installed Oracle database software.

Master database:

OS: CentOS7.4

Hostname: hmdb11dg-db1

Oracle Version: 11.2.0.4.0

Oracle SID: HMDG (a database created using the DBCA tool)

Standby database:

OS: CentOS7.4

Hostname: hmdb11dg-db2

Oracle Version: 11.2.0.4.0

Oracle SID: HMDG2

Note: before starting, there are no database instances on the standby service that will be backed up synchronously with the primary server

Settings for the primary database

1. Enable Archive Log

Check whether the master data is in archive log mode

SQL > SELECT log_mode FROM vested database and Logistic models ARCHIVELOG

If it is NOARCHIVELOG mode, change it to ARCHIVELOG mode

SQL > SHUTDOWN IMMEDIATE; SQL > STARTUP MOUNT; SQL > ALTER DATABASE ARCHIVELOG; SQL > ALTER DATABASE OPEN

two。 Enable mandatory logging

SQL > ALTER DATABASE FORCE LOGGING;SQL > SELECT name,force_logging FROM vested database * name FOR- HMDG YES

3. Create a password file

If the password file does not exist, create a password file, and the standby server uses the password file of the primary server. All user passwords for each database in the Data Guard configuration must be exactly the same.

$orapwd file=/tmp/orapwHMDG password=hm_201802 entries=20

4. Create an alternate redo log

The size of the alternate redo log file should exactly match the online redo log file size of the current primary database.

Determine the number of alternate redo log filegroups, recommended: (maximum logs per thread + 1) * maximum number of threads

SQL > SELECT * FROM v$log GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC- STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME- -1 1 40 104857600 512 1 YESINACTIVE 769491 06-FEB-18 813871 07-FEB-18 2 1 41 104857600 512 1 NOCURRENT 813871 07- FEB-18 2.8147E+14 3 1 39 104857600 512 1 YESINACTIVE 742672 06-FEB-18 769491 06-FEB-18# create 8 sets of spare redo logs SQL > ALTER DATABASE ADD STANDBY LOGFILE GROUP 4'/ u01 SQL > ALTER DATABASE ADD STANDBY LOGFILE GROUP 5'/ u01 Greg redo 05.log' SIZE 500m SIZE > ALTER DATABASE ADD STANDBY LOGFILE GROUP 6'/ u01 SIZE SIZE 500m SIZE SQL > ALTER DATABASE ADD STANDBY LOGFILE GROUP 9'/ u01 SIZE ALTER DATABASE ADD STANDBY LOGFILE GROUP > redo10.log' oradat 500m; ALTER DATABASE ADD STANDBY LOGFILE GROUP 10'/ u01; redo10.log' oradata 500m; ALTER DATABASE ADD STANDBY LOGFILE GROUP 11'/ u01; ALTER DATABASE ADD STANDBY LOGFILE GROUP 11'/ u01; ALTER DATABASE ADD STANDBY LOGFILE GROUP 11'/ u01 GROUP# THREAD# SEQUENCE# ARC STATUS--400 YES UNASSIGNED 500 YES UNASSIGNED 600 YES UNASSIGNED 700 YES UNASSIGNED 8 0 0 YES UNASSIGNED 9 0 0 YES UNASSIGNED 10 0 0 YES UNASSIGNED 11 0 0 YES UNASSIGNED8 rows selected.

5. Open flashback log

SQL > alter database flashback on;SQL > select flashback_on from vested database: FLASHBACKONMY

6. Oracle snooping configuration

Active and standby database must be configured to register static listening service (listener.ora configuration)

# main database $cat / u01/app/oracle/product/11.2.0/db_1/network/admin/listener.oraLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = hmdb11dg-db1) (PORT = 1521)) (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521) SID_LIST_LISTENER = (SID_DESC = (GLOBAL_DBNAME = HMDG. DB) (ORACLE_HOME = / u01/app/oracle/product/11.2.0/db_1) (SID_NAME = HMDG)) ADR_BASE_LISTENER = / u01/app/oracle# standby database $cat / u01/app/oracle/product/11.2.0/db_1/network/admin/listener.oraLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (PROTOCOL = TCP) (HOST = hmdb11dg-db2) ( PORT = 1521)) (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = HMDG2.DB) (ORACLE_HOME = / u01/app/oracle/product/11.2.0/db_1) (SID_NAME = HMDG2)) ADR_BASE_LISTENER = / u01/app/oracle

7. Configuration of TNS alias connection information for active and standby databases

Modify the $ORACLE_HOME/network/admin/tnsnames.ora configuration file on both servers to use the same configuration for the master and standby

HMDG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = hmdb11dg-db1) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HMDG.DB) HMDG2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = hmdb11dg-db2) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HMDG2.DB)

8. Test using TNS aliases to connect to the database

[oracle@hmdb11dg-db1] $sqlplus system/hm_123456@HMDGSQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 7 16:07:54 2018Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL >

9. Master database initialization parameter settings

Check the DB_NAME and DB_UNIQUE_NAME parameter settings. In my example, the DB_NAME and DB_UNIQUE_ name values of the primary database are both HMDG. DB_NAME is that all nodes use the same value. Even if the primary DB_ name value is used, DB_UNIQUE_NAME must be a globally unique value, that is, each node has a different value.

SQL > SHOW PARAMETER DB_NAMENAME TYPE VALUE---db_name string HMDGSQL > SHOW PARAMETER DB_UNIQUE_ NAMENAME TYPE VALUE---db_unique_name string HMDG# sets the DB_UNIQUE_NAME parameter Numerical value SQL > ALTER SYSTEM SET DB_UNIQUE_NAME=HMDG SCOPE=SPFILE

After determining the DB_UNIQUE_NAME of the library, let's first set the LOG_ARCHIVE_CONFIG parameter

SQL > ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG= (HMDG,HMDG2) 'SCOPE=SPFILE

Configure the location of the local archive log in the primary database and the location of the remote standby data redo log. Note that LOG_ARCHIVE_DEST_1 is set to the local parameters and LOG_ARCHIVE_DEST_2 is set to the remote node.

SQL > ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/HMDG VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=HMDG' SCOPE=SPFILE;SQL > ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=HMDG2 LGWR ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=HMDG2' SCOPE=SPFILE

Set the values of LOG_ARCHIVE_DEST_STATE_1 and LOG_ARCHIVE_DEST_STATE_2 to ENABLE

SQL > ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=SPFILE;SQL > ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=SPFILE

Set the LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters to the appropriate values, and REMOTE_LOGIN_PASSWORDFILE must be set to 'EXCLUSIVE'

SQL > ALTER SYSTEM SET LOG_ARCHIVE_FORMAT ='% tweets% slots% r.arc' SCOPE = SPFILE;SQL > ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES = 30 SCOPE = SPFILE;SQL > ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE SCOPE = SPFILE

Next, set the parameter values related to the standby database to ensure that the primary database is ready to switch to the standby database.

SQL > ALTER SYSTEM SET FAL_SERVER=HMDG2 SCOPE = SPFILE;SQL > ALTER SYSTEM SET FAL_CLIENT=HMDG SCOPE = SPFILE;SQL > ALTER SYSTEM SET DB_FILE_NAME_CONVERT='HMDG2','HMDG' SCOPE = SPFILE;SQL > ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/HMDG2/','/u01/app/oracle/oradata/HMDG/' SCOPE = SPFILE;SQL > ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE = SPFILE

After setting the initialization parameters of the primary database, you need to restart the database configuration to take effect.

SQL > shutdown immediateSQL > startupSQL > show parameter standby_file_managementNAME TYPE VALUE-- standby_file_management string AUTO

10. Generate a PFILE parameter file

After setting the initialization parameters of the primary database, we can manually generate a PFILE parameter file for the primary server

SQL > CREATE PFILE FROM SPFILE

View the generated initHMDG.ora file in the $ORACLE_HOME/dbs/ directory

$cat initHMDG.oraHMDG.__db_cache_size=234881024HMDG.__java_pool_size=33554432HMDG.__large_pool_size=1962934272HMDG.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentHMDG.__pga_aggregate_target=150994944HMDG.__sga_target=3137339392HMDG.__shared_io_pool_size=0HMDG.__shared_pool_size=872415232HMDG.__streams_pool_size=0*.audit_file_dest='/u01/app/oracle/admin/HMDG/adump'*.audit_trail='db'* .accounts = '11.2.0.4.0percent. Controlbacks filesystems. Controlbacks files.U01ApplicationoracleGreoradataUniHMDG Universe control01.ctl' '/ u01aphort oraclewhichfastened recoveryrecovery areaHMDGUniverse control02.ctlharmless. Dbstores blockquote sizecards 8192. Dbstores domaincake databases. Dbdatabases fileholders nameplates convertplease HMDG2' 'HMDG'*.db_name='HMDG'*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'*.db_recovery_file_dest_size=10737418240*.db_unique_name='HMDG'*.diagnostic_dest='/u01/app/oracle'*.dispatchers=' (PROTOCOL=TCP) (SERVICE=HMDGXDB)'* .fal _ client='HMDG'*.fal_server='HMDG2'*.log_archive_config='DG_CONFIG= (HMDG HMDG2)'* .log _ archive_dest_1='LOCATION=/u01/app/oracle/oradata/HMDG VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=HMDG'*.log_archive_dest_2='SERVICE=HMDG2 LGWR ASYNC VALID_FOR= (ONLINE_LOGFILES) PRIMARY_ROLE) DB_UNIQUE_NAME=HMDG2'*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_archive_format='%t_%s_%r.arc'*.log_archive_max_processes=30*.log_file_name_convert='/u01/app/oracle/oradata/HMDG2/' '/ u01 apprenticeship oracleUniverse oradataUniverse HMDG Universe accounts. Accountabilities targetbacks 3277848576 accounts.opencodes cursorsbooks 300 accounts.processespacks 5000s.remotecodes logincodes passwordfileplates exclusitions.sessionswords 5505 accounts.standbycharacters fileholders managementbags AUTOPLERS .undodisposable tablespacewings UNDOTBS1'

11. Back up the primary database

To create a backup by manually synchronizing the original data files of the primary database, if you are using RMAN's DUPLICATE to restore the standby database, you do not need to perform the second step.

[oracle@hmdb11dg-db1 ~] $rman target = / RMAN > BACKUP DATABASE PLUS ARCHIVELOG

twelve。 Create control files and PFILE files for the standby database

Create a standby database control file

SQL > ALTER DATABASE CREATE STANDBY CONTROLFILE AS'/ tmp/HMDG2.ctl'

Create a PFILE file for the standby database

SQL > CREATE PFILE='/tmp/initHMDG2.ora' FROM SPFILE

Alternate database server settings

1. Create the necessary directories on the standby database

$mkdir / u01 mkdir u01/app/oracle/fast_recovery_area/HMDG2 $mkdir / u01/app/oracle/admin/HMDG2 $mkdir / u01/app/oracle/admin/HMDG2/ {adump,dpdump,pfile,scripts} $mkdir-p / u01/app/oracle/oradata/HMDG2 $mkdir-p / u01/app/oracle/fast_recovery_area/HMDG2

two。 Copy control files, parameter files, and password files from the primary server to the standby server

# Control file $scp oracle@hmdb11dg-db1:/tmp/HMDG2.ctl / u01/app/oracle/oradata/HMDG2/control01.ctl $cp / u01/app/oracle/oradata/HMDG2/control01.ctl / u01/app/oracle/fast_recovery_area/HMDG2/control02.ctl# password file $scp oracle@hmdb11dg-db1:/tmp/orapwHMDG / u01/app/oracle/product/11.2.0/db_1/dbs/orapwHMDG2# parameter file $scp oracle@hmdb11dg-db1: / tmp/initHMDG2.ora / u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG2.ora

3. Modify standby database initialization parameters

Modify the PFILE file $ORACLE_HOME/dbs/initHMDG.ora of the standby server

. * .audit_file_dest='/u01/app/oracle/admin/HMDG2/adump'*.control_files='/u01/app/oracle/oradata/HMDG2/control01.ctl','/u01/app/oracle/fast_recovery_area/HMDG2/control02.ctl*.db_name='HMDG'*.db_unique_name='HMDG2'*.db_file_name_convert='HMDG','HMDG2'*.log_archive_config='DG_CONFIG= (HMDG HMDG2)'* .log _ archive_dest_1='LOCATION=/u01/app/oracle/oradata/HMDG2 VALID_FOR= (ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=HMDG2'*.log_archive_dest_2='SERVICE=HMDG LGWR ASYNC VALID_FOR= (ONLINE_LOGFILES) PRIMARY_ROLE) DB_UNIQUE_NAME=HMDG'*.fal_client='HMDG'*.fal_server='HMDG2*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_archive_format='%t_%s_%r.arc'*.log_archive_max_processes=30*.log_file_name_convert='/u01/app/oracle/oradata/HMDG/','/u01/app/oracle/oradata/HMDG2/'...

Create a standby database (DUPLICATE)

Start the standby database instance in NOMOUNT mode using the PFILE file of the standby database

$sqlplus / as sysdbaSQL > STARTUP NOMOUNT PFILE='/u01/app/oracle/product/11.2.0/db_1/dbs/initHMDG2.ora'

Create a SPFILE file

SQL > CREATE SPFILE FROM PFILE

Use RMAN to copy the primary database to the standby database, connect to the sys user, and use DUPLICATE to copy the primary database to the standby database

$rman TARGET sys/hm_123456@HMDG AUXILIARY sys/hm_123456@HMDG2

Copy the main library using the following DUPLICATE statement

DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE

If there are no errors during RMAN replication, you can immediately open the log redo application.

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; or SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

Cancel the application for reinstatement order

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Force the log switcher on the primary database to archive the current redo log group on the master

SQL > ALTER SYSTEM SWITCH LOGFILE

Query the log application on the server.

SQL > select sequence#, first_time, applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIM APPLIED- 10008-FEB-18 YES 10108-FEB-18 YES 10208-FEB-18 IN-MEMORY

Perform a log switch on the master

ALTER SYSTEM SWITCH LOGFILE

Then check the log application.

SQL > select sequence#, first_time, applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIM APPLIED- 10008-FEB-18 YES 10108-FEB-18 YES 10208-FEB-18 YES 10308-FEB-18 IN-MEMORY

Active / standby handover

1. Master database

Query the master and standby status in the current master database

SQL > SELECT SWITCHOVER_STATUS FROM Vacation DataseX SWITCHOVERVERVER STATUSMOTO STANDBY

When the status of the primary database is TO STANDBY, it means that you can switch to the standby database

Master execution

SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;SQL > SHUTDOWN IMMEDIATE;SQL > STARTUP MOUNT;-- starts the old primary database as standby database SQL > STARTUP NOMOUNT;SQL > ALTER DATABASE MOUNT STANDBY DATABASE;SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION when the slave is switched to the master.

two。 Standby database

Determine the current handover status on standby

SQL > SELECT SWITCHOVER_STATUS FROM database; SWITCHOVER_STATUS-TO_PRIMARY 1 row selected

Prepare for execution

SQL > ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;-- confirm to open the database SQL > ALTER DATABASE OPEN;-- or SQL > SHUTDOWN IMMEDIATE;SQL > STARTUP

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.