New Generation Consultants is a global applications systems integration and consulting company offering services in the area of Applications Implementation, Middleware and SOA Consulting, Applications Upgrades, Business Intelligence, Technology Consulting and Managed Services.
The assumption here is that we have exhausted all possible locations to find another good and valid copy or backup of the archivelog that we are looking for, which could be in one of the following:
- directories defined in the LOG_ARCHIVE_DEST_n - another directory in the same server or another server - standby database - RMAN backup - OS backup
If the archivelog is not found in any of the above mentioned locations, then the approach and strategy on how to recover and open the database depends on the SCN (System Change Number) of the datafiles, as well as, whether the log sequence# required for the recovery is still available in the online redologs.
For the SCN of the datafiles, it is important to know the mode of the database when the datafiles are backed up. That is whether the database is open, mounted or shutdown (normally) when the backup is taken.
If the datafiles are restored from an online or hot backup, which means that the database is open when the backup is taken, then we must apply at least the archivelog(s) or redolog(s) whose log sequence# are generated from the beginning and until the completion of the said backup that was used to restore the datafiles.
However, if the datafiles are restored from an offline or cold backup, and the database is cleanly shutdown before the backup is taken, that means that the database is either not open, is in nomount mode or mounted when the backup is taken, then the datafiles are already synchronized in terms of their SCN. In this situation, we can immediately open the database without even applying archivelogs, because the datafiles are already in a consistent state, except if there is a requirement to roll the database forward to a point-in-time after the said backup is taken. The critical key thing here is to ensure that all of the online datafiles are synchronized in terms of their SCN before we can normally open the database. So, run the following SQL statement, as shown below, to determine whether the datafiles are synchronized or not. Take note that we query the V$DATAFILE_HEADER, because we want to know the SCN recorded in the header of the physical datafile, and not the V$DATAFILE, which derives the information from the controlfile.
select status, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*) from v$datafile_header group by status, checkpoint_change#, checkpoint_time order by status, checkpoint_change#, checkpoint_time;
The results of the above query must return one and only one row for the online datafiles, which means that they are already synchronized in terms of their SCN. Otherwise, if the results return more than one row for the online datafiles, then the datafiles are still not synchronized yet. In this case, we need to apply archivelog(s) or redolog(s) to synchronize all of the online datafiles. Take note of the CHECKPOINT_TIME in the V$DATAFILE_HEADER, which indicates the date and time how far the datafiles have been recovered. The results of the query above may return some offline datafiles. So, ensure that all of the required datafiles are online, because we may not be able to recover later the offline datafile once we open the database in resetlogs. Even though we can recover the database beyond resetlogs for the Oracle database starting from 10g and later versions due to the introduction of the format "%R" in the LOG_ARCHIVE_FORMAT, it is recommended that you online the required datafiles now than after the database is open in resetlogs to avoid any possible problems. However, in some cases, we intentionally offline the datafile(s), because we are doing a partial database restore, or perhaps we don't need the contents of the said datafile.
You may run the following query to determine the offline datafiles:
select file#, name from v$datafile where file# in (select file# from v$datafile_header where status='OFFLINE');
You may issue the following SQL statement to change the status of the required datafile(s) from "OFFLINE" to "ONLINE":
alter database datafile online;
If we are lucky that the required log sequence# is still available in the online redologs and the corresponding redolog member is still physically existing on disk, then we may apply them instead of the archivelog. To confirm, issue the following query, as shown below, that is to determine the redolog member(s) that you can apply to recover the database:
set echo on feedback on pagesize 100 numwidth 16 alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; select LF.member, L.group#, L.thread#, L.sequence#, L.status, L.first_change#, L.first_time, DF.min_checkpoint_change# from v$log L, v$logfile LF, (select min(checkpoint_change#) min_checkpoint_change# from v$datafile_header where status='ONLINE') DF where LF.group# = L.group# and L.first_change# >= DF.min_checkpoint_change#;
If the above query returns no rows, because the V$DATABASE.CONTROLFILE_TYPE has a value of "BACKUP", then try to apply each of the redolog membes one at a time during the recovery. You may run the following query to determine the redolog members:
select * from v$logfile;
If you have tried to apply all of the online redolog members instead of an archivelog during the recovery, but you always received the ORA-00310 error, as shown in the example below, then the log sequence# required for recovery is no longer available in the online redolog.
ORA-00279: change 189189555 generated at 11/03/2007 09:27:46 needed for thread 1 ORA-00289: suggestion : +BACKUP ORA-00280: change 189189555 for thread 1 is in sequence #428 Specify log: {=suggested filename AUTO CANCEL} +BACKUP/prmy/onlinelog/group_2.258.603422107 ORA-00310: archived log contains sequence 503; sequence 428 required ORA-00334: archived log: '+BACKUP/prmy/onlinelog/group_2.258.603422107'
After trying all of the possible solutions mentioned above, but you still cannot open the database, because the archivelog required for recovery is either missing, lost or corrupted, or the corresponding log sequence# is no longer available in the online redolog, since they are already overwritten during the redolog switches, then we cannot normally open the database, since the datafiles are in an inconsistent state. So, the following are the 3 options available to allow you to open the database:
Option#1: Force open the database by setting some hidden parameters in the init.ora. Note that you can only do this under the guidance of Oracle Support with a service request. But there is no 100% guarantee that this will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that you have a good and valid backup of the current database.
Option#2: If you have a good and valid backup of the database, then restore the database from the said backup, and recover the database by applying up to the last available archivelog. In this option, we will only recover the database up to the last archivelog that is applied, and any data after that are lost. If no archivelogs are applied at all, then we can only recover the database from the backup that is restored. However, if we restored from an online or hot backup, then we may not be able to open the database, because we still need to apply the archivelogs generated during the said backup in order to synchronize the SCN of the datafiles before we can normally open the database.
Option#3: Manually extract the data using the Oracle's Data Unloader (DUL), which is performed by Oracle Field Support at the customer site on the next business day and for an extra charge. If the customer wants to pursue this approach, we need the complete name, phone# and email address of the person who has the authority to sign the work order in behalf of the customer.
This script can be used to purge database backups taken using the script in the previous post. (replace the argument after “mtime” with the required number of days beyond which the backups can be deleted).
======================================================== DATE=`date +%d%b%y_%H_%M_%S` LOGFILE='/t2/backups/logs/dbtech_backup_'${DATE}'.log' ORACLE_BASE=/t2/oracleORACLE_HOME=/t2/oracle/PROD/db/tech_st/10.2.0 ORACLE_SID=PROD WORK=/t2/scripts EXEC_DIR=/t2/scripts LOG_HOME=/t2/backups/logs DB_BACKUP_LOC=/t2/backups/dbtech export ORACLE_BASE ORACLE_SID ORACLE_HOME LOG_HOME DB_BACKUP_LOC TNS_ADMIN=$ORACLE_HOME/network/admin/PROD_perp SHELL=/usr/bin/ksh LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib export TNS_ADMIN SHELL LD_LIBRARY_PATH PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/usr/lib:/etc:$ORACLE_HOME/bin:$LD_LIBRARY_PATH export PATH TERM=vt100 export TERM NLS_LANG=American_America.UTF8 export NLS_LANG find $LOG_HOME -type f -mtime +7 xargs rm -f find $ORACLE_HOME/admin/PROD_perp/bdump -type f -mtime +14 xargs rm -f find $ORACLE_HOME/admin/PROD_perp/udump -type f -mtime +14 xargs rm -f find $ORACLE_HOME/admin/PROD_perp/cdump -type f -mtime +14 xargs rm -f find $DB_BACKUP_LOC -type f -mtime +6 > $WORK/purge.list if [ -s $WORK/purge.list ]then for file in `cat $EXEC_DIR/purge.list` do rm ${file} done for address in `cat $EXEC_DIR/mail_list` do if [ "`echo $address cut -c1`" != "#" ]; then mail -s "${ORACLE_SID} - DB Purge Success Log" $address < $WORK/purge.list sleep 1 fi done else for address in `cat $EXEC_DIR/mail_list` do if [ "`echo $address cut -c1`" != "#" ]; then echo "No Files found in backup directory to purge!!" mail -s "${ORACLE_SID} - DB Purge Failure Log" $address fi done fi find $WORK/purge.list xargs rm -f ========================================================
This script can be used to take a backup of the database stack. A database backup purge script is given in the next post which can be used with this script.
===================================================== DATE=`date +%d%b%y_%H_%M_%S` LOGFILE='/t2/backups/logs/dbtech_backup_'${DATE}'.log' ORACLE_BASE=/t2/oracle ORACLE_HOME=/t2/oracle/PROD/db/tech_st/10.2.0 ORACLE_SID=PROD WORK=/t2/scripts EXEC_DIR=/t2/scripts export ORACLE_BASE ORACLE_SID ORACLE_HOME TNS_ADMIN=$ORACLE_HOME/network/admin/PROD_perp SHELL=/usr/bin/ksh LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib export TNS_ADMIN SHELL LD_LIBRARY_PATH PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/usr/lib:/etc:$ORACLE_HOME/bin:$LD_LIBRARY_PATH export PATH TERM=vt100 export TERM NLS_LANG=American_America.UTF8 export NLS_LANG APPS_PASSWD=`cat apps_pass.txt` echo $APPS_PASSWD perl /t2/oracle/PROD/db/tech_st/10.2.0/appsutil/scripts/PROD_perp/adpreclone.pl dbTier >> $LOGFILE # 781539.1 tar -czvf /t2/backups/dbtech/PROD_db_${DATE}.tgz $ORACLE_HOME >> $LOGFILE returncode=$? echo $returncode return code >> $LOGFILE date >> $LOGFILE if [ $returncode -ne 0 ]; then for address in `cat $EXEC_DIR/mail_list` do if [ "`echo $address cut -c1`" != "#" ]; then tail -n 100 $LOGFILE mail -s "TAR Database Techstack Backup Failure Log" $address echo "TAR Database Techstack Backup Failure Log" >> $LOGFILE fi done else for address in `cat $EXEC_DIR/mail_list` do if [ "`echo $address cut -c1`" != "#" ]; then tail -n 100 $LOGFILE mail -s "TAR Database Techstack Backup Successful Log" $address echo "TAR Database Techstack Backup Successful Log" >> $LOGFILE fi done fi =====================================================
The symbolic links are not created in SLES8 and after with the 'chkconfig -add' command. To have the symbolic links created run the following in addition:
/sbin/chkconfig --set dbora 35
- Now the instance will startup automatically when Linux boots up.
