recover a nonsystem datafile in archivelog mode



First check that your database is working on archivelog mode:

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     32
Next log sequence to archive   34
Current log sequence           34

If not, follow this post database in archivelog mode
Second, perform a full backup of your database.

RMAN> backup database;

Starting backup at 27-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 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=00003 name=/oracle/db/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/oracle/db/oradata/orcl/system01.dbf
input datafile file number=00013 name=/oracle/db/oradata/orcl/WCPVM_iasactivities.dbf
input datafile file number=00012 name=/oracle/db/oradata/orcl/WCPVM_ocs.dbf
input datafile file number=00007 name=/oracle/db/oradata/orcl/WCPVM_iaswebcenter.dbf
input datafile file number=00004 name=/oracle/db/oradata/orcl/undotbs01.dbf
input datafile file number=00008 name=/oracle/db/oradata/orcl/WCPVM_iasjive.dbf
input datafile file number=00011 name=/oracle/db/oradata/orcl/WCPVM_mds.dbf
input datafile file number=00002 name=/oracle/db/oradata/orcl/WCPVM_ias_opss.dbf
input datafile file number=00010 name=/oracle/db/oradata/orcl/WCPVM_iau.dbf
input datafile file number=00005 name=/oracle/db/oradata/orcl/WCPVM_webcenter_portlet.dbf
input datafile file number=00009 name=/oracle/db/oradata/orcl/WCPVM_svctbl.dbf
input datafile file number=00006 name=/oracle/db/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 27-SEP-17
channel ORA_DISK_1: finished piece 1 at 27-SEP-17
piece handle=/oracle/db/fast_recovery_area/ORCL/backupset/2017_09_27/o1_mf_nnndf_TAG20170927T071644_dwqdpds0_.bkp tag=TAG20170927T071644 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 27-SEP-17
channel ORA_DISK_1: finished piece 1 at 27-SEP-17
piece handle=/oracle/db/fast_recovery_area/ORCL/backupset/2017_09_27/o1_mf_ncsnf_TAG20170927T071644_dwqdspvd_.bkp tag=TAG20170927T071644 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-SEP-17

i perform a dd writing zeros to datafile 6 in order to make corrupt this datafile

[oracle@wcp12cr2 Documents]$ dd if=/dev/zero of=/oracle/db/oradata/orcl/users01.dbf bs=4096k count=10
10+0 records in
10+0 records out
41943040 bytes (42 MB) copied, 0.564675 s, 74.3 MB/s


Now, you can see that USERS tablespace has 0Mb

[oracle@wcp12cr2 Documents]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Sep 27 07:30:15 2017

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1423238009)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    820      SYSTEM               ***     /oracle/db/oradata/orcl/system01.dbf
2    60       WCPVM_IAS_OPSS       ***     /oracle/db/oradata/orcl/WCPVM_ias_opss.dbf
3    880      SYSAUX               ***     /oracle/db/oradata/orcl/sysaux01.dbf
4    145      UNDOTBS1             ***     /oracle/db/oradata/orcl/undotbs01.dbf
5    25       WCPVM_IAS_PORTLET    ***     /oracle/db/oradata/orcl/WCPVM_webcenter_portlet.dbf
6    0        USERS                ***     /oracle/db/oradata/orcl/users01.dbf
7    250      WCPVM_IAS_WEBCENTER  ***     /oracle/db/oradata/orcl/WCPVM_iaswebcenter.dbf
8    100      WCPVM_IAS_DISCUSS    ***     /oracle/db/oradata/orcl/WCPVM_iasjive.dbf
9    10       WCPVM_STB            ***     /oracle/db/oradata/orcl/WCPVM_svctbl.dbf
10   60       WCPVM_IAU            ***     /oracle/db/oradata/orcl/WCPVM_iau.dbf
11   100      WCPVM_MDS            ***     /oracle/db/oradata/orcl/WCPVM_mds.dbf
12   400      WCPVM_OCS            ***     /oracle/db/oradata/orcl/WCPVM_ocs.dbf
13   500      WCPVM_IAS_ACTIVITY   ***     /oracle/db/oradata/orcl/WCPVM_iasactivities.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    87       TEMP                 32767       /oracle/db/oradata/orcl/temp01.dbf
2    100      WCPVM_IAS_TEMP       32767       /oracle/db/oradata/orcl/WCPVM_iastemp.dbf
3    100      WCPVM_OCS_TEMP       150         /oracle/db/oradata/orcl/WCPVM_ocstemp.dbf

and in alert_log you can see:

