Recover Standby database from ORA-00332: archived log is too small


When a standby database crashes and upon instance recovery gets stuck with error ORA-00332: archived log is too small - may be incompletely archived, then following set of actions must be carried out to restore the service.1. Check that the instance is in MOUNTED. (select status from v$instance)

2. If not mounted, then start the database in mounted modeSQL> startup mount;
3. Search the alert log to identify the archive log that is corrupt, 
Sample lines from the alert log
Errors in file /u01/app/oracle/diag/rdbms/fxfgsby/FXFGSBY1/trace/FXFGSBY1_ora_19463.trc:
ORA-00332: archived log is too small - may be incompletely archived
ORA-00334: archived log: '+FLASH/fxfgsby/archivelog/2013_08_19/thread_1_seq_173831.5998.823855747'
Shutting down recovery slaves due to error 332


4. In the above example, the corrupt log file belongs to Thread#=1 and Sequence#=173831

5. Log on to the Primary database cluster and take a copy of the file from ASM onto the local file system.

Note 1: Check the file size on chose the appropriate copy method. (ls -s filename) Note 2: If the archive log is >= 1G then use the DBMS_FILE_TRANSFER method.
dbms_file_transfer.COPY_FILE(source_directory_object=> , source_file_name=>, destination_directory_object=>, destination_file_name=>)
Note 3: If the archive log is < 1G then use the normal "cp" method

6. Transfer the archive log to GVA server.

7. Copy the file into ASM into the appropriate dated folder. For instance the log file in example was from the 19-Aug-2013 folder, then copy it into +FLASH/[dbname]/archivelog/[date]
The destination file name must be of the format thread_[thread_no]_[seq_no] 
Do not include the db and file incarnation number. These will be auto generated by ASM.
Note 1: If the archive log is >= 1G then use the DBMS_FILE_TRANSFER method.
dbms_file_transfer.COPY_FILE(source_directory_object=> , source_file_name=>, destination_directory_object=>, destination_file_name=>)
Note 2: If the archive log is < 1G then use the normal "cp" method

8. Make a note of the full file name of the copied file. e.g thread_1_seq_173831.6767.823855747

9. Register the log file back into the database. Note the database must be mounted for this operation to be successful
SQL> alter database register or replace logfile '[filename with the full path]';


10. Start MRP in archive log mode 
SQL> alter database recover managed standby database disconnect from session;

11. Tail the alert log to verify Media recovery is progressing as expected. Wait util the the last of the available logs have been consumed

12. Stop MRP
SQL> alter database recover managed standby database cancel;

13. Start MRP in real time mode
SQL> alter database recover managed standby database disconnect from session using current logfile;

14. You can now open the database using the "alter database open" command or use the srvctl command line utility to restart the database.

Comments

Post a Comment

Popular posts from this blog

Check Active Dataguard Replication Status

In order to check if ADG is up-to-date, one has to tail the alert log on both the Primary and Physical Standby databases, followed by a manual check to look for the log status of (in-transit) against each of the threads. While this method is perfect it can be bit tedious to perform. You can now do the same using one of the attached SQL from the Primary database. Deployment and usage steps - Open a vi editor on the Primary database. - Copy of the below SQL into the editor - Complete the  database link creation command based on the database    you are checking. - Save the text file as check_adg_status.sql - execute the sql file as sys user. e.g. sqlplus -s  / as sysdba @check_adg_status.sql  Note : These commands cannot be run from the Physical standby as the query relies on using a temporary database link that cannot be created on a read-only database. Troubleshooting 1. Some database clusters may report the following error when invoked   ...
Read more

Top CPU consuming oracle sessions

Use the shell script from  here  to obtain the details of the Oracle sessions using high CPU.  Usage: Log in as oracle user and navigate to the folder where the script has been copied into. Do not forget to grant execute permissions to the script prior to invoking it. $ ./top_oracle_sessions.sh [cpu_threshold]  e.g.1  $ ./top_oracle_sessions.sh 90       This will gather information if overall CPU usage is above 90% e.g.1  $ ./top_oracle_sessions.sh        This will gather information if overall CPU usage is above 50%. This is the default setting. Notes: Use the attached script to obtain the details of the Oracle sessions using high CPU. The script works by follows Takes 2 snapshots using Linux "top" command with a interval of 2 seconds between snaps Uses the data from the second snapshot to determine if the overall CPU usage is above a set threshold. The threshold is a conf...
Read more