Vəziyyət:
- Bütün kontrol fayllar silinmişdir
- Kontrol faylımızın yaranma scripti var
- Tablespce-lərdən biri read only-dir
.
İlk olaraq bir tablespace yaradırıq , only read only edirik, kontrol faylın backup-nı alırıq və bütün kontrol faylları silirik:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> create tablespace tbs_test datafile '/u01/app/oracle/oradata/DB11G/test_tbs.dbf' size 5m ;
Tablespace created.
SQL> alter tablespace tbs_test read only;
Tablespace altered.
SQL> alter database backup controlfile to trace as '/home/oracle/backups/ctl_backup.dat';
Database altered.
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 inf
[/pcsh]
ctl_backup.dat faylından istifadə edərək kontrol faylı yaradırıq və DB-nı işə salmağa cəh edirik:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/DB11G/system01.dbf',
'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',
'/u01/app/oracle/oradata/DB11G/users2_01.dbf',
'/home/oracle/backups/fortest/tbs1.dbf',
'/home/oracle/backups/fortest/tbs2.dbf',
'/home/oracle/backups/fortest/users02.dbf',
'/home/oracle/backups/fortest/users03.dbf',
'/u01/app/oracle/product/11.2/db_1/dbs/undotbs13.dbf'
CHARACTER SET AL32UTF8; 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Control file created.
[/pcsh]
Və biz görürük ki kontrol faylın yaranma skriptində read only tablespace barədə məlumat yoxdur.
[pcsh lang=”applescript” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf'
SQL>
[/pcsh]
Data faylların adını sorğuladıqda görürük ki tbs_test readonly tablespacesinin datafaylları barədə məlumat yoxdur:
[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
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::
8 rows selected.
[/pcsh]
DB-nı recover etməyə cəhd edirik və redo məlumatları əlavə etmək üçün redo log-ları təqdim edirik:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> recover database using backup controlfile;
ORA-00279: change 13911238 generated at 05/09/2015 10:57:10 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13911238 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.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 4: '/u01/app/oracle/oradata/DB11G/test_tbs.dbf'
ORA-01112: media recovery not started
[/pcsh]
Recovery başlamır, çünki oracle itmiş read only datafaylı UNNAMED00004 kimi qəbul etdiyindən redolog-lar əlavə oluna bilmir. Ona görədə biz UNNAMED00004 faylının adını onun orijinal adına dəyişirik
[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
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004
::::::::::::::output trimmed::::::::::::::
9 rows selected.
SQL> alter database rename file '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004' to '/u01/app/oracle/oradata/DB11G/test_tbs.dbf';
Database altered.
[/pcsh]
İndi recovery davam edə bilər , çünki datafayl öz düzgün adına rename olundu. DB-ni recover edib bazanı açıqırıq:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> recover database using backup controlfile;
ORA-00279: change 13916733 generated at 05/09/2015 12:55:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13916733 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.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
TBS_TEST /u01/app/oracle/oradata/DB11G/test_tbs.dbf
::::::::::::::output trimmed::::::::::::::
9 rows selected.
[/pcsh]Situation:
- All control files are lost
- You have a creation script of the control file
- One of the tablespaces is read only
.
First, create tablespace and make it read only, then take backup of the control file and delete all available control files.
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> create tablespace tbs_test datafile '/u01/app/oracle/oradata/DB11G/test_tbs.dbf' size 5m ;
Tablespace created.
SQL> alter tablespace tbs_test read only;
Tablespace altered.
SQL> alter database backup controlfile to trace as '/home/oracle/backups/ctl_backup.dat';
Database altered.
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 inf
[/pcsh]
Create control file using ctl_backup.dat file and try opening the database:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/DB11G/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/DB11G/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/DB11G/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/DB11G/system01.dbf',
'/u01/app/oracle/oradata/DB11G/sysaux01.dbf',
'/u01/app/oracle/oradata/DB11G/users2_01.dbf',
'/home/oracle/backups/fortest/tbs1.dbf',
'/home/oracle/backups/fortest/tbs2.dbf',
'/home/oracle/backups/fortest/users02.dbf',
'/home/oracle/backups/fortest/users03.dbf',
'/u01/app/oracle/product/11.2/db_1/dbs/undotbs13.dbf'
CHARACTER SET AL32UTF8; 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Control file created.
[/pcsh]
And we saw that there is not information about readonly datafile on this script.
[pcsh lang=”applescript” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> startup force;
ORACLE instance started.
::::::::::::::output trimmed::::::::::::::
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf'
SQL>
[/pcsh]
Select the name of datafiles and you say it is no information about tbs_test readonly datafile:
[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
/u01/app/oracle/oradata/DB11G/users2_01.dbf
::::::::::::::output trimmed::::::::::::::
8 rows selected.
[/pcsh]
Try to recover the database and provide the redo log file to applying redo entries:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> recover database using backup controlfile;
ORA-00279: change 13911238 generated at 05/09/2015 10:57:10 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13911238 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.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 4: '/u01/app/oracle/oradata/DB11G/test_tbs.dbf'
ORA-01112: media recovery not started
[/pcsh]
Recovery hasn’t started because it applied information about missing read only datafile which is missing since Oracle accept it as UNNAMED00004.
Select name of datafiles, now you can see UNNAMED00004 file, which is the our missing read only datafile and rename the datafile:
[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
/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004
::::::::::::::output trimmed::::::::::::::
9 rows selected.
SQL> alter database rename file '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00004' to '/u01/app/oracle/oradata/DB11G/test_tbs.dbf';
Database altered.
[/pcsh]
Now recovery will continue because the datafile has been renamed to its correct name. Recover database and open it:
[pcsh lang=”sql” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]
SQL> recover database using backup controlfile;
ORA-00279: change 13916733 generated at 05/09/2015 12:55:13 needed for thread 1
ORA-00289: suggestion :
/home/oracle/backups/DB11G/archivelog/2015_05_09/o1_mf_1_2_%u_.arc
ORA-00280: change 13916733 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/DB11G/redo02.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
TBS_TEST /u01/app/oracle/oradata/DB11G/test_tbs.dbf
::::::::::::::output trimmed::::::::::::::
9 rows selected.
[/pcsh]
Valeh Agayev's blog 
You must be logged in to post a comment.