Routine maintenance and troubleshooting of Oracle 11G R2 slave uard

Routine maintenance and troubleshooting of Oracle 11G R2 slave uard

1. for the Forced Logging mode, there are some DDL statements that can avoid writing redo logs by specifying the NOLOGGING clause (the purpose is to increase the speed, which is indeed effective in some cases). After specifying the database as the force logging mode, the database records all operations except the temporary tablespace or temporary rollback segment and ignores specified parameters such as NOLOGGING. If a statement such as nologging is executed while executing force logging, force logging waits until all such statements are executed. Force logging is saved as a fixed parameter in the control file, so it is not affected by restart and other operations (only once)

Enable force logging

SQL> alter database force logging;

Disable force logging

SQL> alter database no force logging;

View the force logging status:

SQL> select FORCE_LOGGING from v $ database;

2. Master-slave Database Password

The password file is located at $ ORACLE_HOME/dbs/orapwSID. The passwords of the master and slave databases must be consistent. Otherwise, logs may fail to be transmitted. It is better to use scp to transmit the password more conveniently.

3. About listener. ora and tnsnames. ora

Listener. ora is the Database listener Configuration file, and tnsnames. ora is the network service name configuration file.

To modify listener. ora, You need to restart the listener, while tnsnames. ora does not need to be restarted. We can use the default listener. ora

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost. localdomain) (PORT = 1521) ADR_BASE_LISTENER =/opt/oracle

The above is dynamic registration. If it is static registration, it is

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME =/opt/oracle/product/11.2.0/db_1) (PROGRAM = extproc )) (SID_DESC = (GLOBAL_DBNAME = db1) (ORACLE_HOME =/opt/oracle/product/11.2.0/db_1) (SID_NAME = db1 )))

Tnsnames. ora requires only the service name

Db1 = (DEST_NAME (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = db1) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1) db2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = db2) (PORT = 1521 ))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db2 )))

After the above configurations are modified according to your actual situation, you can test each other's tnsping db1 or tnsping db2.

4. parameter file description

Parameter file Description: add the following parameters. If the initialization parameters are already configured, You need to modify them accordingly. 1. Description of initialization parameters related to the master database role: DB_NAME: ensure that DB_NAME is the same for all databases in the same Data Guard environment. DB_UNIQUE_NAME specifies a unique name for each database, to indicate different databases in the same consumer uard environment. LOG_ARCHIVE_CONFIG this parameter uses the DG_CONFIG attribute to list all DB_UNIQUE_NAME (including master database db and slave database db) in the same Data Guard, separated by commas. For example, LOG_ARCHIVE_CONFIG = 'db _ CONFIG = (db1, db22) 'Log _ ARCHIVE_DEST_n: path for generating the archive file. This parameter is very important, and the logging uard is set here to transfer logs. LOG_ARCHIVE_DEST_STATE_n specifies that the parameter value is ENABLE, indicating whether the corresponding LOG_ARCHIVE_DEST_n parameter is valid. REMOTE_LOGIN_PASSWORDFILE we recommend that you set the parameter value to EXCLUSIVE or SHARED. Ensure that all db servers in the same Data Guard configuration have the same sys password. Different log transmission will fail. The database is EXCLUSIVE by default, and generally does not need to be modified. LOG_ARCHIVE_FORMAT specifies the format of the archive file. Generally, you do not need to modify it. Keep the default value. 2. The following parameters are related to the standby database role. We recommend that you set them in the initialization parameters of the primary database, in this way, after the master and slave database roles are converted to each other, you do not need to modify the slave uard to run properly. FAL_SERVER specifies the Connection Service name from the slave database to the master database. FAL_SERVER = the server on which the orcl2 log is located. FAL_CLIENT specifies the Connection Service name from the master database to the slave database, and FAL_CLIENT = orcl log receiving client. STANDBY_FILE_MANAGEMENT if the data file of the master database is modified (such as new database creation or renaming), modify the data file in the slave database according to the settings of this parameter. AUTO indicates automatic management. If it is set to MANUAL, MANUAL management is required. For example, under STANDBY_FILE_MANAGEMENT = AUTO, the initialization parameters of the master database are modified. The db_name parameter has been set. You do not need to modify SQL> alter system set db_unique_name = 'db1' scope = spfile; SQL> alter system set log_archive_config = 'dg _ config = (db1, db2) 'scope = spfile; --- db1 and db2 here are db_unique_name

