Backup & Recovery Database
Laboratorio de recuperación con
Recovery Manager
Oracle Database 11gR2, 11.2.0.3
·
Pérdida archivo de
configuración instancia BD
·
Recuperación BD con
pérdida datafile sin respaldo
·
Recuperación BD con
pérdida de datafile con respaldo
Elaboración: Ronald Vargas Q.
Realización: 120 minutos
login as:
oracle
oracle@10.0.0.1's password:
[oracle@lab1
~]$ cd
[oracle@lab1
~]$ pwd
/home/oracle
Generando la falla a nivel del archivo
de parámetros de la base de datos
[oracle@lab1
~]$ cd $ORACLE_HOME/dbs
[oracle@lab1 dbs]$ mv spfilelab1.ora spfilelab1.bk
[oracle@lab1 dbs]$ ls -la
total 9584
drwxr-xr-x 2 oracle oinstall 4096 Dec
7 16:37 .
drwxr-xr-x
75 oracle oinstall 4096 Dec 7 12:47 ..
-rw-rw---- 1 oracle oinstall 1544 Dec
7 16:37 hc_lab1.dat
-rw-rw---- 1 oracle oinstall 1544 Dec
7 13:48 hc_reco.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15
2009 init.ora
-rw-r----- 1 oracle oinstall 24 Dec
7 12:39 lkLAB1
-rw-r----- 1 oracle oinstall 24 Dec
7 13:42 lkRECO
-rw-r----- 1 oracle oinstall 1536 Dec
7 12:44 orapwlab1
-rw-r----- 1 oracle oinstall 1536 Dec
7 13:47 orapwreco
-rw-r----- 1 oracle oinstall 9748480 Dec 7 16:12 snapcf_lab1.f
-rw-r----- 1 oracle oinstall 2560 Dec
7 16:06 spfilelab1.bk
-rw-r----- 1 oracle oinstall 2560 Dec
7 16:04 spfilereco.ora
[oracle@lab1
dbs]$
[oracle@lab1
~]$ lsnrctl status
LSNRCTL
for Linux: Version 11.2.0.3.0 - Production on 07-DEC-2012 16:32:11
Copyright
(c) 1991, 2011, Oracle. All rights
reserved.
Connecting
to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of
the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version
11.2.0.3.0 - Production
Start
Date 07-DEC-2012 13:38:20
Uptime 0 days 2 hr. 53 min. 51 sec
Trace
Level off
Security ON: Local OS Authentication
SNMP OFF
Listener
Parameter File
/opt/product/oracle/11gR2/network/admin/listener.ora
Listener
Log File
/opt/product/oracle/diag/tnslsnr/lab1/listener/alert/log.xml
Listening
Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services
Summary...
Service
"reco" has 1 instance(s).
Instance "reco", status READY, has
1 handler(s) for this service...
Service
"recoXDB" has 1 instance(s).
Instance "reco", status READY, has
1 handler(s) for this service...
The
command completed successfully
Inicio de la instancia de la base de datos LAB1
[oracle@lab1 ~]$ sqlplus /nolog
SQL*Plus:
Release 11.2.0.3.0 Production on Fri Dec 7 16:32:16 2012
Copyright
(c) 1982, 2011, Oracle. All rights
reserved.
SQL>
connect / as sysdba
Connected
to an idle instance.
SQL>
startup
ORA-01078: failure in processing system
parameters
LRM-00109: could not open parameter file
'/opt/product/oracle/11gR2/dbs/initlab1.ora'
SQL>
exit
Disconnected
[oracle@lab1
~]$
[oracle@lab1
~]$ more rman_recovery.sh
rman
catalog=rman/rman@reco target /
[oracle@lab1
~]$
Procedimiento de recuperación de archive de parámetros en base a la
política de autobackup del archivo controlfile y spfile.
[oracle@lab1 ~]$ ./rman_recovery.sh
Recovery
Manager: Release 11.2.0.3.0 - Production on Fri Dec 7 16:35:14 2012
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to target database (not started)
connected
to recovery catalog database
RMAN> startup force nomount;
startup
failed: ORA-01078: failure in processing system parameters
LRM-00109:
could not open parameter file '/opt/product/oracle/11gR2/dbs/initlab1.ora'
starting
Oracle instance without parameter file for retrieval of spfile
Oracle
instance started
Total
System Global Area 159019008 bytes
Fixed
Size 1343612 bytes
Variable
Size 75501444 bytes
Database
Buffers 79691776 bytes
Redo
Buffers 2482176 bytes
RMAN>
list backup;
List of
Backup Sets
===================
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
148 Full
456.91M DISK 00:03:26 07-DEC-12
BP Key: 150 Status: EXPIRED Compressed: NO Tag: TAG20121207T143603
Piece Name:
/opt/product/oracle/11gR2/dbs/apprman_backupBackupLAB1_DB_0ans95ek_10_1
List of Datafiles in backup set 148
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
2
Full 846472 07-DEC-12
/app/oradata/lab1/sysaux01.dbf
3
Full 846472 07-DEC-12
/app/oradata/lab1/undotbs01.dbf
4
Full 846472 07-DEC-12
/app/oradata/lab1/users01.dbf
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
149 Full
668.03M DISK 00:04:05 07-DEC-12
BP Key: 151 Status: EXPIRED Compressed: NO Tag: TAG20121207T143603
Piece Name:
/opt/product/oracle/11gR2/dbs/apprman_backupBackupLAB1_DB_09ns95ek_9_1
List of Datafiles in backup set 149
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
1
Full 846471 07-DEC-12
/app/oradata/lab1/system01.dbf
5
Full 846471 07-DEC-12
/app/oradata/lab1/example01.dbf
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
165 Full
9.36M DISK 00:00:01 07-DEC-12
BP Key: 168 Status: AVAILABLE Compressed: NO Tag: TAG20121207T144014
Piece Name:
/opt/product/oracle/11gR2/dbs/apprman_backupcontrol_bk_c-2354388833-20121207-00?
SPFILE Included: Modification time: 07-DEC-12
SPFILE db_unique_name: LAB1
Control File Included: Ckp SCN: 846764 Ckp time: 07-DEC-12
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
194 Full
3.29M DISK 00:00:01 07-DEC-12
BP Key: 197 Status: AVAILABLE Compressed: NO Tag: TAG20121207T144503
Piece Name:
/opt/product/oracle/11gR2/dbs/apprman_backupBackupLAB1_DB_0cns95vg_12_1
List of Datafiles in backup set 194
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
4
Full 847009 07-DEC-12
/app/oradata/lab1/users01.dbf
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
203 Full
9.36M DISK 00:00:01 07-DEC-12
BP Key: 205 Status: AVAILABLE Compressed: NO Tag: TAG20121207T144507
Piece Name:
/opt/product/oracle/11gR2/dbs/apprman_backupcontrolbk_c-2354388833-20121207-01
SPFILE Included: Modification time: 07-DEC-12
SPFILE db_unique_name: LAB1
Control File Included: Ckp SCN: 847024 Ckp time: 07-DEC-12
BS
Key Size Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
236 617.50K
DISK 00:00:00 07-DEC-12
BP Key: 241 Status: AVAILABLE Compressed: NO Tag: TAG20121207T145047
Piece Name:
/app/rman_backup/Backup_Lab1_LAB1_0gns96aa_16_1
List of Archived Logs in backup set 236
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
7 845947 07-DEC-12 847306 07-DEC-12
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
253 Full
457.11M DISK 00:00:43 07-DEC-12
BP Key: 256 Status: AVAILABLE Compressed: NO Tag: TAG20121207T145050
Piece Name:
/app/rman_backup/Backup_Lab1_LAB1_0ins96ac_18_1
List of Datafiles in backup set 253
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
2
Full 847366 07-DEC-12
/app/oradata/lab1/sysaux01.dbf
3
Full 847366 07-DEC-12
/app/oradata/lab1/undotbs01.dbf
4
Full 847366 07-DEC-12
/app/oradata/lab1/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
254 Full
668.03M DISK 00:01:26 07-DEC-12
BP Key: 257 Status: AVAILABLE Compressed: NO Tag: TAG20121207T145050
Piece Name:
/app/rman_backup/Backup_Lab1_LAB1_0hns96ac_17_1
List of Datafiles in backup set 254
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
1
Full 847365 07-DEC-12
/app/oradata/lab1/system01.dbf
5
Full 847365 07-DEC-12
/app/oradata/lab1/example01.dbf
BS
Key Size Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
269 24.50K
DISK 00:00:00 07-DEC-12
BP Key: 272 Status: AVAILABLE Compressed: NO Tag: TAG20121207T145230
Piece Name:
/app/rman_backup/Backup_Lab1_LAB1_0jns96df_19_1
List of Archived Logs in backup set 269
Thrd Seq
Low SCN Low Time Next SCN
Next Time
----
------- ---------- --------- ---------- ---------
1
8 847306 07-DEC-12 847410 07-DEC-12
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
280 Full
9.36M DISK 00:00:01 07-DEC-12
BP Key: 282 Status: AVAILABLE Compressed: NO Tag: TAG20121207T145233
Piece Name:
/app/rman_backup/controlbk_c-2354388833-20121207-02
SPFILE Included: Modification time: 07-DEC-12
SPFILE db_unique_name: LAB1
Control File Included: Ckp SCN: 847443 Ckp time: 07-DEC-12
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
349 Incr 0
400.29M DISK 00:00:33 07-DEC-12
BP Key: 353 Status: AVAILABLE Compressed: NO Tag: TAG20121207T161016
Piece Name:
/app/rman_backup/Backup_Lab1_LAB1_0qns9ava_26_1
List of Datafiles in backup set 349
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
2
0 Incr 851855 07-DEC-12 /app/oradata/lab1/sysaux01.dbf
3
0 Incr 851855 07-DEC-12 /app/oradata/lab1/undotbs01.dbf
4
0 Incr 851855 07-DEC-12 /app/oradata/lab1/users01.dbf
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
350 Incr 0
667.70M DISK 00:01:17 07-DEC-12
BP Key: 354 Status: AVAILABLE Compressed: NO Tag: TAG20121207T161016
Piece Name:
/app/rman_backup/Backup_Lab1_LAB1_0pns9av9_25_1
List of Datafiles in backup set 350
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
1
0 Incr 851854 07-DEC-12 /app/oradata/lab1/system01.dbf
5
0 Incr 851854 07-DEC-12 /app/oradata/lab1/example01.dbf
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
364 Full
9.36M DISK
00:00:02 07-DEC-12
BP Key: 367 Status: AVAILABLE Compressed: NO Tag: TAG20121207T161136
Piece Name:
/app/rman_backup/controlbk_c-2354388833-20121207-03
SPFILE Included: Modification time: 07-DEC-12
SPFILE db_unique_name: LAB1
Control File Included: Ckp SCN: 851900 Ckp time: 07-DEC-12
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
385 Incr 1
184.00K DISK 00:00:23 07-DEC-12
BP Key: 389 Status: AVAILABLE Compressed: NO Tag: TAG20121207T161157
Piece Name:
/app/rman_backup/Backup_Lab1_LAB1_0tns9b2e_29_1
List of Datafiles in backup set 385
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
2
1 Incr 851951 07-DEC-12 /app/oradata/lab1/sysaux01.dbf
3
1 Incr 851951 07-DEC-12 /app/oradata/lab1/undotbs01.dbf
4
1 Incr 851951 07-DEC-12 /app/oradata/lab1/users01.dbf
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
386 Incr 1
56.00K DISK 00:00:40 07-DEC-12
BP Key: 390 Status: AVAILABLE Compressed: NO Tag: TAG20121207T161157
Piece Name:
/app/rman_backup/Backup_Lab1_LAB1_0sns9b2e_28_1
List of Datafiles in backup set 386
File LV Type Ckp SCN Ckp Time
Name
---- -- ---- ---------- --------- ----
1
1 Incr 851952 07-DEC-12 /app/oradata/lab1/system01.dbf
5
1 Incr 851952 07-DEC-12 /app/oradata/lab1/example01.dbf
BS
Key Type LV Size Device Type Elapsed Time Completion Time
-------
---- -- ---------- ----------- ------------ ---------------
402 Full
9.36M DISK 00:00:01 07-DEC-12
BP Key: 405 Status: AVAILABLE Compressed: NO Tag: TAG20121207T161245
Piece Name: /app/rman_backup/controlbk_c-2354388833-20121207-04
SPFILE Included: Modification time: 07-DEC-12
SPFILE db_unique_name: LAB1
Control File Included: Ckp SCN: 851989 Ckp time: 07-DEC-12
RMAN>
RMAN> restore spfile from autobackup;
Starting
restore at 07-DEC-12
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=18 device type=DISK
allocated
channel: ORA_DISK_2
channel
ORA_DISK_2: SID=19 device type=DISK
channel
ORA_DISK_1: looking for AUTOBACKUP on day: 20121207
channel
ORA_DISK_1: AUTOBACKUP found:
/app/rman_backup/controlbk_c-2354388833-20121207-04
channel
ORA_DISK_2: looking for AUTOBACKUP on day: 20121207
channel
ORA_DISK_2: skipped, AUTOBACKUP already found
channel
ORA_DISK_1: restoring spfile from AUTOBACKUP
/app/rman_backup/controlbk_c-2354388833-20121207-04
channel
ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished
restore at 07-DEC-12
RMAN> shutdown
abort;
Oracle
instance shut down
RMAN>
startup
connected
to target database (not started)
Oracle
instance started
database
mounted
database
opened
Total
System Global Area 313860096 bytes
Fixed
Size 1344652 bytes
Variable
Size 234883956 bytes
Database
Buffers 71303168 bytes
Redo
Buffers 6328320 bytes
RMAN>
RMAN>
exit
Recovery
Manager complete.
Comprobación de recuperación correcta
[oracle@lab1
~]$ clear screen
[oracle@lab1
~]$ ORACLE_SID=lab1
[oracle@lab1
~]$ sqlplus /nolog
SQL*Plus:
Release 11.2.0.3.0 Production on Fri Dec 7 16:38:19 2012
Copyright
(c) 1982, 2011, Oracle. All rights
reserved.
SQL>
connect / as sysdba
Connected.
SQL>
show sga
Total
System Global Area 313860096 bytes
Fixed
Size 1344652 bytes
Variable
Size 234883956 bytes
Database
Buffers 71303168 bytes
Redo
Buffers 6328320 bytes
SQL>
select instance_name from v$instance;
INSTANCE_NAME
----------------
lab1
Elapsed:
00:00:00.02
SQL>
exit
Disconnected
from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lab1
~]$ cd $ORACLE_HOME/dbs
[oracle@lab1 dbs]$ ls -la
total 9584
drwxr-xr-x 2 oracle oinstall 4096 Dec
7 16:37 .
drwxr-xr-x
75 oracle oinstall 4096 Dec 7 12:47 ..
-rw-rw---- 1 oracle oinstall 1544 Dec
7 16:37 hc_lab1.dat
-rw-rw---- 1 oracle oinstall 1544 Dec
7 13:48 hc_reco.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15
2009 init.ora
-rw-r----- 1 oracle oinstall 24 Dec
7 12:39 lkLAB1
-rw-r----- 1 oracle oinstall 24 Dec
7 13:42 lkRECO
-rw-r----- 1 oracle oinstall 1536 Dec
7 12:44 orapwlab1
-rw-r----- 1 oracle oinstall 1536 Dec
7 13:47 orapwreco
-rw-r----- 1 oracle oinstall 9748480 Dec 7 16:12 snapcf_lab1.f
-rw-r----- 1 oracle oinstall 2560 Dec
7 16:06 spfilelab1.bk
-rw-r----- 1 oracle oinstall 2560 Dec
7 16:38 spfilelab1.ora
-rw-r----- 1 oracle oinstall 2560 Dec
7 16:04 spfilereco.ora
[oracle@lab1 dbs]$
Caso #2 Recuperación de datafile
perdido con respaldo y sin respaldo
Recuperación sin respaldo
[oracle@lab1
~]$ sqlplus /nolog
connect
SQL*Plus: Release
11.2.0.3.0 Production on Fri Dec 7 17:16:55 2012
Copyright
(c) 1982, 2011, Oracle. All rights
reserved.
SQL> /
as sysdba
Connected.
SQL>
show parameter create
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter system set
db_create_file_dest='/app/oradata/lab1' scope=both;
System
altered.
Elapsed:
00:00:00.28
SQL>
SQL> create tablespace tbs_error_data;
Tablespace
created.
Elapsed:
00:00:05.59
SQL>
column file_name format a80
SQL>
select file_name, file_id from dba_data_files;
FILE_NAME
FILE_ID
--------------------------------------------------------------
----------
/app/oradata/lab1/users01.dbf 4
/app/oradata/lab1/undotbs01.dbf 3
/app/oradata/lab1/sysaux01.dbf 2
/app/oradata/lab1/system01.dbf 1
/app/oradata/lab1/example01.dbf 5
/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_erro_8d4ycd14_.dbf 6
6 rows selected.
Elapsed: 00:00:00.01
SQL>
------------------<<<<<<<<<<<<<<CLAVE>>>>>>>>>>>>>>-------------------
SQL>
!rm /app/oradata/lab1/LAB1/datafile/o1_mf_tbs_erro_8d4ycd14_.dbf
SQL>
!ls -l /app/oradata/lab1/LAB1/datafile/*
ls:
/app/oradata/lab1/LAB1/datafile/*: No such file or directory
---------------------<<<<<<<<<<<<<<>>>>>>>>>>>>>>-------------------
SQL>
shutdown immediate
ORA-03113:
end-of-file on communication channel
Process
ID: 10281
Session
ID: 44 Serial number: 9
SQL>
shutdown abort
ORA-24324:
service handle not initialized
ORA-01041:
internal error. hostdef extension doesn't exist
SQL>
connect / as sysdba
Connected
to an idle instance.
SQL>
shutdown abort
ORACLE
instance shut down.
SQL>
startup nomount;
ORACLE
instance started.
Total
System Global Area 313860096 bytes
Fixed
Size 1344652 bytes
Variable
Size 243272564 bytes
Database
Buffers 62914560 bytes
Redo
Buffers 6328320 bytes
SQL>
alter database mount;
Database
altered.
SQL>
alter database open;
alter
database open
*
ERROR at
line 1:
ORA-01157:
cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6:
'/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_erro_8d4ycd14_.dbf'
Elapsed: 00:00:00.06
SQL>
alter database tbs_error_data offline;
alter
database tbs_error_data offline
*
ERROR at
line 1:
ORA-02231:
missing or invalid option to ALTER DATABASE
Elapsed:
00:00:00.02
SQL> alter database datafile '/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_erro_8d4ycd14_.dbf'
offline drop;
Database
altered.
Elapsed:
00:00:00.10
SQL> alter database open;
Database
altered.
Elapsed:
00:00:13.56
SQL> drop tablespace tbs_error_data including
contents;
Tablespace
dropped.
Elapsed:
00:00:12.94
SQL>
SQL>
shutdown immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
startup
ORACLE
instance started.
Total
System Global Area 313860096 bytes
Fixed
Size 1344652 bytes
Variable
Size 243272564 bytes
Database
Buffers 62914560 bytes
Redo
Buffers 6328320 bytes
Database
mounted.
Database
opened.
SQL>
Recuperación con respaldo
[oracle@lab1
~]$ ./rman_connect.sh
Recovery
Manager: Release 11.2.0.3.0 - Production on Fri Dec 7 17:02:31 2012
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: LAB1 (DBID=2354388833)
connected
to recovery catalog database
RMAN>
list backup of archivelog all;
List of
Backup Sets
===================
BS
Key Size Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
236 617.50K
DISK 00:00:00 07-DEC-12
BP Key: 241 Status: AVAILABLE Compressed: NO Tag: TAG20121207T145047
Piece Name:
/app/rman_backup/Backup_Lab1_LAB1_0gns96aa_16_1
List of Archived Logs in backup set 236
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
7 845947 07-DEC-12 847306 07-DEC-12
BS
Key Size Device Type Elapsed Time Completion Time
-------
---------- ----------- ------------ ---------------
269 24.50K
DISK 00:00:00 07-DEC-12
BP Key: 272 Status: AVAILABLE Compressed: NO Tag: TAG20121207T145230
Piece Name:
/app/rman_backup/Backup_Lab1_LAB1_0jns96df_19_1
List of Archived Logs in backup set 269
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ---------- ---------
1 8
847306 07-DEC-12 847410 07-DEC-12
RMAN>
SQL> create tablespace tbs_error_data;
Tablespace
created.
Elapsed:
00:00:03.73
SQL>
exit
Disconnected
from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lab1
~]$ cd
Generando
respaldo de la base de datos
[oracle@lab1
~]$ ./rman_connect.sh
Recovery
Manager: Release 11.2.0.3.0 - Production on Fri Dec 7 17:43:19 2012
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: LAB1 (DBID=2354388833)
connected to recovery catalog database
RMAN>
RMAN> backup database;
Starting
backup at 07-DEC-12
starting
full resync of recovery catalog
full
resync complete
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=35 device type=DISK
allocated
channel: ORA_DISK_2
channel
ORA_DISK_2: SID=33 device type=DISK
channel
ORA_DISK_1: starting full datafile backup set
channel
ORA_DISK_1: specifying datafile(s) in backup set
input
datafile file number=00002 name=/app/oradata/lab1/sysaux01.dbf
input
datafile file number=00005 name=/app/oradata/lab1/example01.dbf
input
datafile file number=00003 name=/app/oradata/lab1/undotbs01.dbf
channel
ORA_DISK_1: starting piece 1 at 07-DEC-12
channel
ORA_DISK_2: starting full datafile backup set
channel
ORA_DISK_2: specifying datafile(s) in backup set
input
datafile file number=00001 name=/app/oradata/lab1/system01.dbf
input
datafile file number=00006
name=/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_erro_8d4zr2g3_.dbf
input
datafile file number=00004 name=/app/oradata/lab1/users01.dbf
channel
ORA_DISK_2: starting piece 1 at 07-DEC-12
channel
ORA_DISK_2: finished piece 1 at 07-DEC-12
piece
handle=/app/rman_backup/Backup_Lab1_LAB1_14ns9gg0_36_1 tag=TAG20121207T174431
comment=NONE
channel
ORA_DISK_2: backup set complete, elapsed time: 00:01:06
channel
ORA_DISK_1: finished piece 1 at 07-DEC-12
piece
handle=/app/rman_backup/Backup_Lab1_LAB1_13ns9gg0_35_1 tag=TAG20121207T174431
comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:01:36
Finished
backup at 07-DEC-12
Starting
Control File and SPFILE Autobackup at 07-DEC-12
piece handle=/app/rman_backup/controlbk_c-2354388833-20121207-07
comment=NONE
Finished
Control File and SPFILE Autobackup at 07-DEC-12
RMAN>
SQL>
exit
Disconnected
from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
sqlplu[oracle@lab1
~]$ sqlplus /nolog
connect /
as sysdba
SQL*Plus:
Release 11.2.0.3.0 Production on Fri Dec 7 17:49:28 2012
Copyright
(c) 1982, 2011, Oracle. All rights
reserved.
SQL>
Connected.
SQL>
column file_name format a80
SQL>
set linesize 200
SQL>
select file_name, file_id from dba_data_files;
FILE_NAME
FILE_ID
--------------------------------------------------------------------------------
----------
/app/oradata/lab1/users01.dbf
4
/app/oradata/lab1/undotbs01.dbf
3
/app/oradata/lab1/sysaux01.dbf 2
/app/oradata/lab1/system01.dbf
1
/app/oradata/lab1/example01.dbf
5
/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_erro_8d4zr2g3_.dbf 6
6 rows selected.
Elapsed:
00:00:00.20
SQL>
select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TBS_ERROR_DATA
7 rows
selected.
Elapsed:
00:00:00.06
SQL> !rm
/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_erro_8d4zr2g3_.dbf
SQL>
shutdown immediate;
ORA-03113:
end-of-file on communication channel
Process
ID: 11084
Session
ID: 33 Serial number: 23
Volviendo
a levantar la instancia de base de datos
[oracle@lab1
~]$ sqlplus /nolog
SQL*Plus:
Release 11.2.0.3.0 Production on Fri Dec 7 17:53:29 2012
Copyright
(c) 1982, 2011, Oracle. All rights
reserved.
SQL>
connect / as sysdba
Connected
to an idle instance.
SQL>
startup
ORACLE
instance started.
Total
System Global Area 313860096 bytes
Fixed
Size 1344652 bytes
Variable
Size 243272564 bytes
Database
Buffers 62914560 bytes
Redo
Buffers 6328320 bytes
Database
mounted.
ORA-01157: cannot identify/lock data file 6
- see DBWR trace file
ORA-01110:
data file 6: '/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_erro_8d4zr2g3_.dbf'
SQL>
[oracle@lab1
~]$ cd
[oracle@lab1
~]$ ./rman_recovery.sh
Recovery
Manager: Release 11.2.0.3.0 - Production on Fri Dec 7 17:54:58 2012
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: LAB1 (DBID=2354388833, not open)
connected
to recovery catalog database
RMAN> restore tablespace tbs_error_data;
Starting
restore at 07-DEC-12
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=20 device type=DISK
allocated
channel: ORA_DISK_2
channel
ORA_DISK_2: SID=17 device type=DISK
channel
ORA_DISK_1: starting datafile backup set restore
channel
ORA_DISK_1: specifying datafile(s) to restore from backup set
channel
ORA_DISK_1: restoring datafile 00006 to
/app/oradata/lab1/LAB1/datafile/o1_mf_tbs_erro_8d4zr2g3_.dbf
channel
ORA_DISK_1: reading from backup piece /app/rman_backup/Backup_Lab1_LAB1_14ns9gg0_36_1
channel
ORA_DISK_1: piece handle=/app/rman_backup/Backup_Lab1_LAB1_14ns9gg0_36_1
tag=TAG20121207T174431
channel
ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore
at 07-DEC-12
starting
full resync of recovery catalog
full
resync complete
RMAN> alter database open;
database
opened
RMAN>
exit
Recovery
Manager complete.
[oracle@lab1 ~]$
Validando recuperación y creando objeto en tablespace recuperado
[oracle@lab1
~]$ sqlplus system/oracle@lab1
SQL*Plus:
Release 11.2.0.3.0 Production on Fri Dec 7 17:56:44 2012
Copyright
(c) 1982, 2011, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
create table t1(campo1 number, campo2 varchar2(20)) tablespace tbs_error_data;
Table
created.
Elapsed:
00:00:01.31
SQL>
insert into t1 values(1,'Datos no validos');
1 row
created.
Elapsed:
00:00:00.03
SQL>
select * from t1;
CAMPO1 CAMPO2
---------- --------------------
1
Datos no validos
Elapsed: 00:00:00.02
SQL>
