ORACLE DATAGUARD SWITCHOVER OPERATION

At this article I’ll present Data Guard Switch OVER. During the run time system if you prefer to  change role of your primary Database to Standby Database you’d need Swicth OVER the Databases without lost of data or resetting of redo logs.

I tested the Switchover first on my testing systems before working on Production.

Verify the primary database instance is open and the standby database instance is mounted.

Make sure that there are no active users connected to the databases.

ON THE PRIMARY QUERY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

------------------

TO STANDBY

Make sure you move the all log file. Force a log switch to archive the current online redo log file on primary

SQL> ALTER system switch logfile;

Verify the new redo data was archived on the standby database.

SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Make sure (non-broker) on the standby

SQL>select process from v$managed_standby where process like 'MRP%';

And Make sure there is no GAP

SQL> select status, gap_status

  2  from v$archive_dest_status

  3  where dest_id = 2;

STATUS    GAP_STATUS

--------- ------------------------

VALID     NO GAP

Now Switch the primary database to the new standby:

SQL> alter database commit to switchover to physical standby ;

Database altered.

Shutdown the database.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

Start up the database no mount mode;

SQL> startup nomount;

 

Now mount the database as standby.

SQL> alter database mount standby database;

Database altered.

Defer the remote archive destination on the old primary:

SQL> alter system set log_archive_dest_state_2=defer;

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

------------------

SWITCHOVER PENDING

ON THE STANDBY QUERY

Switch the physical standby to the new primary:

SQL> alter database commit to switchover to primary;

Database altered.

Shutdown the database.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

….

…

..

Database mounted.

Database opened.

 Now enable remote archiving on the new primary to the new standby:

SQL> alter system set log_archive_dest_state_2=enable;

So, At last I'm going to start managed recover on the new standby database:

SQL> recover managed standby database disconnect; 

Media recovery complete.

ORA-01110: ORA-01113: ORA-00283: Errors

This post present the following error's solution.

ORA-00283: recovery session canceled due to errors
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

** This article is on my testing database that's why i didn't hesitate to recover until cansel also i had a same standby  database to get my database back. but make sure you have ALL DATABASE BACKUP in case. 

SQL> shu immediate   
Database closed.
Database dismounted.   
ORACLE instance shut down.   

SQL> startup   mount;
ORACLE instance mounted.  

RMAN> alter database open resetlogs;   RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============  RMAN-00571: ===========================================================  RMAN-03002: failure of alter db command at 10/07/2014 10:30:25  ORA-01139: RESETLOGS option only valid after an incomplete database recovery  


RMAN> alter database open;   
RMAN-00571: ===========================================================  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============  RMAN-00571: ===========================================================  RMAN-03002: failure of alter db command at 10/07/2014 10:32:31  ORA-01122: database file 1 failed verification check   
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'  ORA-01207: file is more recent than control file - old control file   


Restore The Database...

RMAN> restore database;    
Starting restore at 11-JULY-14   
using channel ORA_DISK_1    
channel ORA_DISK_1: starting datafile backupset restore  channel ORA_DISK_1: specifying datafile(s) to restore from backup set  restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf  restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf   
restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf  channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl/1mlmljvq_1_1  channel ORA_DISK_1: restored backup piece 1   
piece handle=/u01/app/oracle/backup/orcl/1mlmljvq_1_1 tag=TAG20100830T185905  channel ORA_DISK_1: restore complete, elapsed time: 00:00:39  channel ORA_DISK_1: starting datafile backupset restore  channel ORA_DISK_1: specifying datafile(s) to restore from backup set  restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf  channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/orcl/1vlmncgc_1_1  channel ORA_DISK_1: restored backup piece 1   
piece handle=/u01/app/oracle/backup/orcl/1vlmncgc_1_1 tag=TAG20100831T110340  channel ORA_DISK_1: restore complete, elapsed time: 00:02:43  Finished restore at 11-JULY-14 

And Recover the Database.

RMAN> recover database;  
Starting recover at 11-JUNY-14  

using channel ORA_DISK_1   
starting media recovery............................
 ..
..
..
...
....
...
...



RMAN> exit   
Recovery Manager complete.

SQL> shu immediate  

ORA-01109: database not open  

  

  

Database dismounted.  

ORACLE instance shut down.  

SQL> startup  

ORACLE instance started. 

Total System Global Area  444596224 bytes  

Fixed Size                  1219904 bytes  

Variable Size             188744384 bytes  

Database Buffers          251658240 bytes  

Redo Buffers                2973696 bytes  

Database mounted.  

ORA-01113: file 1 needs media recovery  

ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'  

  

  My Database couldnt open try to open again.

 

  

SQL> alter database open;   

alter database open  

*  

ERROR at line 1:  

ORA-01113: file 1 needs media recovery  

ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'  

  

This command will recover until first archive log.

  

SQL>  recover database until cancel;  

ORA-00279: change 696770 generated at 10/07/2014 10:43:53 needed for thread 1  

ORA-00289: suggestion :  

/u01/app/oracle/flash_recovery_area/orcl/archivelog/2014_07_11/o1_mf_1_2_%u_.arc  

ORA-00280: change 696770 for thread 1 is in sequence #2  

  

  

Specify log: {<ret>=suggested | filename | AUTO | CANCEL}  

CANCEL

Media recovery cancelled.  

SQL> alter database open resetlogs;  

  

Database altered.  

  

SQL> archive log list;  

Database log mode              Archive Mode  

Automatic archival             Enabled  

Archive destination            USE_DB_RECOVERY_FILE_DEST  

Oldest online log sequence     1  

Next log sequence to archive   1  

Current log sequence           1  

SQL>  

Oracle Database Resizing Redolog files

Re size Redo log file

At this Article I'd like to present Resizing Redo log file, So 2 days ago i got a question "how do you re size redo log file" at my intern view. The simple question was good and i decided to post about this issue.

First of all lets check our redo log group.

 SQL>  select x.group#,x.member,y.bytes/1024/1024 "size"

  2  from v$logfile x, v$log y

  3  where x.group# = y.group#;

    GROUP# MEMBER                                         size

---------- ---------------------------------------- ----------

         3 /u01/app/oracle/oradata/orcl/redo03.log          50

         2 /u01/app/oracle/oradata/orcl/redo02.log          50

         1 /u01/app/oracle/oradata/orcl/redo01.log          50

I've 3 group and 50mb size of them.

Lets check the status 

SQL> select group#,status from v$log;

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 CURRENT

         3 INACTIVE

 Now switch the log file to the last group.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 ACTIVE

         3 CURRENT

 If we want to make active group to inactive we would use the checkpoint command.

SQL> alter system checkpoint global;

System altered.

 So I'm gonna re size first log file from 50mb to 60mb.

first make sure group 1 is inactive then drop it.

SQL> alter database drop logfile group 1;

Database altered.

 i have got 3 group that's why i should have at less 2 group at my database. if you try to drop another one you would got a error.

Now I'm going to add my new group 1 sized 60mb

SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/orcl/redo01.log') size 60m reuse;

Database altered.

check the status.

SQL> select group#,status from v$log;

    GROUP# STATUS

---------- ----------------

         1 UNUSED

         2 INACTIVE

         3 CURRENT

 okay its done.

check the size of group

SQL> select x.group#,x.member,y.bytes/1024/1024 "size"

  2  from v$logfile x, v$log y

  3  where x.group# = y.group#;

    GROUP# MEMBER                                         size

---------- ---------------------------------------- ----------

         3 /u01/app/oracle/oradata/orcl/redo03.log          50

         2 /u01/app/oracle/oradata/orcl/redo02.log          50

         1 /u01/app/oracle/oradata/orcl/redo01.log          60

So that's it,