Routine maintenance and troubleshooting of Oracle 11G R2 slave uard

Routine maintenance and troubleshooting of Oracle 11G R2 slave uard

1. About the Forced Logging Mode
Some DDL statements can avoid writing redo logs by specifying the NOLOGGING clause (the purpose is to increase the speed, which is indeed valid 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)
)
)

The above are modified according to your actual situation.
After the above configuration, 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, modify them accordingly.

1. Description of initialization parameters related to the master database role:

DB_NAME
Make sure that DB_NAME is the same for all databases in the same Data Guard environment.

DB_UNIQUE_NAME
Specify a unique name for each database to indicate different databases in the same dataguard 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, which are 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
Specify the parameter value as ENABLE to indicate 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 primary and standby database roles are converted to each other, you do not need to modify the role uard to run properly.

FAL_SERVER
Specify the Connection Service name from the slave database to the master database. FAL_SERVER = the server where the orcl2 log is located.

FAL_CLIENT
Specify the Connection Service name from the master database to the slave database. FAL_CLIENT = orcl log receiving client.

STANDBY_FILE_MANAGEMENT

If the data file of the master database is modified (such as new or renamed), 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.

Example: STANDBY_FILE_MANAGEMENT = AUTO

Modify the initialization parameters of the master database.

The db_name parameter has been set and does not need to be modified.

SQL> alter system set db_unique_name = 'db1' scope = spfile;
SQL> alter system set log_archive_config = 'dg _ config = (db1, db2) 'scope = spfile;
--- Here db1 and db2 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 archive directory. You must 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;
----- The service here is the service name of the master database connected to the slave database, which will be configured later in the tnsnames. ora File

The valid_for parameter indicates that 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 preceding modification is required as a parameter for the master database role. To facilitate later Master/Slave database switchover, we recommend that you configure parameters for the master database role as well.
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 parameters 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
------------------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 VALID
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE

10 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
--------------------------------------------------------------------
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
--------------------------------------------------------------------
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/201711_904130046.dbf 11 YES
/Opt/oracle/flash_recovery_area/201712_904130046.dbf 12 YES
/Opt/oracle/flash_recovery_area/201713_904130046.dbf 13 YES
/Opt/oracle/flash_recovery_area/41514_904130046.dbf 14 YES
/Opt/oracle/flash_recovery_area/201715_904130046.dbf 15 YES
/Opt/oracle/flash_recovery_area/41516_904130046.dbf 16 YES
/Opt/oracle/flash_recovery_area/41517_904130046.dbf 17 YES
/Opt/oracle/flash_recovery_area/41518_904130046.dbf 18 YES
/Opt/oracle/flash_recovery_area/201719_904130046.dbf 19 YES
/Opt/oracle/flash_recovery_area/201720_904130046.dbf 20 YES
/Opt/oracle/flash_recovery_area/201721_904130046.dbf 21 YES
/Opt/oracle/flash_recovery_area/41522_904130046.dbf 22 YES
/Opt/oracle/flash_recovery_area/201723_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 from the archive directory of the master database
Register the alter database register physical logfile '/opt/oracle/flash_recovery_area/archive filename' on the slave database ';

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 in the specified directory at oracle10G 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 CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
LNS WRITING

Slave database:

SQL> select process, status from v $ managed_standby;
PROCESS STATUS
---------------------
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
ARCH CONNECTED
RFS IDLE
RFS IDLE
MRP0 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 the table to standby is used, 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;
The to_primary table can be switched normally.
Run: alter database commit to switchover to primary;
Otherwise execute: 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 gaps exists on the physical standby database.
SQL> SELECT THREAD #, LOW_SEQUENCE #, HIGH_SEQUENCE # FROM V $ ARCHIVE_GAP;

(3) Eliminate archive redo log gaps
Upload the archive redo log that has not been uploaded to the physical backup database from the primary database or other backup locations 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 the failover operation on the physical backup database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

(5) converting a physical standby database into a master role
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

(6) restart the new primary database
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

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

7. archiving log processing
A. Archive logs applied in the physical standby database must be deleted regularly.
Rman> delete archivelog all completed before 'sysdate-7 ';
Delete the archived Log File seven days ago. A full backup is recommended after deletion.

B. manually delete the archived 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 logs to the slave 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
--------------------------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 ERROR ORA-03113: end-of-file on communication channel

Solution: run the command in the master database
SQL> alter system set log_archive_dest_state_2 = enable;
This command manually triggers the master reservoir 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
---------------------------------------------------------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_ARCHIVE_DEST_2 ERROR 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 run
SQL> alter system set log_archive_dest_state_2 = enable;

ORA-16191: Primary log shipping client not logged on standby
SQL> select dest_name, status, error from v $ archive_dest;
DEST_NAME STATUS ERROR
-----------------------------------------
LOG_ARCHIVE_DEST_1 VALID
LOG_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 run
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.
Logs sent from the master database cannot be applied.
Check in the slave database,
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 start to check whether there is mrp

[Oracle @ HJITBACKUP bdump] $ ps-ef | grep mrp
Oracle 31896 1 0? 00:00:00 ora_mrp0_flow
Oracle 32001 31820 0 00:00:00 pts/1 grep mrp

Check the gap and find that the process exists on the slave database,

SQL> select * from v $ archive_gap;
No rows selected
No query view found,

Check V $ MANAGED_STANDBY
SQL> select process, status from v $ managed_standby;
PROCESS STATUS
-------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_GAP
RFS IDLE
RFS IDLE

We found that MRP0 is waiting for the GAP. For more information, see this view.

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
Arch connected n/A 0 0 0 0
MRP0 WAIT_FOR_GAP N/A 1 928 0 0
Rfs idle n/A 0 0 0 0
Rfs idle n/A 0 0 0 0
No application found in log 928,

It turns out that the master database is deleted by 928, And the slave database cannot be applied, so it can only be recovered from the backup. restore archivelog
Now the problem has been solved.

Query slave database status
SQL> SELECT PROCESS, STATUS, THREAD #, SEQUENCE #, BLOCK #, BLOCKS FROM V $ MANAGED_STANDBY;

PROCESS STATUS
-------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
RFS IDLE
RFS IDLE
So after standby is installed, the status here should be
MRP0 WAIT_FOR_LOG is normal

9. Notes
We recommend that you use lower-case letters for the names involved in the master and slave databases 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;

You should view the alert file of the standby database in real time to clearly understand the status of Master/Slave updates. This is also an important method for troubleshooting.

Related View
V $ ARCHIVE_DEST
V $ ARCHIVE_DEST_STATUS
V $ ARCHIVE_GAP
V $ ARCHIVED_LOG
V $ DATABASE
V $ DATAFILE
V $ DATAGUARD_STATUS
V $ LOG
V $ LOGFILE
V $ LOG_HISTORY
V $ STANDBY_LOG

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Important configuration parameters of Oracle Data Guard

Configure Oracle 11g Data Guard based on the same host

Explore Oracle 11g elastic uard

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

Role conversion for Oracle Data Guard

FAL gap in Oracle Data Guard logs

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby