Adding a New Hard Disk in Linux

By Kamran Agayev A.

Each Oracle DBA has to secure the database against data loss. In the first place, files forming database’s physical structure have to be secured. As we know the physical structure of Oracle Database mainly consist of Control Files, Redo Log files, Data files and Archived Redo Log files. Today, I’m going to show you how to protect control files which are important for database, as well as the way to restore control file in case it is lost.

“Loss of Control file” means corruption of hard disk in which Control Files are locate. Oracle DBA should configure database so that in case Control file located hard disk get corrupted, it is able to restore the database with survived one within short span of time possible.

When we say “loss of Control file”, we mean corruption of hard drive where Control files locate.

Today we’re going to analyze three different projects related to Control File backup and recovery

1. As Control File has been multiplexed, in spite of its loss we’ll start our database with survived one stored in another hard disk
2.  After we get “creation script” of control file, we create new control file using that script and start our database when all Control Files are lost
3. After we get binary copy of the Control File, we recover our database using archived redo log files in spite of some changes made to database after the binary backup of the Control file

Project 1

First of all, we create new “virtual hard disk”. Then we multiplex Control Files and put the multiplexed Control file into “virtual hard disk”. Then we remove this hard disk and show how the control file is lost and get error during startup of database. Then as the control file has been multiplexed, we retrieve Control file from another hard drive that is safe and open the database

My task plan is as follows:

1.  Mounting additional hard drive into Linux

2.  Multiplexing Control File

3.  Removing newly added hard drive, loss of control file, dysfunction of database

4.  Retrieval of Control file

Before using Control files, we need to know “what the Control file is and why it’s so useful for database?”

Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

• The database name

• Names and locations of associated data files and redo log files

• The timestamp of the database creation

• The current log sequence number

• Checkpoint information

The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

When we install Oracle database, it automatically creates 3 copies of Control file in the same directory as a default.

My first step will be adding new hard disk to locate a copy of Control file:

1. In order to do it, shutdown VMware and by entering “Edit Virtual machine Settings” window click on “Add”button

2. Select “Hard Disk” and click on Next

3. Leave the option to be as default and Click on “Next”

4. Click on Next and leave the option to be as default “SCSI”

5. Specify disk capacity as “100MB”. So type “0.1” (GB) in the text field and check “Allocate all disk space now” checkbox

6. Specify destination where you want to save information of new hard disk and click on “Finish” button.

You will then see new hard disk being created with 100Mb size

After applying new hard disk into VMware, we start OS. Unlike Windows, in Linux newly applied hard disk should be mounted to system in order to be recognized by OS. In order to mount it to OS, we follow the following steps:

