martes, 31 de diciembre de 2013

Oracledbacr By Example:Laboratorio de recuperación con Recovery Manager Oracle Database 11gR2, 11.2.0.3

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>

Todos los Sábados a las 8:00PM

Optimismo para una vida Mejor

Optimismo para una vida Mejor
Noticias buenas que comentar