SQL> alter system set log_archive_dest_1 = 'location =/opt/oracle/flash_recovery_area 'scope = spfile; --/opt/oracle/flash_recovery_area is the local archiving directory. You need to manually create this directory, you can also specify other paths. Note that the oracle account has the permission to read and write the directory. SQL> alter system set log_archive_dest_state_1 = enable scope = spfile; -- this usually does not need to be modified. The default value is enable. SQL> alter system set log_archive_dest_2 = 'service = db2 valid_for = (online_logfiles, primary_role) arch async noaffrem db_unique_name = db2 'scope = spfile; ----- here the service is the service name of the master database connected to the slave database, which will be later in tnsnames. the valid_for parameter configured in the ora file indicates that the online_logfile must be transmitted to the slave database only when the archive log destination is the master database of the database. Arch async noaffsert describes the synchronous mode. There are three synchronous Methods: maximum protection, maximum performance, and maximum availability. SQL> alter system set log_archive_dest_state_2 = enable scope = spfile; The above modified parameters are required as the master database role. To facilitate later Master/Slave database switchover, we recommend that you configure the parameters used as the standby database role in the master database. SQL> alter system set fal_server = db2 scope = spfile; SQL> alter system set fal_client = db scope = spfile; SQL> alter system set standby_file_management = auto scope = spfile; generate a static parameter file for later use by the slave database. SQL> create pfile from spfile; restart the master database to make the parameter take effect.

6. Restart uard Start and Stop and maintain:

DataGuard stop: master-slave

DataGuard start: slave and master

7. Routine uard monitoring view

A. Check whether the log archiving path is available in the master database. If the remote archiving directory is unavailable, the error message is displayed.

SQL> select dest_name, status, error from v $ archive_dest; DEST_NAME STATUS ERROR ---------------------- -------------------------------- too many rows have been created before INACTIVE10 rows selected. the above record indicates that the archive log directory of the slave database is valid and normal.

B. query the master-slave role of the database and the running mode of the current slave uard. The query results for the master and slave databases are different.

Master database:

SQL> select database_role, LOG_MODE, PROTECTION_MODE, PROTECTION_LEVEL from v $ database; DATABASE_ROLE LOG_MODE PROTECTION_MODE PROTECTION_LEVEL -------------------- begin executing PRIMARY ARCHIVELOG MAXIMUM PERFORMANCE

Slave database:

SQL> select database_role, LOG_MODE, PROTECTION_MODE, PROTECTION_LEVEL from v $ database; DATABASE_ROLE LOG_MODE PROTECTION_MODE PROTECTION_LEVEL =------------ using PHYSICAL STANDBY ARCHIVELOG MAXIMUM PERFORMANCE

C. The results of the master and slave databases are different for applications that obtain archived logs. There will be 2 records for each archive file on the master database

SQL> select name, SEQUENCE #, APPLIED from v $ archived_log order by sequence #;

Slave database:

/Opt/oracle/flash_recovery_area/limit 11 YES/opt/oracle/flash_recovery_area/201712_904130046.dbf 12 YES/opt/oracle/flash_recovery_area/limit 13 YES/opt/oracle/flash_recovery_area/limit 14 YES /opt/oracle/flash_recovery_area/limit 15 YES/opt/oracle/flash_recovery_area/41516_904130046.dbf 16 YES/opt/oracle/flash_recovery_area/limit 17 YES/opt/oracle/flash_recovery_area/limit 18 YES /opt/oracle/flash_recovery_area/limit 19 YES/opt/oracle/flash_recovery_area/201720_904130046.dbf 20 YES/opt/oracle/flash_recovery_area/limit 21 YES/opt/oracle/flash_recovery_area/limit 22 YES /opt/oracle/flash_recovery_area/41523_904130046.dbf 23 YES/opt/oracle/flash_recovery_area/41524_904130046.dbf 24 IN-MEMORY