Hex dump of (file 6, block 1) in trace file /oracle/db/diag/rdbms/orcl/orcl/trace/orcl_ora_6598.trc

Corrupt block relative dba: 0x01800001 (file 6, block 1)
Completely zero block found during kcvxfh v8

Reading datafile '/oracle/db/oradata/orcl/users01.dbf' for corruption at rdba: 0x01800001 (file 6, block 1)
Reread (file 6, block 1) found different corrupt data (no logical check)
Hex dump of (file 6, block 1) in trace file /oracle/db/diag/rdbms/orcl/orcl/trace/orcl_ora_6598.trc

Corrupt block relative dba: 0x01800001 (file 6, block 1)
Completely zero block found during reread

so we put this datafile in offline mode:
SQL> alter database datafile 6 offline;

Database altered.
After this we can restore the datafile and recover in order to apply all redologs since full backup to present time as you can see below:

[oracle@wcp12cr2 trace]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Sep 27 07:35:42 2017

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1423238009)

RMAN> restore datafile 6;

Starting restore at 27-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 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 /oracle/db/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/db/fast_recovery_area/ORCL/backupset/2017_09_27/o1_mf_nnndf_TAG20170927T071644_dwqdpds0_.bkp
channel ORA_DISK_1: piece handle=/oracle/db/fast_recovery_area/ORCL/backupset/2017_09_27/o1_mf_nnndf_TAG20170927T071644_dwqdpds0_.bkp tag=TAG20170927T071644
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 27-SEP-17

RMAN> recover datafile 6;

Starting recover at 27-SEP-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 27-SEP-17

Then all we need to do is to online the datafile number 6, you can do it from sqlplus or rman, in this case we make it from rman

RMAN> sql 'alter database datafile 6 online';

using target database control file instead of recovery catalog
sql statement: alter database datafile 6 online

Now, we can see user tablespace is ok, and no errors in alert.log

RMAN>  report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    820      SYSTEM               ***     /oracle/db/oradata/orcl/system01.dbf
2    60       WCPVM_IAS_OPSS       ***     /oracle/db/oradata/orcl/WCPVM_ias_opss.dbf
3    880      SYSAUX               ***     /oracle/db/oradata/orcl/sysaux01.dbf
4    145      UNDOTBS1             ***     /oracle/db/oradata/orcl/undotbs01.dbf
5    25       WCPVM_IAS_PORTLET    ***     /oracle/db/oradata/orcl/WCPVM_webcenter_portlet.dbf
6    5        USERS                ***     /oracle/db/oradata/orcl/users01.dbf
7    250      WCPVM_IAS_WEBCENTER  ***     /oracle/db/oradata/orcl/WCPVM_iaswebcenter.dbf
8    100      WCPVM_IAS_DISCUSS    ***     /oracle/db/oradata/orcl/WCPVM_iasjive.dbf
9    10       WCPVM_STB            ***     /oracle/db/oradata/orcl/WCPVM_svctbl.dbf
10   60       WCPVM_IAU            ***     /oracle/db/oradata/orcl/WCPVM_iau.dbf
11   100      WCPVM_MDS            ***     /oracle/db/oradata/orcl/WCPVM_mds.dbf
12   400      WCPVM_OCS            ***     /oracle/db/oradata/orcl/WCPVM_ocs.dbf
13   500      WCPVM_IAS_ACTIVITY   ***     /oracle/db/oradata/orcl/WCPVM_iasactivities.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    87       TEMP                 32767       /oracle/db/oradata/orcl/temp01.dbf
2    100      WCPVM_IAS_TEMP       32767       /oracle/db/oradata/orcl/WCPVM_iastemp.dbf
3    100      WCPVM_OCS_TEMP       150         /oracle/db/oradata/orcl/WCPVM_ocstemp.dbf

Comments

Post a Comment

Popular posts from this blog

Oracle Historical Session Information with ASH >10g

Image
In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time: You can use this 2 views to obtain this information: v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour. v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types: set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ; for example, if you are looking for metrics related with connected sessions to database, you can do: SQL> set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo
Read more

Check sessions and processes limits in Oracle

Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit: set linesize 190 set pages 200 select * from gv$resource_limit; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE ---------- ------------------------------ ------------------- --------------- --------------- ------------ 1 processes 44 300 300 300 1 sessions 52 215 512 512 1 enqueue_locks 30 295 6480 6480 1 enqueue_resources 24 165 2472 UNLIMITED 1 ges_procs 0 0 0 0 1 ges_ress 0 0
Read more

Purging and archiving Oracle alert.log and listener.log

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files
Read more