1. In Linux, SCSI disks are labled /dev/sda, /dev/sdb, /dev/sdc etc… to represent the first, second, third,… SCSI hard drives
   
   /dev/sda is hard disk where OS and Oracle are installed. Its size is 15GB. The hard disk that newly created is /dev/sdb. We should mount it to our system. We use fdisk program to create a partition and mount this hard drive to our system. To mount this hard disk to any folder, we need to create that folder  
   
   [root@localhost ~]# cd /
   [root@localhost /]# mkdir /control_file_folder
   
   Here, new folder named “/control_file_folder” created in the root directory. Now using fdisk program, create a new partition
   
   [root@localhost /]# /sbin/fdisk /dev/sdb
   
   Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklab
   
   Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won’t be recoverable.
   
   Warning: invalid flag 0×0000 of partition table 4 will be corrected by w(rite)
   
   Command (m for help): m  (Click m to view all commands)
   
   Command action
   
      a   toggle a bootable flag
      b   edit bsd disklabel
      c   toggle the dos compatibility flag
      d   delete a partition
      l   list known partition types
      m   print this menu
      n   add a new partition
      o   create a new empty DOS partition table
      p   print the partition table
      q   quit without saving changes
      s   create a new empty Sun disklabel
      t   change a partition’s system id
      u   change display/entry units
      v   verify the partition table
      w   write table to disk and exit
      x   extra functionality (experts only)
   
   Command (m for help): n
   
   Command action
   
      e   extended
      p   primary partition (1-4)
   
   e
   
   Partition number (1-4): 1
   
   First cylinder (1-102, default 1): 1
   
   Last cylinder or +size or +sizeM or +sizeK (1-102, default 102):
   
   Using default value 102
   
   Command (m for help): p
   
   Disk /dev/sdb: 106 MB, 106954752 bytes
   
   64 heads, 32 sectors/track, 102 cylinders
   
   Units = cylinders of 2048 * 512 = 1048576 bytes
   
   Device Boot      Start         End      Blocks   Id  System
   /dev/sdb1               1         102      104432    5  Extended
   
   Command (m for help): w
   
   The partition table has been altered!
   
   Calling ioctl() to re-read partition table.
   
   Syncing disks.
   
   Now using mkfs program, build Linux file system on this device. mkfs  is used to build a Linux file system on a device, usually a hard disk partition
   
   [root@localhost /]# mkfs -t ext3 /dev/sdb
   
   mke2fs 1.35 (28-Feb-2004)
   
   /dev/sdb is entire device, not just one partition!
   
   Proceed anyway? (y,n) y
   
   Filesystem label=
   
   OS type: Linux
   
   Block size=1024 (log=0)
   
   Fragment size=1024 (log=0)
   
   26208 inodes, 104448 blocks
   
   5222 blocks (5.00%) reserved for the super user
   
   First data block=1
   
   Maximum filesystem blocks=67371008
   
   13 block groups
   
   8192 blocks per group, 8192 fragments per group
   
   2016 inodes per group
   
   Superblock backups stored on blocks:
   
           8193, 24577, 40961, 57345, 73729
   
   Writing inode tables: done
   
   Creating journal (4096 blocks): done
   
   Writing superblocks and filesystem accounting information: done
   
   This filesystem will be automatically checked every 30 mounts or 180 days, whichever comes first.  Use tune2fs -c or -i to override.
   
   Now that the new hard drive is partition and formated, the last step is to mount the new drive. Mount the new hard drive on the directory /control_file_folder using mount command
   
   [root@localhost /]# mount -t ext3 /dev/sdb /control_file_folder/
   
   [root@localhost /]# cd /control_file_folder/
   
   [root@localhost control_file_folder]# df -h
   
   Filesystem            Size  Used Avail Use% Mounted on
   
   /dev/mapper/VolGroup00-LogVol00
   
                          14G  9.6G  3.4G  74% /
   
   /dev/sda1              99M   13M   82M  14% /boot
   
   none                  252M     0  252M   0% /dev/shm
   
   /dev/sdb               99M  5.6M   89M   6%
   
   /control_file_folder
   
   [root@localhost control_file_folder]#
   
2. Enter the drive into the fstab file so that it is recognized and mounted upon system boot
   
   /dev/sdb      /control_file_folder auto   defaults      1 2
   
   Now reboot your system and after reboot, run
   
   [root@localhost /]# df -h
   
   Filesystem            Size  Used Avail Use% Mounted on
   
   /dev/mapper/VolGroup00-LogVol00
                          14G  9.6G  3.4G  74% /
   
   /dev/sda1              99M   13M   82M  14% /boot
   
   none                  252M     0  252M   0% /dev/shm
   
   /dev/sdb               99M  5.6M   89M   6%
   
   control_file_folder
   
   As seen, the newly created hard drive was mounted automatically to the folder /control_file_folder after reboot
   

3. In this step, multiplex the Control File.

4. Now let’s remove that hard disk and see what is going to happen to database
   
   Before removing hard disk, shutdown OS
   
   Select new hard disk on the main page and click on “Remove” button to remove that hard disk
   
   Then start the system. Open new terminal and type “df –h” to verify that hard disk was not mounted
   
   The hard disk created and mounted a while ago in the list has disappeared. This is because the Control file located on that hard disk has been deleted. Since the Control File has been deleted, the database doesn’t start.
   
   To verify this, connect to database and try to start it
   
   [oracle@localhost /]$ sqlplus “/as sysdba”
   
   SQL*Plus: Release 10.1.0.3.0 – Production on Sun Mar 15 00:57:19 2009
   
   Copyright (c) 1982, 2004, Oracle.  All rights reserved.
   
   Connected to an idle instance.
   
   SQL> startup
   
   ORACLE instance started.
   
   Total System Global Area  167772160 bytes
   
   Fixed Size                   778212 bytes
   
   Variable Size              61874204 bytes
   
   Database Buffers          104857600 bytes
   
   Redo Buffers                 262144 bytes
   
   ORA-00205: error in identifying controlfile, check alert log for more info
   
   SQL> alter database open;
   
   alter database open
   
   *
   
   ERROR at line 1:
   
   ORA-01507: database not mounted
   
   As seen from above, database didn’t start. To check the reason, open alert.log file to investigate it. Use tail -20 command to get last 20 lines of the file. To exit from Sql*Plus by keeping it connected, use host command. This will take you to OS terminal, and by running exit command, you can return to Sql*Plus easily and you don’t need to connect once again to database
   
   SQL> host
   
   [oracle@localhost /]$ tail -20 /home/oracle/product/10.1.0/Db_1/admin/qafqaz/bdump/alert_qafqaz.log
   
   SMON started with pid=7, OS id=4374
   
   RECO started with pid=8, OS id=4376
   
   CJQ0 started with pid=9, OS id=4378
   
   Sun Mar 15 00:57:22 2009
   
   starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’…
   
   starting up 1 shared server(s) …
   
   Sun Mar 15 00:57:22 2009
   
   ALTER DATABASE   MOUNT
   
   Sun Mar 15 00:57:22 2009
   
   ORA-00202: controlfile: ‘/control_file_folder/control02.ctl’
   
   ORA-27037: unable to obtain file status
   
   Linux Error: 2: No such file or directory
   
   Additional information: 3
   
   Sun Mar 15 00:57:22 2009
   
   Controlfile identified with block size 16384
   
   Sun Mar 15 00:57:25 2009
   
   ORA-205 signalled during: ALTER DATABASE   MOUNT…
   
   Sun Mar 15 00:57:29 2009
   
   alter database open
   
   ORA-1507 signalled during: alter database open…
   
   [oracle@localhost /]$
   
   At the moment the database doesn’t open. To solve this problem you need to copy survived control file to /conrol_file_folder directory
   
   In order to do it, we follow the following steps:
   
   [oracle@localhost /]$ cp /home/oracle/product/10.1.0/oradata/qafqaz/control01.ctl /control_file_folder/control02.ctl
   
   [oracle@localhost /]$ sqlplus “/ as sysdba”
   
   SQL*Plus: Release 10.1.0.3.0 – Production on Sun Mar 15 01:05:25 2009
   
   Copyright (c) 1982, 2004, Oracle.  All rights reserved.
   
   Connected to:
   
   Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 – Production
   
   With the Partitioning, OLAP and Data Mining options
   
   SQL> shutdown immediate
   
   ORA-01507: database not mounted
   
   ORACLE instance shut down.
   
   SQL> startup
   
   ORACLE instance started.
   
   Total System Global Area  167772160 bytes
   
   Fixed Size                   778212 bytes
   
   Variable Size              61874204 bytes
   
   Database Buffers          104857600 bytes
   
   Redo Buffers                 262144 bytes
   
   Database mounted.
   
   Database opened.
   
   SQL> select status from v$instance;
   
   STATUS
   ————
   OPEN
   
   SQL>
   
   From these steps it can be seen that as our control files have already been multiplexed, so you can start you database without error even though hard drives where control files located are corrupted
   
   But sometimes there might be some cases when you lose all your control files because of single fault. In that case you should crest control files using sql command

Project 2

Lets create “creation script” of Control File and then delete all Control files. After observing database corruption, we create new Control file by using “creation script” of Control file and open database  

For this, we follow the following steps