If any log is found to be discontinuous, You need to compare the archive log sequence of the master database to determine whether there is any lost log. If yes, you need to manually register the log and apply the archive log.

(Method: copy the corresponding archive file from the archive directory of the master database to the backup database and register alter database register physical logfile '/opt/oracle/flash_recovery_area/archive filename ';

Then, manually apply the log alter database recover automatic standby database. During the test, it is found that the lost archive log file is automatically registered when it is admitted to the specified directory at 10 Gb without manual registration .)

 

D. query the process information of the Master/Slave Database

SQL> select process, status from v $ managed_standby; -- Query process Information on the Master/Slave Database

Master database:

SQL> select process, status from v $ managed_standby; PROCESS STATUS --------- ------------ ARCH CLOSINGARCH CLOSINGLNS WRITING

Slave database:

SQL> select process, status from v $ managed_standby; PROCESS STATUS --------- ---------- ARCH CONNECTEDARCH CLOSINGARCH CONNECTEDRFS IDLERFS IDLEMRP0 APPLYING_LOG

Note the above two red parts

 

F. view the dataguard status information.

SQL> select message_num, message from v $ export uard_status;

G. Check whether logs in the slave database are missing.

SQL> select * from v $ archive_gap;

6. Master/Slave database switchover

Switchover (scheduled switchover without data loss)

Failover (when the master database fails, you need to switch the role from the master database to the slave database)

A. switchover Switching

Master database: select switchover_status from v $ database; if it is a standby table, it can be switched normally. directly execute alter database commit to switchover to physical standby; otherwise execute: alter database commit to switchover to physical standby with session shutdown; shutdown immediate; startup nomount; alter database mount standby database; alter database recover managed standby database disconnect from session;

Slave database: select switchover_status from v $ database; if it is a to_primary table, it can be switched normally. run: alter database commit to switchover to primary; otherwise, run: alter database commit to switchover to primary with session shutdown; shutdown immediate; startup;

 

B. failover of failover

(1) Determine whether the master database is unable to start due to serious hardware faults or other causes. (2) Check whether archive redo log gapsSQL> select thread #, LOW_SEQUENCE #, HIGH_SEQUENCE # from v $ ARCHIVE_GAP; (3) eliminate archive redo log gaps from the primary database or other backup locations to transmit the archive redo log that is not passed to the physical backup database, and register it in the controlfile of the physical backup database. SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'archive redo log file name'; repeat steps 2 and 3 until no record exists in the V $ ARCHIVE_GAP view. (4) Initiate a failover SQL operation on the physical STANDBY DATABASE> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE; (5) convert the physical standby database to the master role SQL> alter database commit to switchover to primary; (6) restart the new master database SQL> shutdown immediate; SQL> STARTUP;

(7) perform full backup for the new primary database.

7. processing of archived logs. archive logs applied in the physical standby database must be deleted regularly. rman> delete archivelog all completed before 'sysdate-7'; DELETE the archived log files seven days ago. A full backup is recommended after deletion. B. manually delete the archive log file, and then execute the following two commands in RMAN to update the control file crosscheck archivelog all; delete expired archivelog all; c. cancel the transfer of the log to the standby database. alter system set log_archive_dest_state_2 = 'defer ';

 

8. Common Faults:

A. ORA-03113 error occurred archiving on the master database after the slave database restarts

SQL> select dest_name, status, error from v $ archive_dest; DEST_NAME STATUS ERROR cause -------- LOG_ARCHIVE_DEST_1 VALIDLOG_ARCHIVE_DEST_2 ERROR ORA-03113: end-of-file on communication channel solution: execute SQL> alter system set log_archive_dest_state_2 = enable in the master database. This command manually triggers the master database to try to connect to the slave database. In this case, you only need to ensure that the Network and Configuration between the master and slave databases are correct. Dataguard automatically restores this error. This cycle is 300 seconds by default. You can also add the reopen parameter to the log_archive_dest_2 parameter to specify the cycle for continued attempts after the master-slave database fails.

