Oracle 11gR2 Active Data Guard adjustment case [1]

Oracle 11gR2 Active Data Guard adjustment case [1]

The customer's environment is Oracle RAC Database 11.2.0.3 for Linux x86_64bit + Oracle Database 11.2.0.3 for Linux x86_64bit (ADG slave Database). RAC uses ASM and the slave Database uses the file system. The customer added a data file to the SYSTEM tablespace in the main database of RAC. Due to script problems, the data file was placed on the local file SYSTEM of node 1, causing the database instance of node 2 to go down, this process is successfully synchronized to the slave database. The file system space corresponding to the file synchronized by the slave database is insufficient. You also need to adjust the location of the file, use the following steps to simulate the problem and solve the problem.

1. Simulate misoperation.

1. master database operations:
SQL> show parameter db_create
 
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_create_file_dest string + DBFILE1
Db_create_online_log_dest_1 string
Db_create_online_log_dest_2 string
Db_create_online_log_dest_3 string
Db_create_online_log_dest_4 string
Db_create_online_log_dest_5 string
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
+ DBFILE1/ractest/datafile/users.259.754173059
+ DBFILE1/ractest/datafile/undotbs1.258.754173059
+ DBFILE1/ractest/datafile/sysaux.257.754173057
+ DBFILE1/ractest/datafile/system.256.754173057
+ DBFILE1/ractest/datafile/undotbs2.264.754173315
+ DBFILE1/ractest/datafile/soe.274.686330585
+ DBFILE1/ractest/datafile/liubinglin.276.786500233
+ DBFILE1/ractest/datafile/liubinglin.268.805074155
 
8 rows selected.
 
All data files of the current RAC are stored in the ASM disk group.
 
1) Add a data file for the SYSTEM tablespace.
SQL> alter tablespace system add datafile 'system02. dbf' size 5 m;
 
Tablespace altered.
 
When you add a system02.dbf data file to the SYSETM tablespace, the name of the ASM disk group is not specified.

/Xxx ×××××/
Note:
Because the db_create_file_dest parameter is specified, datafile can be followed by any parameter to create a file at the location specified by the db_create_file_dest parameter. For example:
SQL> create tablespace t1 datafile size 5 m;
 
Tablespace created.
 
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
+ DBFILE1/ractest/datafile/users.259.754173059
+ DBFILE1/ractest/datafile/undotbs1.258.754173059
+ DBFILE1/ractest/datafile/sysaux.257.754173057
+ DBFILE1/ractest/datafile/system.256.754173057
+ DBFILE1/ractest/datafile/undotbs2.264.754173315
+ DBFILE1/ractest/datafile/soe.274.686330585
+ DBFILE1/ractest/datafile/liubinglin.276.786500233
+ DBFILE1/ractest/datafile/liubinglin.268.805074155
+ DBFILE1/ractest/datafile/t1.279.814356763
 
10 rows selected.
/Xxx ×××××/
 
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
+ DBFILE1/ractest/datafile/users.259.754173059
+ DBFILE1/ractest/datafile/undotbs1.258.754173059
+ DBFILE1/ractest/datafile/sysaux.257.754173057
+ DBFILE1/ractest/datafile/system.256.754173057
+ DBFILE1/ractest/datafile/undotbs2.264.754173315
+ DBFILE1/ractest/datafile/soe.274.686330585
+ DBFILE1/ractest/datafile/liubinglin.276.786500233
+ DBFILE1/ractest/datafile/liubinglin.268.805074155
/U01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf
 
9 rows selected.
Because the disk group name is not specified, the file is created in the $ ORACLE_HOME/dbs default directory of the local file system.
 
Slave database operations:
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
/U01/app/oracle/oradata/ractestusers.259.754173059
/U01/app/oracle/oradata/ractestundotbs1.258.754173059
/U01/app/oracle/oradata/ractestsysaux.257.754173057
/U01/app/oracle/oradata/ractestsystem.256.754173057
/U01/app/oracle/oradata/ractestundotbs2.264.754173315
/U01/app/oracle/oradata/ractestsoe.274.686330585
/U01/app/oracle/oradata/ractestliubinglin.276.786500233
/U01/app/oracle/oradata/ractestliubinglin.268.805074155
/U01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf
 
9 rows selected.
Operations on the master database are successfully synchronized to the slave database.

Ii. troubleshooting process.

After performing the preceding steps, the database has two problems. The instance cannot work properly because the data file RAC added to the SYSTEM tablespace in the master database cannot be accessed by other instances. On the slave database, because the/u01 file system has a small space, you must move system02.dbf to another directory. The following describes how to deal with these two problems.
 
Master database operations:
1) Stop all database instances of RAC.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
2) copy the system02.dbf data file to the ASM disk.
[Root @ rhel1 bin] # su-grid
[Grid @ rhel1 ~] $ Asmcmd-p
 
ASMCMD [+]> cp/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf + DBFILE1/ractest/datafile/
Copying/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf-> + DBFILE1/ractest/datafile/system02.dbf
ASMCMD [+]> cd + DBFILE1/ractest/datafile/
ASMCMD [+ DBFILE1/ractest/datafile]> ls
LIUBINGLIN.268.805074155
LIUBINGLIN.276.786500233
SOE.274.686330585
SYSAUX.257.754173057
SYSTEM.256.754173057
UNDOTBS1.258.754173059
UNDOTBS2.264.754173315
USERS.259.754173059
XIAOYANG.275.786499073
System02.dbf
 
3) Rename the location of system02.dbf.
ASMCMD [+ DBFILE1/ractest/datafile]> exit
[Grid @ rhel1 ~] $ Exit
Logout
[Root @ rhel1 bin] # su-oracle
[Oracle @ rhel1 ~] $ SQL
 
SQL * Plus: Release 11.2.0.2.0 Production on Thu May 2 09:18:12 2013
 
Copyright (c) 1982,201 0, Oracle. All rights reserved.
 
Connected to an idle instance.
 
SQL> startup mount
ORACLE instance started.
 
Total System Global Area 784998400 bytes
Fixed Size 2230600 bytes
Variable Size 486540984 bytes
Database Buffers 289406976 bytes
Redo Buffers 6819840 bytes
Database mounted.
SQL> select name from v $ datafile;
 
NAME
--------------------------------------------------------------------------------
+ DBFILE1/ractest/datafile/system.256.754173057
+ DBFILE1/ractest/datafile/sysaux.257.754173057
+ DBFILE1/ractest/datafile/undotbs1.258.754173059
+ DBFILE1/ractest/datafile/users.259.754173059
+ DBFILE1/ractest/datafile/undotbs2.264.754173315
+ DBFILE1/ractest/datafile/soe.274.686330585
+ DBFILE1/ractest/datafile/liubinglin.276.786500233
+ DBFILE1/ractest/datafile/liubinglin.268.805074155
/U01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf
 
9 rows selected.
 
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf' to '+ DBFILE1/ractest/datafile/system02.dbf ';
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------
+ DBFILE1/ractest/datafile/users.259.754173059
+ DBFILE1/ractest/datafile/undotbs1.258.754173059
+ DBFILE1/ractest/datafile/sysaux.257.754173057
+ DBFILE1/ractest/datafile/system.256.754173057
+ DBFILE1/ractest/datafile/undotbs2.264.754173315
+ DBFILE1/ractest/datafile/soe.274.686330585
+ DBFILE1/ractest/datafile/liubinglin.276.786500233
+ DBFILE1/ractest/datafile/liubinglin.268.805074155
+ DBFILE1/ractest/datafile/system02.dbf
 
9 rows selected.
 
After that, start other RAC instances.
 
Standby database operation:
1). Stop the slave database instance.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
2). Move the data file location.
[Oracle @ RedHat5 ~] $ Mv/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf/u01/app/oracle/oradata/system02.dbf
 
3) Rename the location of system02.dbf.
[Oracle @ redhat5 ~] $ Sqlplus/as sysdba
 
SQL * Plus: Release 11.2.0.2.0 Production on Thu May 2 01:28:22 2013
 
Copyright (c) 1982,201 0, Oracle. All rights reserved.
 
Connected to an idle instance.
 
SQL> startup mount
ORACLE instance started.
 
Total System Global Area 313159680 bytes
Fixed Size 2226072 bytes
Variable Size 251660392 bytes
Database Buffers 54525952 bytes
Redo Buffers 4747264 bytes
Database mounted.
SQL> select name from v $ datafile;
 
NAME
--------------------------------------------------------------------------------
/U01/app/oracle/oradata/ractestsystem.256.754173057
/U01/app/oracle/oradata/ractestsysaux.257.754173057
/U01/app/oracle/oradata/ractestundotbs1.258.754173059
/U01/app/oracle/oradata/ractestusers.259.754173059
/U01/app/oracle/oradata/ractestundotbs2.264.754173315
/U01/app/oracle/oradata/ractestsoe.274.686330585
/U01/app/oracle/oradata/ractestliubinglin.276.786500233
/U01/app/oracle/oradata/ractestliubinglin.268.805074155
/U01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf
 
9 rows selected.
 
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf' to '/u01/app/oracle/oradata/system02.dbf ';
Alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf' to '/u01/app/oracle/oradata/system02.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is
Automatic.
The above prompt makes it clear that this operation cannot be performed when standby_file_management is equal to AUTO.
 
SQL> show parameter standby
 
NAME TYPE VALUE
-----------------------------------------------------------------------------
Standby_archive_dest string? /Dbs/arch
Standby_file_management string AUTO
 
Modify standby_file_management = manual:
SQL> alter system set standby_file_management = manual;
 
System altered.
 
SQL> alter database rename file '/u01/app/oracle/product/11.2.0/db_1/dbs/system02.dbf' to '/u01/app/oracle/oradata/system02.dbf ';
 
Database altered.
 
Renamed successfully.
 
SQL> alter database open;
 
Database altered.
 
SQL> alter database recover managed standby database using current logfile disconnect;
 
Database altered.
 
Restore the value of standby_file_management:
SQL> alter system set standby_file_management = auto;
 
System altered.
 
Master database operations:
Perform the following steps to verify that the log transmission service returns to normal.
SQL> alter system switch logfile;
 
System altered.
 
Switch the logfile of all RAC instances.
 
SQL> select dest_id, thread #, max (sequence #) from v $ archived_log where resetlogs_change #= 5069294 group by dest_id, thread #
 
DEST_ID THREAD # MAX (SEQUENCE #)
----------------------------------
2 1 17
1 2 11
1 1 19
2 2 9
 
Wait a few seconds,
SQL> select dest_id, thread #, max (sequence #) from v $ archived_log where resetlogs_change #= 5069294 group by dest_id, thread #;
 
DEST_ID THREAD # MAX (SEQUENCE #)
----------------------------------
2 1 20
1 2 12
1 1 20
2 2 12
 
Log service returns to normal.

-- End --

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