Vəziyyət:
- Bütün kontrol faylar silinmişdir
- Kontrol faylın backupını aldıqdan sonra yeni yaradılmış data file silinmişdir
- Kontrol faylın binary backup-ı vardır
.
Bu vəziyyəti test etmək üçün ilk öncə kontrol faylın binary backup-nı alırıq və daha sonra bütün kontrol faylları silirik:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> alter database backup controlfile to '/home/oracle/backups/control_bckp.ctl';
Database altered.
Create new tablespace
SQL> create tablespace tbs_test2 datafile '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf' size 5M;
Tablespace created.
SQL> !rm /home/oracle/backups/controlfiles/*.ctl
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info
[/pcsh]
DB-nı mount etmək üçün kontrol faylın backupını silinmiş faylların yerınə kopyalayırıq və adını dəyişirik. Sonra isə kontrol faylın backupından istifadə edərək recovery etməyə cəhd edirik:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl
[oracle@orcl controlfiles]$ ls /home/oracle/backups/controlfiles/
control01.ctl control02.ctl control03.ctl
SQL> alter database mount;
Database altered.
SQL> recover database using backup controlfile;
::::::::::::::output trimmed::::::::::::::
ORA-00280: change 13917957 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
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/app/oracle/oradata/DB11G/tbs_test2.dbf'
ORA-01112: media recovery not started[/pcsh]
ORA-01244 errorundan biz aydın olurkı bizim adı təyin olunmamış datafaylımız var. İndi biz kontrol faylın backupından sonra yaradılmış yeni datafaylın adını tapmalı və onun əsasında datafaylı yaratmalıyıq:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005
10 rows selected.
SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf';
Database altered.[/pcsh]
Yeniden recover etməyə cəhd edirik və open edirik:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> recover database using backup controlfile;
ORA-00279: change 13929485 generated at 05/10/2015 11:12:15 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_10/o1_mf_1_1_%u_.arc
ORA-00280: change 13929485 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select tbs.name,dt.name from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;
NAME NAME
------------------------------ -------------------------------------------------
SYSTEM /u01/app/oracle/oradata/DB11G/system01.dbf
SYSAUX /u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
TBS_TEST2 /u01/app/oracle/oradata/DB11G/tbs_test2.dbf
10 rows selected.
[/pcsh]Situation:
- All control files have been lost
- After creating backup of control file new created datafile lost
- Control file binary backup is available
.
Take binary backup of the control file and remove all control files to simulate lost of all control files:
[pcsh lang=”sql” tab_size=”” message=”” hl_lines=”” provider=”manual”]
SQL> alter database backup controlfile to '/home/oracle/backups/control_bckp.ctl';
Database altered.
Create new tablespace
SQL> create tablespace tbs_test2 datafile '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf' size 5M;
Tablespace created.
SQL> !rm /home/oracle/backups/controlfiles/*.ctl
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
ORA-00205: error in identifying control file, check alert log for more info
--[/pcsh]
To mount Database copy backup of control file to the original control file locations and rename to original control file names.Then recover database using backup control file
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control01.ctl
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control02.ctl
[oracle@orcl controlfiles]$ cp /home/oracle/backups/control_bckp.ctl /home/oracle/backups/controlfiles/control03.ctl
[oracle@orcl controlfiles]$ ls /home/oracle/backups/controlfiles/
control01.ctl control02.ctl control03.ctl
SQL> alter database mount;
Database altered.
SQL> recover database using backup controlfile;
::::::::::::::output trimmed::::::::::::::
ORA-00280: change 13917957 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
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/app/oracle/oradata/DB11G/tbs_test2.dbf'
ORA-01112: media recovery not started[/pcsh]
ORA-01244 says that we have unnamed datafile. Now we must find this datafile and creat datafile based on it with name of datafile that were created after taking backup of control file:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/system01.dbf
/u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005
10 rows selected.
SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/DB11G/tbs_test2.dbf';
Database altered.[/pcsh]
Now recover the database again and then open the database:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> recover database using backup controlfile;
ORA-00279: change 13929485 generated at 05/10/2015 11:12:15 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_10/o1_mf_1_1_%u_.arc
ORA-00280: change 13929485 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select tbs.name,dt.name from v$datafile dt,v$tablespace tbs where dt.ts#=tbs.ts#;
NAME NAME
------------------------------ -------------------------------------------------
SYSTEM /u01/app/oracle/oradata/DB11G/system01.dbf
SYSAUX /u01/app/oracle/oradata/DB11G/sysaux01.dbf
::::::::::::::output trimmed::::::::::::::
TBS_TEST2 /u01/app/oracle/oradata/DB11G/tbs_test2.dbf
10 rows selected.
[/pcsh]
Valeh Agayev's blog 
You must be logged in to post a comment.