B. ORA-01031: insufficient privileges error SQL> select dest_name, status, ERROR from v $ archive_dest; DEST_NAME STATUS ERROR ------------------ too many errors ORA-01031: insufficient Privileges solution: unify the Database Password File of the master and slave databases, or recreate the password file. Set the sys password to the same. Then execute SQL> alter system set log_archive_dest_state_2 = enable;

C. ORA-16191: Primary log shipping client not logged on standbySQL> select dest_name, status, error from v $ archive_dest; DEST_NAME status error failed ----------- LOG_ARCHIVE_DEST_1 VALIDLOG_ARCHIVE_DEST_2 ERROR ORA-16191: primary log shipping client not logged on standby solution: unify the Database Password File of the master and slave databases, or recreate the password file. Set the sys password to the same. Then execute SQL> alter system set log_archive_dest_state_2 = enable;

D. the standby database has been unable to apply logs. The MRP0 process displays the WAIT_FOR_GAP problem. The logs sent from the primary database cannot be used in the standby database for check. SQL> select sequence #, applied from v $ archived_log; SEQUENCE # APP -----930 NO 931 NO 932 NO 933 NO 934 NO 935 NO 936 NO 937 NO 938 NO 939 NO 940 NO then check whether mrp [oracle @ HJITBACKUP bdump] $ ps -ef | grep mrporacle 31896 1 0? 00:00:00 ora_mrp0_floworacle 32001 31820 0 00:00:00 pts/1 grep mrp seems to exist. Then, check the gap and find that this process exists on the slave database. SQL> select * from v $ archive_gap; no rows selected query view found, then check V $ MANAGED_STANDBYSQL> select process, status from v $ managed_standby; process status --- ---- arch connectedarch CONNECTEDMRP0 WAIT_FOR_GAPRFS idlerfs idle found MRP0 waiting for GAP, for more information, see SQL> select process, status, group #, thread #, sequence #, block #, blocks from v. $ Managed_standby; process status group # THREAD # SEQUENCE # BLOCK # BLOCKS --- ---- arch connected n/A 0 0 0 0 0 arch connected n/A 0 0 0 0MRP0 WAIT_FOR_GAP N/ 1 928 0 0RFS idle n/A 0 0 0 0RFS idle n/A 0 0 0 0 found that log 928 is not applied, it turns out that the master database is deleted by 928, And the slave database cannot be applied. Therefore, it can only be recovered from the backup. The restore archivelog has now completed troubleshooting. SQL> SELECT PROCESS, STATUS, THREAD #, SEQUENCE #, BLOCK #, BLOCKS FROM V $ MANAGED_STANDBY;

Process status --- ---- arch connectedarch CONNECTEDMRP0 WAIT_FOR_LOGRFS IDLERFS IDLE

9. Note: We recommend that you use lower-case letters for the names involved in the Master/Slave database to avoid inexplicable errors during the configuration process. If you execute alter database clear unarchived logfile or alter database open resetlogs in the master database, the mongouard must be rebuilt. Before working in the continuous recovery mode, you must ensure that all the previously archived logs have been applied to the slave database. In the continuous recovery mode, oracle will not apply the archived logs, but will only apply the archived logs that will come later. New tables, tablespaces, and datafiles can be applied to the standby database through Logs. However, new temporary tablespaces and rename datafile cannot be applied to the standby database. When the archive log gap occurs, you need to find the corresponding archive log and copy the archive log to the log_archive_dest directory of the slave node. Then, alter database recover standic standby database; the alert file of the standby DATABASE should be viewed in real time to clearly understand the updates of the master and slave databases. This is also an important method for troubleshooting. Related view V $ ARCHIVE_DESTV $ response $ ARCHIVE_GAPV $ ARCHIVED_LOGV $ DATABASEV $ DATAFILEV $ paiuard_statusv $ LOGV $ LOGFILEV $ LOG_HISTORYV $ STANDBY_LOG