My Blog

October 15, 2008

Restore database with damaging data files,control files and redo logs

Filed under: Oracle — by enotebook @ 12:06 pm

Think about a scenario – One day your database crashed, all the data files, controls files and redo logs(in case you didn’t multiplex them anyway) are damaged, fortunately you do have all the archived logs on hand and with a backup set of data files that you even don’t know when they have been backed-up, is your database able to be restored? This post does the test and gives you a glance on restoration process of oracle database.

SQL> startup
ORACLE instance started.
Total System Global Area 247463936 bytes
Fixed Size 1218772 bytes
Variable Size 79693612 bytes
Database Buffers 163577856 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.

Ensure database is running on archive mode.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> select name from v$tablespace;
NAME
——————————
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
SQL> select name from v$datafile;
NAME
——————————————————————————–
/u01/oradata/orcl/system01.dbf
/u01/oradata/orcl/undotbs01.dbf
/u01/oradata/orcl/sysaux01.dbf
/u01/oradata/orcl/users01.dbf

Have the data files to be backed-up.
SQL> alter tablespace system begin backup;
Tablespace altered.
SQL> alter tablespace undotbs1 begin backup;
Tablespace altered.
SQL> alter tablespace sysaux begin backup;
Tablespace altered.
SQL> alter tablespace users begin backup;
Tablespace altered.
SQL> host
[oracle@LinuxOra oradata]$ cd /u01/oradata/
[oracle@LinuxOra oradata]$ ls
orcl
[oracle@LinuxOra oradata]$ mkdir orclbak
[oracle@LinuxOra oradata]$ cp orcl/* orclbak/
[oracle@LinuxOra oradata]$ exit
exit
SQL> alter tablespace system end backup;
Tablespace altered.
SQL> alter tablespace undotbs1 end backup;
Tablespace altered.
SQL> alter tablespace sysaux end backup;
Tablespace altered.
SQL> alter tablespace users end backup;
Tablespace altered.

Archive the logs
SQL> alter system switch logfile;
System altered.

Database is running and has being accepted lots of user’s transaction. Here we create a tablespace and a table.
SQL> create tablespace test datafile ‘/u01/oradata/orcl/test.dbf’ size 10M;
Tablespace created.
SQL> create table t tablespace test as select * from v$datafile;
Table created.
SQL> select count(*) from t;
COUNT(*)
———-
5

SQL> alter system switch logfile;
System altered.

It’s a good idea to always backup the control file to trace after any changes applied to database structure.
SQL> alter database backup controlfile to trace;
Database altered.

Find out the trace file and see its content, you might need to use it to re-create control file.
SQL> select spid from v$process where addr in (select s.paddr from v$session s, v$mystat m where s.sid = m.sid and rownum < 2);
SPID
————
24697
SQL> shutdown immediate
Database closed.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
[oracle@LinuxOra oradata]$ cd /u01/app/oracle/admin/orcl/udump/
$ ls |grep *24697*
orcl_ora_24697.trc
$ pwd
/u01/app/oracle/admin/orcl/udump
$ more 100 orcl_ora_24697.trc

Control file re-creation scripts
$ cd /u01/oradata
[oracle@LinuxOra oradata]$ touch ctl.sql
[oracle@LinuxOra oradata]$ vi ctl.sql
CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/oradata/orcl/redo01.log’ SIZE 50M,
GROUP 2 ‘/u01/oradata/orcl/redo02.log’ SIZE 50M,
GROUP 3 ‘/u01/oradata/orcl/redo03.log’ SIZE 50M
— STANDBY LOGFILE
DATAFILE
‘/u01/oradata/orcl/system01.dbf’,
‘/u01/oradata/orcl/undotbs01.dbf’,
‘/u01/oradata/orcl/sysaux01.dbf’,
‘/u01/oradata/orcl/users01.dbf’
CHARACTER SET WE8ISO8859P1
;

Simulate damaging data files and control files (move them)
[oracle@LinuxOra oradata]$ ls
ctl.sql orcl orclbak

Lost data files.
[oracle@LinuxOra oradata]$ mv orcl orcl_latest

Restore backed-up data files to specified location.
[oracle@LinuxOra oradata]$ mv orclbak/ orcl
[oracle@LinuxOra oradata]$ ls
ctl.sql orcl orcl_latest
[oracle@LinuxOra oradata]$
[oracle@LinuxOra oradata]$ cd orcl
$ ls
control01.ctl control02.ctl control03.ctl redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

Damage control files.
$ mv control01.ctl control01.ctl.latest
$ mv control02.ctl control02.ctl.latest
$ mv control03.ctl control03.ctl.latest
$ ls
control03.ctl.latest redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl.latest control01.ctl.latest redo02.log sysaux01.dbf temp01.dbf users01.dbf
$
$ exit
exit

Database cannot be mounted since losing of control files. they can be re-create, content in ctl.sql is from the trace file as showed above.
SQL> @/u01/oradata/ctl.sql;
Control file created.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

After re-creating control files we can open database, but it needs recovery.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/u01/oradata/orcl/system01.dbf’

SQL> recover database using backup controlfile until cance;
ORA-00277: illegal option to the UNTIL recovery flag CANCE

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 887850 generated at 10/14/2008 14:59:50 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2008_10_14/o1_mf_1_1_%u_.arc
ORA-00280: change 887850 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 887946 generated at 10/14/2008 15:02:30 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2008_10_14/o1_mf_1_2_%u_.arc
ORA-00280: change 887946 for thread 1 is in sequence #2
ORA-00278: log file
‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2008_10_14/o1_mf_1_1_4h9v67
29_.arc’ no longer needed for this recovery

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 5: ‘/u01/oradata/orcl/test.dbf’

ORA-01112: media recovery not started

ORA-01111: name for data file 5 is unknown – rename to correct file
ORA-01110: data file 5: ‘/u01/app/oracle/product/10g/db/dbs/UNNAMED00005’
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01111: name for data file 5 is unknown – rename to correct file
ORA-01110: data file 5: ‘/u01/app/oracle/product/10g/db/dbs/UNNAMED00005’

ORA-01112: media recovery not started

After applying all the archived redo logs, we can see test tablespace has been restored, but the file name is incorrect since re-creatation of control file, for continue to restore data
base, oracle writes a UNAMED style filename to describe the data file in control file, rename it to correct file as error message showed.
SQL> alter database create datafile ‘/u01/app/oracle/product/10g/db/dbs/UNNAMED00005’ as ‘/u01/oradata/orcl/test.dbf’ reuse;
Database altered.

Open database with using resetlogs option.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from t
2 ;
COUNT(*)
———-
5

Done.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: