Oracle 11gR2 Database and Active Data Guard migration case, 11gr2guard

Oracle 11gR2 Database and Active Data Guard migration case, 11gr2guard

The customer's core system is composed of an Oracle Database 11.2.0.3.4 single machine and an Active Data Guard, which run on two PC servers respectively. The Oracle Linux 5.8 x86_64bit Operating System, the two servers are not connected to storage devices. Due to old equipment and other reasons, we need to migrate this Oracle Database System (master database and ADG database) to the two newly purchased servers, it is neither cross-platform nor cross-platform. To minimize the downtime, we first use the latest RMAN level 0 backup to restore the database on two new servers, then, we will restore and recover all the level 1 backup and archive logs so far to the two databases, after the primary database is shut down normally, the remaining archive and online Redo log files are applied to two new databases, so that their data is up-to-date and consistent. Finally, open the primary database, when the synchronization of the ADG is resumed, the whole process starts to shut down from a.m. and continues until a.m. before the migration is successful. Many small problems have been encountered between them and record them again:

1. RMAN reports an error.

RMAN receives the following error after applying partial archiving logs:
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-03002: failure of recover command at 11/13/2014 00:03:03
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/oradata/bak/archivelog/2014_11_12/o1_mf_000062193_b65oryl5 _. arc'
ORA-00283: recovery session canceled due to errors
ORA-19755: cocould not open change tracking file
ORA-19750: change tracking file: '/u01/app/oracle/block_change_file'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Run the following SQL statement to disable block change tracking. The database can continue to use archivelog normally:

SQL> alter database disable block change tracking;

Database altered.

Reference: http://gavinsoorma.com/2009/07/rman-recovery-interrupted-due-to-block-change-tracking-file/


2. Check log synchronization between the master database and the slave database after resetlogs.

You can run the following SQL statement on the master database to check log synchronization between the master database and the slave database:
SQL> select dest_id, thread #, max (sequence #) from v $ archived_log where resetlogs_change #= 936497858 group by dest_id, thread #;

DEST_ID THREAD # MAX (SEQUENCE #)
----------------------------------
2 1 9
1 1 9

Because the primary database executes alter database open resetlogs when it is opened, query v $ archived_log to keep up with resetlogs_change # to ensure that the current database archive is viewed, resetlogs_change # You can use v $ database. resetlogs_change #. In addition, because resetlogs opens the database, sequence # Starts counting again.


3. process the active Standby LogFile.

After the master database is stopped, we want to copy all the Online Redo logfiles and Standby logfiles to the new server and use alter databae rename file... to... the way to rename, did not expect is Active Standby Logfile can not be renamed (received error: ORA-01511: error in renaming log/data files ), however, you must place the Standby Logfile file in the specified directory. The following describes the status of the v $ logfile:

SQL> select group #, member from v $ logfile;

GROUP #
----------
MEMBER
--------------------------------------------------------------------------------
3
/Oradata/orcl/REDO03.LOG

2
/Oradata/orcl/REDO02.LOG

1
/Oradata/orcl/REDO01.LOG


GROUP #
----------
MEMBER
--------------------------------------------------------------------------------
4
/U01/app/oracle/oradata/orcl/sredo01.log

5
/Oradata/orcl/sredo02.log

6
/Oradata/orcl/sredo03.log


GROUP #
----------
MEMBER
--------------------------------------------------------------------------------
7
/Oradata/orcl/sredo04.log


7 rows selected.

Group #4 is the Active Standby Logfile before the primary database. You cannot perform the alter database rename file operation on it.

SQL> alter database drop logfile group 4;
Alter database drop logfile group 4
*
ERROR at line 1:
ORA-00315: log 4 of thread 1, wrong thread #0 in header
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/orcl/sredo01.log'

An error occurred while trying to DROP the GROUP.

SQL> alter database add logfile member '/oradata/orcl/sredo01.log' to group 4;
Alter database add logfile member '/oradata/orcl/sredo01.log' to group 4
*
ERROR at line 1:
ORA-16161: Cannot mix standby and online redo log file members for group 4

Failed to add the member.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;
Alter database clear logfile group 4
*
ERROR at line 1:
ORA-00350: log 4 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/orcl/sredo01.log'

CLEAR fails because it is not archived.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;

Database altered.

Clear unarchived successful.
The solution for Standby Logfile is the same as that for Online Redo Logfile.

SQL> select group #, thread #, status from v $ standby_log;

GROUP # THREAD # STATUS
------------------------------
4 1 UNASSIGNED
5 1 UNASSIGNED
6 1 UNASSIGNED
7 1 UNASSIGNED

SQL> select member from v $ logfile;

MEMBER
--------------------------------------------------------------------------------
/Oradata/orcl/REDO03.LOG
/Oradata/orcl/REDO02.LOG
/Oradata/orcl/REDO01.LOG
/U01/app/oracle/oradata/orcl/sredo01.log
/Oradata/orcl/sredo02.log
/Oradata/orcl/sredo03.log
/Oradata/orcl/sredo04.log

7 rows selected.

SQL> alter database drop logfile group 4;

Database altered.

The log group is successfully dropped.

SQL> select member from v $ logfile;

MEMBER
--------------------------------------------------------------------------------
/Oradata/orcl/REDO03.LOG
/Oradata/orcl/REDO02.LOG
/Oradata/orcl/REDO01.LOG
/Oradata/orcl/sredo02.log
/Oradata/orcl/sredo03.log
/Oradata/orcl/sredo04.log

6 rows selected.

SQL> select group #, thread #, bytes/1024/1024 mb from v $ standby_log;

GROUP # THREAD # MB
------------------------------
5 1 50
6 1 50
7 1 50


SQL> ALTER DATABASE ADD STANDBY LOGFILE group 4 ('/oradata/orcl/sredo01.log') SIZE 50 M;

Database altered.

Add a new location for GROUP 4.

SQL> select member from v $ logfile;

MEMBER
--------------------------------------------------------------------------------
/Oradata/orcl/REDO03.LOG
/Oradata/orcl/REDO02.LOG
/Oradata/orcl/REDO01.LOG
/Oradata/orcl/sredo01.log
/Oradata/orcl/sredo02.log
/Oradata/orcl/sredo03.log
/Oradata/orcl/sredo04.log

7 rows selected.

Complete the Standby Logfile migration.

Note:The preceding operations may not be completed on the slave database. The solution is that after the Standby Logfile migration is completed on the master database, the master database creates a new for Standby Controlfile (alter database create standby controlfile as '/tmp/controlf. ctl ';), pass the new Standby Controlfile and Standby Logfile to the same location, restore the Standby database to the consistent state, open the Standby database, and start to apply logs. Oracle Active Data Guard tuning case: http://blog.itpub.net/23135684/viewspace-1262326/

The Data Guard standby database must be in the consistent State to open read only. Otherwise, the application log of the alter database recover managed standby database will be restored to the consistent state, and then open read only.

4. The master database and slave database cannot be synchronized in real time.

Real-time synchronization in the highest-performance mode when the original primary database and backup database are configured. After the migration is completed, real-time synchronization cannot be performed, but synchronization is normal after the archive switch.
First, the LGWR affrem SYNC parameter is configured for LOG_ARCHIVE_DEST_2 in the primary database, and the standby database executes the following application commands:
Alter database recover managed standby database using current logfile disconnect from session;
The log Transmission Service of the master and slave databases works normally.

Execute the following SQL statement in the primary and standby databases:
SQL> select group #, thread #, status, bytes/1024/1024 mb from v $ standby_log;

GROUP # THREAD # STATUS MB
----------------------------------------
4 0 UNASSIGNED 100
5 0 UNASSIGNED 100
6 0 UNASSIGNED 100
7 0 UNASSIGNED 100
Under normal circumstances, the Standby database Standby Logfile Group must have at least one GROUP that is Active. No, so data cannot be synchronized in real time. After careful observation, we can find that the Thread here is equal to 0, this may be the reason why the Standby database cannot find the Standby Logfile for real-time synchronization.

Run the following SQL statement to start the backup database to the MOUNT state and specify the Thread Number of Standby Logfile:
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.20.3e + 11 bytes
Fixed Size 2245480 bytes
Variable Size 8858373272 bytes
Database Buffers 1.2778E + 11 bytes
Redo Buffers 189480960 bytes
Database mounted.
SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('/oradata/orcl/sredo05.log') SIZE 100 M reuse;

Database altered.

SQL> select group #, thread #, status, bytes/1024/1024 mb from v $ standby_log;

GROUP # THREAD # STATUS MB
----------------------------------------
4 0 UNASSIGNED 100
5 0 UNASSIGNED 100
6 0 UNASSIGNED 100
7 0 UNASSIGNED 100
8 1 UNASSIGNED 100

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILEThread 1 group 4('/Oradata/orcl/sredo01.log') SIZE 100 M reuse;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 ('/oradata/orcl/sredo02.log') SIZE 100 M reuse;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 6 ('/oradata/orcl/sredo03.log') SIZE 100 M reuse;
Alter database add standby logfile thread 1 group 7 ('/oradata/orcl/sredo04.log') SIZE 100 M reuse;
Database altered.
 
SQL> select group #, thread #, status, bytes/1024/1024 mb from v $ standby_log;

GROUP # THREAD # STATUS MB
----------------------------------------
4 1 UNASSIGNED 100
5 1 UNASSIGNED 100
6 1 UNASSIGNED 100
7 1 UNASSIGNED 100
1 ACTIVE 100

After that, Data Synchronization resumes normal operation. The common operations still need to be completed on the master database. During the real-time synchronization between the master database and the slave database, switching between two or more Standby logfiles will continue.

-- End --