26 August, 2010

Adding a DataFile that had been excluded from a CREATE CONTROLFILE

Here is a scenario where a CREATE CONTROLFILE was used to recover a database but one of the datafiles was (inadvertently) excluded from the CREATE CONTROLFILE statement.
The Database when, OPENed, sets the file as a MISSING file. This is because it is present in the Data Dictionary but not in the Controlfile.
The datafile can be "added" back (if it is really available on disk or has been restored earlier) and "re-recovered".



SQL> REM ########### The controlfile was created without the "example01.dbf" datafile
SQL> REM #### An INCOMPLETE RECOVERy was performed
SQL> REM #### The database was OPENed with RESETLOGS
SQL> REM ## The datafile has been added back with an ALTER DATABASE RENAME FILE 'MISSING05.DBF' TO 'example01.dbf'

SQL> @create_cntrlfile
SQL> spool create_cntrlfile
SQL>
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 880803840 bytes
Fixed Size 2087992 bytes
Variable Size 218104776 bytes
Database Buffers 654311424 bytes
Redo Buffers 6299648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORT24FS" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/oracle_fs/Databases/ORT24FS/redo01.dbf' SIZE 50M,
9 GROUP 2 '/oracle_fs/Databases/ORT24FS/redo02.dbf' SIZE 50M,
10 GROUP 3 '/oracle_fs/Databases/ORT24FS/redo03.dbf' SIZE 50M,
11 GROUP 4 '/oracle_fs/Databases/ORT24FS/redo04.dbf' SIZE 50M,
12 GROUP 5 '/oracle_fs/Databases/ORT24FS/redo05.dbf' SIZE 50M
13 -- STANDBY LOGFILE
14 DATAFILE
15 '/oracle_fs/Databases/ORT24FS/system01.dbf',
16 '/oracle_fs/Databases/ORT24FS/sysaux01.dbf',
17 '/oracle_fs/Databases/ORT24FS/users01.dbf',
18 '/oracle_fs/Databases/ORT24FS/undotbs.dbf'
19 CHARACTER SET WE8ISO8859P1
20 ;

Control file created.

SQL> -- Configure RMAN configuration record 1
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','OFF');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 2
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 3
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 8 DAYS');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 4
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET');

PL/SQL procedure successfully completed.

SQL> -- Commands to re-create incarnation table
SQL> -- Below log names MUST be changed to existing filenames on
SQL> -- disk. Any one log file from each branch can be used to
SQL> -- re-create incarnation records.
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- Recovery is required if any of the datafiles are restored backups,
SQL> -- or if the last shutdown was not normal or immediate.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 4325510 generated at 08/25/2010 16:46:41 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_43_%u_.arc
ORA-00280: change 4325510 for thread 1 is in sequence #43


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/ArchiveLogs/ORT24FS/1_43_725069822.dbf
ORA-00279: change 4325717 generated at 08/25/2010 16:54:02 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_44_%u_.arc
ORA-00280: change 4325717 for thread 1 is in sequence #44
ORA-00278: log file '/oracle_fs/ArchiveLogs/ORT24FS/1_43_725069822.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle_fs/Databases/ORT24FS/system01.dbf'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4325717 generated at 08/25/2010 16:54:02 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_44_%u_.arc
ORA-00280: change 4325717 for thread 1 is in sequence #44


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_fs/Databases/ORT24FS/temp01.dbf' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_fs/Databases/ORT24FS/temp01.dbf' REUSE
*
ERROR at line 1:
ORA-01119: error in creating database file
'/oracle_fs/Databases/ORT24FS/temp01.dbf'
ORA-17610: file '/oracle_fs/Databases/ORT24FS/temp01.dbf' does not exist and no
size specified
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_fs/Databases/ORT24FS/temp01.dbf' size 100M autoextend on next 100M maxsize 8000M;

Tablespace altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/oracle_fs/Databases/ORT24FS/undotbs.dbf
/oracle_fs/Databases/ORT24FS/users01.dbf
/oracle_fs/Databases/ORT24FS/sysaux01.dbf
/oracle_fs/Databases/ORT24FS/system01.dbf
/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005

SQL> alter database create datafile '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'
2 as '/oracle_fs/Databases/ORT24FS/example01.dbf';
alter database create datafile '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'
*
ERROR at line 1:
ORA-19723: Cannot recreate plugged in read-only datafile 5
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'


SQL> alter database rename file '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'
2 to '/oracle_fs/Databases/ORT24FS/example01.dbf';

Database altered.

SQL> recover datafile 5;
ORA-00279: change 4325511 generated at 08/25/2010 16:46:41 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_43_%u_.arc
ORA-00280: change 4325511 for thread 1 is in sequence #43


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/ArchiveLogs/ORT24FS/1_43_725069822.dbf
Log applied.
Media recovery complete.
SQL> select * from v$recover_file;

no rows selected

SQL> alter database datafile 5 online;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
1 4034914 23-JUL-10 3632263 04-JUN-10
PARENT 725069822 0 NO

2 4325718 25-AUG-10 4034914 23-JUL-10
CURRENT 727983988 1 NO


SQL> exit


The example01.dbf file was not included in the CREATE CONTROLFILE statement although it was present on disk. The OPEN RESETLOGS after the Incomplete Recovery created a new Incarnation of the database. Yet, I was able to "add" the datafile back by RENAMEing the "MISSING" file.

.
.
.

7 comments:

Emre Baransel said...

very interesting. Thanks Hemant..

RAJESHKUMAR GOVINDARAJAN said...

SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');


after that

i have seen in RMAN> SHOW ALL;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 09/02/2010 12:45:17
RMAN-20021: database not set
RMAN-06031: could not translate database keyword

exit from rman and relogin in rman

and see the show all output

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:

CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

this time no errors

WHY?

i guess to set rman configuration from sql prompt we using the execute command? is it correct?

looking forward to your reply

Rajesh

Hemant K Chitale said...

Rajesh,
"SYS.DBMS_BACKUP_RESTORE.SETCONFIG"
is a PLSQL procedure. *Any* PLSQL procedure executed from SQLPlus must either be in a "BEGIN .. END" block OR be executed with the "EXECUTE" command.

Since a CREATE CONTROLFILE script is executed from SQLPlus, it is generated by Oracle to include the "EXECUTE" for the PLSQL calls. Also, all the calls to register archivelogs and configure rman parameters, being available, in the DBMS_BACKUP_RESTORE package are executed from SQLPlus (since the CREATE CONTROLFILE script has just created the controlfile it is convenient to include these calls so that the controlfile and repository are setup as it was at the time the script was generated.


Hemant

Guti said...

Hi

Lets say a corrupted block is detected in a datafile.
The solution is to
take the tablespace offline,
restore datafile
recover tablespace and make tablespace online.


Is there an alternative way to do this task, without taking the entire tablespace offline? I want to give as less outage as I can.

Hemant K Chitale said...

Guti,

You don't have to take the Tablespace or even the Datafile Offline to recover individual blocks. In fact, since 9i, Block Media Recovery, when using RMAN, has been available. Oracle Support Note#144911.1 first provided an example in 9i.
The RMAN command is "BLOCKRECOVER".
See http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta010.htm#sthref210 in 10.2
or http://download.oracle.com/docs/cd/E11882_01/backup.112/e10643/rcmsynta2001.htm#i84135 in 11.2

Even if you are restoring and recovering a whole datafile, you only need to take the DataFile offline, not the whole Tablespace.

Guti said...

Thanks

So, no outage required to perform the blockrecover option in Rman, right ?
Blockrecover command should restore the corrupted block and apply archivelogs. Is that right ?

Hemant K Chitale said...

Guti,
The BLOCKRECOVER command doesn't require taking the datafile offline.

Hemant K Chitale