Aprende en Comunidad
Avalados por :
Solución de recuperación de SID: Pasos para corregir archivos de control y enlaces suaves en Oracle DB
- Creado 01/03/2024
- Modificado 01/03/2024
- 8 Vistas
0
Cargando...
Hi Team,
Recovery of SID went wrong though the DB opened successfully because unfortunately we used old control file and missed almost 11 data files in controlfile.sql file and which created soft link in dbs path.
In order to overcome the situation below steps performed and database become consistent. If in case in the future if we face the same issue, we can use the below steps confidently.
1) Find the missing datafiles:
SQL> set line 250
SQL> col FILE_NAME for a45
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME, ONLINE_STATUS from dba_data_files;
/oracle/SID/121/dbs/MISSING00268 268 PSAPSID ONLINE
/oracle/SID/121/dbs/MISSING00269 269 PSAPSID ONLINE
2) Take tablespace or datafiles offline:
Come with below error because data file already offline
SQL> alter database datafile '/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242' offline;
Database altered.
SQL> alter database datafile '/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243' offline;
Database altered.
3) Rename missing datafiles with original files:
SQL> alter database rename file '/oracle/SID/121/dbs/MISSING00268' to '/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242';
SQL> alter database rename file '/oracle/SID121/dbs/MISSING00269' to '/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243';
4) Shutdown and mount the database:
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 6.4425E+10 bytes
Fixed Size 3729840 bytes
Variable Size 7381976656 bytes
Database Buffers 5.6908E+10 bytes
Redo Buffers 130486272 bytes
Database mounted.
5) Perform media recovery:
SQL> recover datafile 268;
Media recovery complete.
SQL> recover datafile 279;
Media recovery complete.
6) Open the database:
SQL> alter database open;
Database altered.
7) check the status of datafiles:
SQL> set line 250
SQL> col FILE_NAME for a45
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME, ONLINE_STATUS from dba_data_files; - After recovery data files online_status went recover to offline.
😎 Bring the datafiles online:
SQL> alter database datafile '/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242' online;
Database altered.
SQL> alter database datafile '/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243' online;
Recovery of SID went wrong though the DB opened successfully because unfortunately we used old control file and missed almost 11 data files in controlfile.sql file and which created soft link in dbs path.
In order to overcome the situation below steps performed and database become consistent. If in case in the future if we face the same issue, we can use the below steps confidently.
1) Find the missing datafiles:
SQL> set line 250
SQL> col FILE_NAME for a45
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME, ONLINE_STATUS from dba_data_files;
/oracle/SID/121/dbs/MISSING00268 268 PSAPSID ONLINE
/oracle/SID/121/dbs/MISSING00269 269 PSAPSID ONLINE
2) Take tablespace or datafiles offline:
Come with below error because data file already offline
SQL> alter database datafile '/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242' offline;
Database altered.
SQL> alter database datafile '/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243' offline;
Database altered.
3) Rename missing datafiles with original files:
SQL> alter database rename file '/oracle/SID/121/dbs/MISSING00268' to '/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242';
SQL> alter database rename file '/oracle/SID121/dbs/MISSING00269' to '/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243';
4) Shutdown and mount the database:
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 6.4425E+10 bytes
Fixed Size 3729840 bytes
Variable Size 7381976656 bytes
Database Buffers 5.6908E+10 bytes
Redo Buffers 130486272 bytes
Database mounted.
5) Perform media recovery:
SQL> recover datafile 268;
Media recovery complete.
SQL> recover datafile 279;
Media recovery complete.
6) Open the database:
SQL> alter database open;
Database altered.
7) check the status of datafiles:
SQL> set line 250
SQL> col FILE_NAME for a45
SQL> select FILE_NAME, FILE_ID, TABLESPACE_NAME, ONLINE_STATUS from dba_data_files; - After recovery data files online_status went recover to offline.
😎 Bring the datafiles online:
SQL> alter database datafile '/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242' online;
Database altered.
SQL> alter database datafile '/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243' online;
Pedro Pascal
Se unió el 07/03/2018
Facebook
Twitter
Pinterest
Telegram
Linkedin
Whatsapp
Sin respuestas
No hay respuestas para mostrar
Se el primero en responder
contacto@primeinstitute.com
(+51) 1641 9379
(+57) 1489 6964
© 2024 Copyright. Todos los derechos reservados.
Desarrollado por Prime Institute
Hola ¿Puedo ayudarte?