Precautions before a shutdown a database

Before a shutdown the Production or any important database, take some basic precaution besides the backup.

Create an init parameter file from current spfile (If database is using spfile)

In case, If database is not mounting / coming up or need to disable some of parameter(s) or database feature to startup :

SQL> create pfile from spfile;

Create backup of a control file in text format

To know the location and data file's name of a database, In case if a database is not mounting or opening after shutdown

SQL> alter database backup controlfile to trace;
Database altered.

Go to udump folder and check for latest trc file to get control file definition:

[oracle@xxdb1 udump]$ ll -ltr tail -2
-rw-r----- 1 oracle oinstall 743 Apr 24 00:15 xxx1_ora_9000.trc
-rw-r----- 1 oracle oinstall 18004 Apr 24 07:23 xxx1_ora_30518.trc

[oracle@xxxdb1 udump]$ vi xxx1_ora_30518.trc

Check the status of the data files

To confirm, All the data files are available and none of them offline

SQL> select TABLESPACE_NAME, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME ONLINE_
------------------------------ -------
SYSTEM SYSTEM
UNDOTBS1 ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
.....

Review the last 200 lines of alert log file:

For any possible error message or problem

[oracle@xxxdb1 bdump]$ view alert_db_name.log

Open the alert log file as tail -f alert_db_name.log

Make a practice to view what is going with a database, When are you performing some major changes or activities on a database:

[oracle@xxxdb1 ~]$ tail -f /oracle/admin/xxx1/bdump/alert_xxx1.log

In Data Guard / Standby setup

Before a shutdown of a database and after open of a database:

open an alert log as tail -f alert_db_name.log at primary and a standby database:

[oracle@xxxdb1 ~]$ tail -f /oracle/admin/xxx1/bdump/alert_xxx1.log

[oracle@xxxdb2 ~]$ tail -f /oracle/admin/xxx2/bdump/alert_xxx2.log

Switch an archive log manually, checked archive has shipped and media recovery process is applying it.

SQL> alter system switch logfile;

---content from Standby database alert_db_name.log --------
Fri Apr 23 23:41:47 2010
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[24]: Assigned to RFS process 14252
RFS[24]: Identified database type as 'physical standby'
Fri Apr 23 23:43:07 2010
Primary database is in MAXIMUM PERFORMANCE mode
RFS[23]: Successfully opened standby log 8: '/oracle/oradata/xxx2/redo/stby_redo08a.log'
Fri Apr 23 23:43:12 2010
Media Recovery Log /oracle/backups/xxx2/stby_archive/1_12529_704597469.arc
Media Recovery Waiting for thread 1 sequence 12530 (in transit)
Fri Apr 23 23:43:19 2010
Primary database is in MAXIMUM PERFORMANCE mode
RFS[23]: Successfully opened standby log 8: '/oracle/oradata/xxx2/redo/stby_redo08a.log'
Fri Apr 23 23:43:23 2010
Media Recovery Log /oracle/backups/xxx2/stby_archive/1_12530_704597469.arc
Media Recovery Waiting for thread 1 sequence 12531 (in transit)

Managing Archive Gaps Manually on Standby Database Setup

To determine if there is an archive gap on your physical standby database

SQL> SELECT * FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
----------- ------------- --------------
1 12499 12511


In this case, Standby database is waiting for archive log 12499

SQL> select process,sequence#,status from v$managed_standby;

PROCESS SEQUENCE# STATUS
--------- ---------- ------------
ARCH 12509 CLOSING
ARCH 12510 CLOSING
ARCH 12504 CLOSING
ARCH 12508 CLOSING
MRP0 12499 WAIT_FOR_GAP
RFS 12511 IDLE

6 rows selected.

But if you check, archives have been either shipped already or even if you copy these on to Standby server (MRP is still waiting for 12499)

[oracle@xxxdb2 ~]$ cd /oracle/backups/xxx2/stby_archive
[oracle@xxxdb2 stby_archive]$ ll -l 1_12499*
-rw-r----- 1 oracle oinstall 85923328 Apr 23 19:01 1_12499_704597469.arc
[oracle@xxxdb2 stby_archive]$ ll -l 1_1250* head -5
-rw-r----- 1 oracle oinstall 85897728 Apr 23 19:38 1_12500_704597469.arc
-rw-r----- 1 oracle oinstall 83760640 Apr 23 19:39 1_12501_704597469.arc
-rw-r----- 1 oracle oinstall 1121792 Apr 23 19:39 1_12502_704597469.arc
-rw-r----- 1 oracle oinstall 21140480 Apr 23 19:39 1_12503_704597469.arc
-rw-r----- 1 oracle oinstall 246272 Apr 23 19:31 1_12504_704597469.arc
[oracle@xxxdb2 stby_archive]$


Below SQL is also confirmed for same:


SQL> column dest format a20
SQL> select substr(DEST_NAME,1,20) dest ,archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;


DEST ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
----------------------- ---------------- ------------- --------------- ------------
LOG_ARCHIVE_DEST_1 0 0 0 0
LOG_ARCHIVE_DEST_2 1 12509 0 0
….
LOG_ARCHIVE_DEST_10 0 0 0 0
STANDBY_ARCHIVE_DEST 1 12508 1 12498

11 rows selected.



You may find similar entry or error in standby database's alert log file:

Media Recovery Waiting for thread 1 sequence 12499
Fetching gap sequence in thread 1, gap sequence 12499-12503
Fri Apr 23 21:56:44 2010
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 12499-12503
DBID 2467369570 branch 704597469
FAL[client]: All defined FAL servers have been attempted.


Some time, due to the cancelation of Recovery Process at standby causes a partial apply of archive log. To fix this problem


To find out archive log at Primary database Server:

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# between 12499 and 12503;

Copy the missing archives to Standby Server (optional).

Register them using the ALTER DATABASE REGISTER LOGFILE sQL on your physical standby database (to re-appy them):

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12499_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12500_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12501_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12502_704597469.arc';
Database altered.

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle/backups/xxx2/stby_archive/1_12503_704597469.arc';
Database altered.

MRP automatically start and apply the archive one by one.