1. Initially, we get “creation script” of Control file. To get this script, run the following command:
   
   SQL> alter database backup controlfile to trace as ‘/tmp/trace.txt’;
   
   Database altered.
   
   SQL> host
   
    [oracle@localhost /]$ ls -lh /tmp/trace.txt
   
   -rw-r–r–  1 oracle oinstall 5.8K Mar 19 09:38 /tmp/trace.txt
   
   [oracle@localhost /]$
   
2. Then find destination where Control files are stored and delete them all
   
   SQL> select name from v$controlfile;
   
   NAME
   ——————————————————————————–
   /home/oracle/OraHome_1/oradata/qafqaz/control01.ctl
   /home/oracle/OraHome_1/oradata/qafqaz/control02.ctl
   /home/oracle/OraHome_1/oradata/qafqaz/control03.ctl
   
   SQL> shutdown immediate
   
   Database closed.
   
   Database dismounted.
   
   ORACLE instance shut down.
   
   SQL> host
   
   [oracle@localhost /]$ ls -lh /home/oracle/OraHome_1/oradata/qafqaz/*.ctl
   
   -rw-r—–  1 oracle oinstall 2.8M Mar 19 09:41 /home/oracle/OraHome_1/oradata/qafqaz/control01.ctl
   
   -rw-r—–  1 oracle oinstall 2.8M Mar 19 09:41 /home/oracle/OraHome_1/oradata/qafqaz/control02.ctl
   
   -rw-r—–  1 oracle oinstall 2.8M Mar 19 09:41 /home/oracle/OraHome_1/oradata/qafqaz/control03.ctl
   
   [oracle@localhost /]$ rm -rf  /home/oracle/OraHome_1/oradata/qafqaz/*.ctl
   
   [oracle@localhost /]$ exit
   
   exit
   
   SQL> startup
   
   ORACLE instance started.
   
   Total System Global Area 1610612736 bytes
   
   
   Fixed Size                   779580 bytes
   Variable Size             397679300 bytes
   Database Buffers         1207959552 bytes
   Redo Buffers                4194304 bytes
   
   ORA-00205: error in identifying controlfile, check alert log for more info
   
   SQL> host
   
   [oracle@localhost /]$ tail -10 /home/oracle/OraHome_1/admin/qafqaz/bdump/alert_qafqaz.log
   
   ALTER DATABASE   MOUNT
   
   Thu Mar 19 09:47:28 2009
   
   ORA-00202: controlfile: ‘/home/oracle/OraHome_1/oradata/qafqaz/control01.ctl’
   
   ORA-27037: unable to obtain file status
   
   Linux Error: 2: No such file or directory
   
   Additional information: 3
   
   Thu Mar 19 09:47:28 2009
   
   Controlfile identified with block size 0
   
   Thu Mar 19 09:47:28 2009
   
   ORA-205 signalled during: ALTER DATABASE   MOUNT…
   
   [oracle@localhost /]$
   
   As you have already seen from the above mentioned steps, when Control files are deleted, database doesn’t open. Therefore , we can run “creation script” of Control file to create new Control file.  For this, we open this file with any editor, then copy CREATE CONTROLFILE command and run it in Sql*Plus to create Control file. We can see it from below steps
   
   SQL> shutdown immediate
   
   ORA-01507: database not mounted
   ORACLE instance shut down.
   
   SQL> STARTUP NOMOUNT
   
   CREATE CONTROLFILE REUSE DATABASE “QAFQAZ” NORESETLOGS  NOARCHIVELOG
   
       MAXLOGFILES 16
       MAXLOGMEMBERS 3
       MAXDATAFILES 100
       MAXINSTANCES 8
       MAXLOGHISTORY 454
   
   LOGFILE
   
     GROUP 1 ‘/home/oracle/OraHome_1/oradata/qafqaz/redo01.log’  SIZE 10M,
     GROUP 2 ‘/home/oracle/OraHome_1/oradata/qafqaz/redo02.log’  SIZE 10M,
     GROUP 3 ‘/home/oracle/OraHome_1/oradata/qafqaz/redo03.log’  SIZE 10M
   
   – STANDBY LOGFILE
   
   DATAFILE
   
     ‘/home/oracle/OraHome_1/oradata/qafqaz/system01.dbf’,
     ‘/home/oracle/OraHome_1/oradata/qafqaz/undotbs01.dbf’,
     ‘/home/oracle/OraHome_1/oradata/qafqaz/sysaux01.dbf’,
     ‘/home/oracle/OraHome_1/oradata/qafqaz/users01.dbf’
   
   CHARACTER SET WE8ISO8859P1;
   
   ORACLE instance started.
   
   Total System Global Area 1610612736 bytes
   Fixed Size                   779580 bytes
   Variable Size             397679300 bytes
   Database Buffers         1207959552 bytes
   Redo Buffers                4194304 bytes
   
   SQL>  
   
   Control file created.
   
   SQL> shutdown immediate
   
   ORA-01109: database not open
   
   Database dismounted.
   
   ORACLE instance shut down.
   
   SQL> startup
   
   ORACLE instance started.
   
   Total System Global Area 1610612736 bytes
   Fixed Size                   779580 bytes
   Variable Size             397679300 bytes
   Database Buffers         1207959552 bytes
   Redo Buffers                4194304 bytes
   
   Database mounted.
   
   Database opened.
   
   SQL> select name from v$controlfile;
   
   NAME
   ——————————————————————————–
   
   /home/oracle/OraHome_1/oradata/qafqaz/control01.ctl
   /home/oracle/OraHome_1/oradata/qafqaz/control02.ctl
   /home/oracle/OraHome_1/oradata/qafqaz/control03.ctl
   
   SQL> select status from v$instance;
   
   STATUS
   ————
   OPEN
   
   In above mentioned example, as we’ve got creation script of Control file, even all Control files are lost, however we can create new Control files using this script and open database successfully.

Project 3

Now, we are going to learn how to handle another troubleshooting. In the first place, please note that its one of strict recommendations of Oracle to run the database in archive log mode. It’s not possible to recover database running in NOARCHIVELOG mode. The scenario of this project is as follows:

Let us assume that we have a database running in ARCHIVELOG mode. We backup Control File of this database as a binary mode. After making some changes on the database and creating archived redo log files, instantly we lose all control files. As the binary copy of control file was taken, by recovering database using archived redo logs, we can open database using binary copy of Control File  

SQL> host

[oracle@localhost root]$ cd /home/oracle/OraHome_1/oradata/qafqaz/

[oracle@localhost qafqaz]$ ls

arch           control02.ctl  redo01.log  redo03.log    system01.dbf  undotbs01.dbf

control01.ctl  control03.ctl  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

[oracle@localhost qafqaz]$ rm -rf arch/

[oracle@localhost qafqaz]$ mkdir arch

[oracle@localhost qafqaz]$ pwd

/home/oracle/OraHome_1/oradata/qafqaz

[oracle@localhost qafqaz]$ exit

exit

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size                   779580 bytes

Variable Size             431233732 bytes

Database Buffers         1174405120 bytes

Redo Buffers                4194304 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter system set log_archive_dest_1=’location=/home/oracle/OraHome_1/oradata/qafqaz/arch’ scope=both;

System altered.

SQL> alter database open;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE

————

ARCHIVELOG

SQL> alter system switch logfile;

System altered.

SQL> select name from v$archived_log;

NAME

——————————————————————————–

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_6_681908323.dbf

SQL> alter system switch logfile;

System altered.

SQL> select name from v$archived_log;

NAME

——————————————————————————–

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_6_681908323.dbf

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_7_681908323.dbf

SQL> alter database backup controlfile to ‘/tmp/control01.ctl’;

Database altered.

SQL> select name from v$controlfile;

NAME

——————————————————————————–

/home/oracle/OraHome_1/oradata/qafqaz/control01.ctl

/home/oracle/OraHome_1/oradata/qafqaz/control02.ctl

/home/oracle/OraHome_1/oradata/qafqaz/control03.ctl

SQL> create table t1 (id number);

Table created.

SQL> begin

  2  for i in 1..1000 loop

  3  insert into t1 values(i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(1) from t1;

  COUNT(1)

———-

      1000

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> begin

  2  for i in 1..1000 loop

  3  insert into t1 values(i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> select count(1) from t1;

  COUNT(1)

———-

      2000

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host

[oracle@localhost root]$ rm -rf /home/oracle/OraHome_1/oradata/qafqaz/*.ctl

[oracle@localhost root]$ cp /tmp/control01.ctl /home/oracle/OraHome_1/oradata/qafqaz/control01.ctl

[oracle@localhost root]$ cp /tmp/control01.ctl /home/oracle/OraHome_1/oradata/qafqaz/control02.ctl

[oracle@localhost root]$ cp /tmp/control01.ctl /home/oracle/OraHome_1/oradata/qafqaz/control03.ctl

[oracle@localhost root]$ exit

exit

SQL> startup

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size                   779580 bytes

Variable Size             414456516 bytes

Database Buffers         1191182336 bytes

Redo Buffers                4194304 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: ‘/home/oracle/OraHome_1/oradata/qafqaz/system01.dbf’

SQL> recover database using backup controlfile;

ORA-00279: change 571494 generated at 03/19/2009 11:48:59 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_8_681908323.dbf

ORA-00280: change 571494 for thread 1 is in sequence #8

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

ORA-00279: change 572763 generated at 03/19/2009 11:54:23 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_9_681908323.dbf

ORA-00280: change 572763 for thread 1 is in sequence #9

ORA-00278: log file

‘/home/oracle/OraHome_1/oradata/qafqaz/arch/1_8_681908323.dbf’ no longer needed

for this recovery

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

ORA-00279: change 572768 generated at 03/19/2009 11:54:35 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_10_681908323.dbf

ORA-00280: change 572768 for thread 1 is in sequence #10

ORA-00278: log file

‘/home/oracle/OraHome_1/oradata/qafqaz/arch/1_9_681908323.dbf’ no longer needed

for this recovery

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

ORA-00308: cannot open archived log

‘/home/oracle/OraHome_1/oradata/qafqaz/arch/1_10_681908323.dbf’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> host

[oracle@localhost root]$ ls /home/oracle/OraHome_1/oradata/qafqaz/arch/

1_6_681908323.dbf  1_7_681908323.dbf  1_8_681908323.dbf  1_9_681908323.dbf

[oracle@localhost root]$ exit

exit

SQL> recover database using backup controlfile

ORA-00279: change 572768 generated at 03/19/2009 11:54:35 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_10_681908323.dbf

ORA-00280: change 572768 for thread 1 is in sequence #10

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

/home/oracle/OraHome_1/oradata/qafqaz/redo01.log

ORA-00310: archived log contains sequence 8; sequence 10 required

ORA-00334: archived log: ‘/home/oracle/OraHome_1/oradata/qafqaz/redo01.log’

SQL> recover database using backup controlfile

ORA-00279: change 572768 generated at 03/19/2009 11:54:35 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_10_681908323.dbf

ORA-00280: change 572768 for thread 1 is in sequence #10

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

/home/oracle/OraHome_1/oradata/qafqaz/redo02.log

ORA-00310: archived log contains sequence 9; sequence 10 required

ORA-00334: archived log: ‘/home/oracle/OraHome_1/oradata/qafqaz/redo02.log’

SQL> recover database using backup controlfile

ORA-00279: change 572768 generated at 03/19/2009 11:54:35 needed for thread 1

ORA-00289: suggestion :

/home/oracle/OraHome_1/oradata/qafqaz/arch/1_10_681908323.dbf

ORA-00280: change 572768 for thread 1 is in sequence #10

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

/home/oracle/OraHome_1/oradata/qafqaz/redo03.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(1) from t1;

  COUNT(1)

———-

      2000

SQL>

In the previous project we enabled archived log mode, took binary copy of Control File, made some changes on database, generated archived redo logs and by using these files recovered the database and started it in spite of loss of all Control files.

The reason for applying archived redo log files is that the control file change sequence number in the data file is greater than the number in the control file. This implies that the wrong control file is being used. That’s why, we used archived redo logs in our recovery process.