Início > Oracle > Restore DataFiles no SO

Restore DataFiles no SO

Bom hoje eu fiz 1 teste em minha vmware para ver se a nota da Oracle abaixo funciona mesmo e não é que funciona, rs, segue:
Retrieve deleted files on Unix / Linux using File Descriptors [ID 444749.1]
Bom criei a tablespace TESTE e coloquei apenas 1 datafile na mesma, segue caminho:

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Current log sequence 9
SQL> select file_name,status from dba_data_files;

FILE_NAME
——————————————
STATUS
———
/u01/app/oracle/oradata/teste/users01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/sysaux01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/undotbs01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/system01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/teste1.dbf
AVAILABLE
SQL> exit

Verifiquei que o datafile se encontra no devido lugar
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
[oracle@hodb001vtr teste]$ ls -ltr
total 1456472
-rw-r—–. 1 oracle oinstall 20979712 Jul 8 2011 temp01.dbf
-rw-r—–. 1 oracle oinstall 52429312 Apr 9 09:35 redo03.log
-rw-r—–. 1 oracle oinstall 52429312 Apr 9 09:35 redo01.log
-rw-r—–. 1 oracle oinstall 503324672 Apr 9 09:35 system01.dbf
-rw-r—–. 1 oracle oinstall 26222592 Apr 9 09:35 undotbs01.dbf
-rw-r—–. 1 oracle oinstall 241180672 Apr 9 09:35 sysaux01.dbf
-rw-r—–. 1 oracle oinstall 5251072 Apr 9 09:35 users01.dbf
-rw-r—–. 1 oracle oinstall 536879104 Apr 9 09:35 teste1.dbf
-rw-r—–. 1 oracle oinstall 52429312 Apr 9 09:35 redo02.log
-rw-r—–. 1 oracle oinstall 7061504 Apr 9 09:36 control01.ctl
-rw-r—–. 1 oracle oinstall 7061504 Apr 9 09:36 control02.ctl
-rw-r—–. 1 oracle oinstall 7061504 Apr 9 09:36 control03.ctl

Simplesmente removi o datafile, segue:

[oracle@hodb001vtr teste]$ rm teste1.dbf

Então vamos conectar no banco de dados e realizar alguns checkpoint, segue:

[oracle@hodb001vtr teste]$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Apr 9 09:36:28 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select file_name,status from dba_data_files;

FILE_NAME
——————————————————————————–
STATUS
———
/u01/app/oracle/oradata/teste/users01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/sysaux01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/undotbs01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/system01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/teste1.dbf
AVAILABLE

Vamos ver se no alert gerou algum erro:

SQL> show parameter back

NAME TYPE VALUE
———————————— ———– ——————————
background_core_dump string partial
background_dump_dest string /u01/app/oracle/admin/teste/bd
ump
backup_tape_io_slaves boolean FALSE
db_flashback_retention_target integer 1440
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
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@hodb001vtr teste]$ cd /u01/app/oracle/admin/teste/bdump
[oracle@hodb001vtr bdump]$ ls -ltr
total 112
-rw-r—–. 1 oracle oinstall 726 Jul 8 2011 teste_lgwr_5778.trc
-rw-r—–. 1 oracle oinstall 698 Jul 8 2011 teste_lgwr_5874.trc
-rw-r—–. 1 oracle oinstall 787 Jul 8 2011 teste_lgwr_10356.trc
-rw-r—–. 1 oracle oinstall 1139 Jul 8 2011 teste_p000_7074.trc
-rw-r—–. 1 oracle oinstall 1145 Jul 8 2011 teste_p001_7076.trc
-rw-r—–. 1 oracle oinstall 757 Jul 8 2011 teste_lgwr_7038.trc
-rw-r—–. 1 oracle oinstall 1147 Jul 20 2011 teste_p001_2461.trc
-rw-r—–. 1 oracle oinstall 1139 Jul 20 2011 teste_p000_2459.trc
-rw-r—–. 1 oracle oinstall 785 Jul 20 2011 teste_lgwr_2439.trc
-rw-r—–. 1 oracle oinstall 1137 Apr 9 09:29 teste_p000_2998.trc
-rw-r—–. 1 oracle oinstall 1145 Apr 9 09:29 teste_p001_3000.trc
-rw-r—–. 1 oracle oinstall 844 Apr 9 09:31 teste_lgwr_2978.trc
-rw-r—–. 1 oracle oinstall 903 Apr 9 09:35 teste_lgwr_3252.trc
-rw-r—–. 1 oracle oinstall 53639 Apr 9 09:35 alert_teste.log

Verificado que não registrou nenhum erro no alert neste tempo que fiz a remoção do datafile, segue:
[oracle@hodb001vtr bdump]$ vi alert_teste.log

Completed: ALTER DATABASE OPEN
Mon Apr 9 09:35:18 2012
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Apr 9 09:35:32 2012
Thread 1 advanced to log sequence 7
Current log# 3 seq# 7 mem# 0: /u01/app/oracle/oradata/teste/redo03.log
Thread 1 advanced to log sequence 8
Current log# 1 seq# 8 mem# 0: /u01/app/oracle/oradata/teste/redo01.log
Thread 1 cannot allocate new log, sequence 9
Checkpoint not complete
Current log# 1 seq# 8 mem# 0: /u01/app/oracle/oradata/teste/redo01.log
Thread 1 advanced to log sequence 9
Current log# 2 seq# 9 mem# 0: /u01/app/oracle/oradata/teste/redo02.log

Vamos checkar para ver se o datafile que removi se encontra no devido lugar que não era para estar por causa de sua remoção, segue:
[oracle@hodb001vtr bdump]$ cd /u01/app/oracle/oradata/teste
[oracle@hodb001vtr teste]$ ls -ltr
total 932172
-rw-r—–. 1 oracle oinstall 20979712 Jul 8 2011 temp01.dbf
-rw-r—–. 1 oracle oinstall 52429312 Apr 9 09:35 redo03.log
-rw-r—–. 1 oracle oinstall 52429312 Apr 9 09:35 redo01.log
-rw-r—–. 1 oracle oinstall 52429312 Apr 9 09:37 redo02.log
-rw-r—–. 1 oracle oinstall 503324672 Apr 9 09:37 system01.dbf
-rw-r—–. 1 oracle oinstall 26222592 Apr 9 09:37 undotbs01.dbf
-rw-r—–. 1 oracle oinstall 241180672 Apr 9 09:37 sysaux01.dbf
-rw-r—–. 1 oracle oinstall 5251072 Apr 9 09:37 users01.dbf
-rw-r—–. 1 oracle oinstall 7061504 Apr 9 09:37 control01.ctl
-rw-r—–. 1 oracle oinstall 7061504 Apr 9 09:37 control02.ctl
-rw-r—–. 1 oracle oinstall 7061504 Apr 9 09:37 control03.ctl

O datafile teste1.dbf não existe mesmo. Como o banco não foi baixado e os processos do oracle estavam com o arquivo em memória, segue:

[oracle@hodb001vtr teste]$ lsof |grep -i teste1.dbf|grep -i deleted
oracle 3250 oracle 23uW REG 253,0 536879104 1584298 /u01/app/oracle/oradata/teste/teste1.dbf (deleted)
oracle 3252 oracle 26u REG 253,0 536879104 1584298 /u01/app/oracle/oradata/teste/teste1.dbf (deleted)
oracle 3254 oracle 24u REG 253,0 536879104 1584298 /u01/app/oracle/oradata/teste/teste1.dbf (deleted)
oracle 3256 oracle 20u REG 253,0 536879104 1584298 /u01/app/oracle/oradata/teste/teste1.dbf (deleted)
oracle 3262 oracle 23u REG 253,0 536879104 1584298 /u01/app/oracle/oradata/teste/teste1.dbf (deleted)
[oracle@hodb001vtr teste]$ cd /proc/3250/fd
[oracle@hodb001vtr fd]$ ls -ltr
total 0
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 9 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_teste.dat
l-wx——. 1 oracle oinstall 64 Apr 9 09:38 8 -> /u01/app/oracle/admin/teste/bdump/alert_teste.log
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 7 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkinstteste (deleted)
l-wx——. 1 oracle oinstall 64 Apr 9 09:38 6 -> /u01/app/oracle/admin/teste/bdump/alert_teste.log
l-wx——. 1 oracle oinstall 64 Apr 9 09:38 5 -> /u01/app/oracle/admin/teste/udump/teste_ora_3242.trc
lr-x——. 1 oracle oinstall 64 Apr 9 09:38 4 -> /dev/null
lr-x——. 1 oracle oinstall 64 Apr 9 09:38 3 -> /dev/null
lr-x——. 1 oracle oinstall 64 Apr 9 09:38 25 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 24 -> /u01/app/oracle/oradata/teste/temp01.dbf
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 23 -> /u01/app/oracle/oradata/teste/teste1.dbf (deleted)
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 22 -> /u01/app/oracle/oradata/teste/users01.dbf
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 21 -> /u01/app/oracle/oradata/teste/sysaux01.dbf
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 20 -> /u01/app/oracle/oradata/teste/undotbs01.dbf
lr-x——. 1 oracle oinstall 64 Apr 9 09:38 2 -> /dev/null
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 19 -> /u01/app/oracle/oradata/teste/system01.dbf
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 18 -> /u01/app/oracle/oradata/teste/control03.ctl
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 17 -> /u01/app/oracle/oradata/teste/control02.ctl
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 16 -> /u01/app/oracle/oradata/teste/control01.ctl
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 15 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkTESTE
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 14 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_teste.dat
lr-x——. 1 oracle oinstall 64 Apr 9 09:38 13 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lr-x——. 1 oracle oinstall 64 Apr 9 09:38 12 -> /dev/zero
lr-x——. 1 oracle oinstall 64 Apr 9 09:38 11 -> /dev/zero
lrwx——. 1 oracle oinstall 64 Apr 9 09:38 10 -> /u01/app/oracle/admin/teste/adump/ora_3242.aud
lr-x——. 1 oracle oinstall 64 Apr 9 09:38 1 -> /dev/null
lr-x——. 1 oracle oinstall 64 Apr 9 09:38 0 -> /dev/null

Bom apenas direcionei a saida do cat para o datafile

cat <id_arquivo> > <local_original_datafile>

[oracle@hodb001vtr fd]$ cat 23 > /u01/app/oracle/oradata/teste/teste1.dbf
[oracle@hodb001vtr fd]$ cd /u01/app/oracle/oradata/teste
[oracle@hodb001vtr teste]$ ls -ltr |grep teste
-rw-r–r–. 1 oracle oinstall 536879104 Apr 9 09:39 teste1.dbf

E lá estava o datafile, apenas dei as devidas permissões para o mesmo, segue:

[oracle@hodb001vtr teste]$ chmod 0640 teste1.dbf
[oracle@hodb001vtr teste]$ ls -ltr |grep teste
-rw-r—–. 1 oracle oinstall 536879104 Apr 9 09:39 teste1.dbf

Bom vamos conectar no banco e abaixa-lo:
[oracle@hodb001vtr teste]$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Apr 9 09:41:37 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> shut immediate
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/teste/teste1.dbf’
ORA-01208: data file is an old version – not accessing current version
SQL> shut abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 88082700 bytes
Database Buffers 71303168 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/teste/teste1.dbf’
SQL> recover datafile 5 ;
Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select file_name,status from dba_data_files;

FILE_NAME
——————————————————————————–
STATUS
———
/u01/app/oracle/oradata/teste/users01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/sysaux01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/undotbs01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/system01.dbf
AVAILABLE

/u01/app/oracle/oradata/teste/teste1.dbf
AVAILABLE
SQL> alter system checkpoint;

System altered.
Pronto apenas para documentar o passo a passo caso eu precise alum dia
Abraço!

Categorias:Oracle
  1. Nenhum comentário ainda.
  1. No trackbacks yet.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: