Kamran Agayev’s Oracle Blog

Kamran Agayev’s Oracle Blog

Step by Step multiplexing Control File of Oracle Database by adding new hard drive to Linux

Posted by Kamran Agayev A. on March 23, 2009

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. In order to do it, shutdown VMware and by entering “Edit Virtual machine Settings” window click on “Add”button as shown below

control_file11

Here, select “Hard Disk” and click on Next

control_file2

Here, leave the option to be as default and Click on “Next”

control_file3

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

control_file4

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

control_file5

Here, specify destination where you want to save information of new hard disk and click on “Finish” button. Now, you can see new hard disk being created with 100Mb size

control_file6

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 0x0000 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. For this, we need to follow the following steps:

SQL> CREATE PFILE=’/tmp/pfile.ora’ FROM SPFILE;

File created.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 – Production

With the Partitioning, OLAP and Data Mining options

[oracle@localhost /]$ vi /tmp/pfile.ora

Here, change CONTROL_FILES parameter and indicate the second control file on the newly created hard disk, which mounted to /control_file_folder

*.control_files=’/home/oracle/product/10.1.0/oradata/qafqaz/control01.ctl’,’/control_file_folder/control02.ctl’

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host

[oracle@localhost /]$ su root

Password:

[root@localhost /]# chown -R oracle:oinstall /control_file_folder/

[root@localhost /]# exit

exit

[oracle@localhost /]$ cp /home/oracle/product/10.1.0/oradata/qafqaz/control01.ctl /control_file_folder/control02.ctl

[oracle@localhost /]$ cd /control_file_folder/

[oracle@localhost control_file_folder]$ ls -lh

total 2.8M

-rw-r—–  1 oracle oinstall 2.8M Mar 14 23:56 control02.ctl

[oracle@localhost control_file_folder]$

[oracle@localhost control_file_folder]$ sqlplus “/as sysdba”

SQL*Plus: Release 10.1.0.3.0 – Production on Sat Mar 14 23:57:15 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile=’/tmp/pfile.ora’;

File created.

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> show parameter control_files

NAME                TYPE        VALUE

——————- ———- ———-

control_files       string      /home/oracle/product/10.1.0/oradata/qafqaz/control01.ctl, /control_file_folder/control02.ctl

SQL>

4. Now let’s remove that hard disk and see what is going to happen to database

Before removing hard disk, shutdown OS

control_file7

Select new hard disk on the main page and click on “Remove” button to remove that hard disk

control_file8

Then start the system. Open new terminal and type “df –h” to verify that hard disk was not mounted

control_file9

As you can see, 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

Advertisements

6 Responses to “Step by Step multiplexing Control File of Oracle Database by adding new hard drive to Linux”

  1. Aijaz Khan said

    Woowwww………… Great It is a very nice tutorial for Beginners who are in the field of dba. Thanks alot and plz keep on posting other blogs related to dba topic.

  2. Thanks Aijaz for your comment. I’m currently working on Video Tutorials and hope it will be more helpful

    You can find my video tutorials from the following link:
    https://kamranagayev.wordpress.com/category/video-tutorials/

  3. abdul wahab said

    Very very good tutorial Kamran Sir. I like this and I have implemented this one in my PC. Your posts and video tutorials are awesome. I follow these and implement.Your posts are really very helpful for all Oracle DBAs. Sir, please keep posting these types of tutorials.

    Thanks and Regards,

    Abdul Wahab
    Kolkata,India.

  4. Thank you for feedback Abdul Wahab

  5. sharravanan said

    Hi kamranagayev,

    I like to express my deep sense of gratitude for your valuable information related to control files multiplexing.
    Especially project 1- 3 are awesome for beginner to learn about control files .Once again thank u Post some thing which is useful for beginner.

  6. Thank you for feedback Sharravanan

    Have you checked my video tutorials?
    https://kamranagayev.wordpress.com/oracle-video-tutorials/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: