How to find the large log files older than one year to delete on unix?
To find the one year old large log files, execute the below command
find . -name '*.log' -size +200k -mtime +360 -print
where find is the command
. is for the current directory ( if you want to check on /var/log then specify the directory here)
'*.log' is to check for the log files
-size is to check for the size of the log files. I gave 200k
-mtime is to check for the time +360 for one year old files
-print is to show the files on the screen.
Thursday, 29 December 2011
Tuesday, 11 October 2011
Flashback Recovery Area Full - Unable to connect RMAN
Yesterday, There was an issue with the Flashback Recovery Area. The FRA disk is full and when we try to connect as "sys as sysdba" or "rman target", the database hangs.
If you are looking for a solution to similar scenario then read on...
Check the disk space for total, available and used space using df command in linux.
move to the archivelog folder in the flash recovery area for the database and check for the old logs.
If you have set the retention window to 7 days for example or have a backup taken 2-3 days before then you can remove the old logs to reclaim the disk space.
Now try to connect using the RMAN
rman target nocatalog
execute the crosscheck command to verify the backups and delete expired backups and logs.
Note, By removing the logs at OS level will not update the data dictionary hence the crosscheck command needs to be executed to cross verify the logs and files.
If you are looking for a solution to similar scenario then read on...
Check the disk space for total, available and used space using df command in linux.
move to the archivelog folder in the flash recovery area for the database and check for the old logs.
If you have set the retention window to 7 days for example or have a backup taken 2-3 days before then you can remove the old logs to reclaim the disk space.
Now try to connect using the RMAN
rman target nocatalog
execute the crosscheck command to verify the backups and delete expired backups and logs.
Note, By removing the logs at OS level will not update the data dictionary hence the crosscheck command needs to be executed to cross verify the logs and files.
Tuesday, 4 October 2011
How to create flashback restore point on database
How to create flashback restore point on database?
If the database is up, shutdown the database and restart the database in mount mode.
Check whether the flashback is on, If the flashback is not on then enable the flashback. Note: If you are using RAC then before enabling flashback make sure all other instances are shutdown
If you executed the flashback on command without shutting down the other instances, you will receive the below error
ERROR at line 1:
ORA-38777: database must not be started in any other instance.
Solution is to make sure all other instances are down.
After enabling the flashback, check the status to make sure the flashback is enabled on the database.
And check the DB_FLASHBACK_RETENTION_TARGET. By default it is 1440 which is one day. If you want for “n” no of days then the formula is 1440*n=value. For example if you want to retain for 3 days then 1440*3=4320
Alter the database to set the retention to 3 days.
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;
And then create the restore point as below
CREATE RESTORE POINT before_my_change;
Wednesday, 28 September 2011
PXE-E32: TFTP Open Timeout - VMWARE - REDHAT LINUX Installtion
PXE-E32: TFTP Open Timeout
When Installing Redhat Linux on the newly created vmware vm machine, I received the error TFTP open timeout error.
Casuse: TFTP open request was not acknowledged.
Solution:
Verify that the TFTP service is running. If you are not using the TFTP then simply wait for few minutes, the boot prompt will come back automatically after sometime with operating system not found message. Now select the operating system iso image.
When Installing Redhat Linux on the newly created vmware vm machine, I received the error TFTP open timeout error.
Casuse: TFTP open request was not acknowledged.
Solution:
Verify that the TFTP service is running. If you are not using the TFTP then simply wait for few minutes, the boot prompt will come back automatically after sometime with operating system not found message. Now select the operating system iso image.
Thursday, 22 September 2011
Linux - Find and delete large files in UNIX
On Red Hat Linux - To find the large files which could be removed to reclaim the space, execute the below command
find / -size +155M -printf "%s - %p\n"
The above command is executed on root directory to find the files which are more than 155MB in size. Remember you will receive permission denied error if you run as other than root. To execute into a particular folder specify the folder name. For example to find in the usr directory,
find /usr -size +155M -printf "%s - %p\n"
The above command can be modified to find in the current directory as below
find . -size +155M -printf "%s - %p\n"
find / -size +155M -printf "%s - %p\n"
The above command is executed on root directory to find the files which are more than 155MB in size. Remember you will receive permission denied error if you run as other than root. To execute into a particular folder specify the folder name. For example to find in the usr directory,
find /usr -size +155M -printf "%s - %p\n"
The above command can be modified to find in the current directory as below
find . -size +155M -printf "%s - %p\n"
Friday, 16 September 2011
ORA-01113: file 1 needs media recovery - system01.dbf
To clone a RAC DB to a new RAC DB
Login to the old RAC DB and shutdown the database and all nodes. Open the database in nomount state and take a controlfile backup to trace. Change the database to a single instance database by changing the cluster_database parameter to false and take a backup of the control file to trace.
Copy the datafiles and control file from trace into the new location and change the name and location of the new database.
When you open the database, you can experience the below error.
problem:
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/opt/oracle/test/system01.dbf'
If you specify recover database the below error will be shown as the controlfile is a backup controlfile.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
So specify the using backup controlfile clause.
SQL> recover database using backup controlfile;
ORA-00279: change 39930507 generated at 09/14/2011 13:52:54 needed for thread 2
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/TEST/archivelog/2011_09_14/o1_mf_2_127_%u_
.arc
ORA-00280: change 39930507 for thread 2 is in sequence #287
Now comes the another issue though you say cancel it will not cancel the media recovery to re-open the database.
The solution to recover the database is by using clause "until Cancel"
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
When asked for logs, as below
Specify log: {=suggested | filename | AUTO | CANCEL}
enter "cancel"
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
If you face the similar error then the above action could be one of your solution.
Login to the old RAC DB and shutdown the database and all nodes. Open the database in nomount state and take a controlfile backup to trace. Change the database to a single instance database by changing the cluster_database parameter to false and take a backup of the control file to trace.
Copy the datafiles and control file from trace into the new location and change the name and location of the new database.
When you open the database, you can experience the below error.
problem:
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/opt/oracle/test/system01.dbf'
If you specify recover database the below error will be shown as the controlfile is a backup controlfile.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
So specify the using backup controlfile clause.
SQL> recover database using backup controlfile;
ORA-00279: change 39930507 generated at 09/14/2011 13:52:54 needed for thread 2
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/TEST/archivelog/2011_09_14/o1_mf_2_127_%u_
.arc
ORA-00280: change 39930507 for thread 2 is in sequence #287
Now comes the another issue though you say cancel it will not cancel the media recovery to re-open the database.
The solution to recover the database is by using clause "until Cancel"
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
When asked for logs, as below
Specify log: {
enter "cancel"
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
If you face the similar error then the above action could be one of your solution.
Friday, 1 July 2011
Oracle11g_data_recovery_advisor
$ rman target devdb
Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 19 16:09:31 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: devdb (DBID=6959472717)
RMAN> list failure all;
using target database control file instead of recovery catalog
no failures found that match specification
RMAN> list failure all;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN> list failure critical;
no failures found that match specification
RMAN> list failure high;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
Impact: Some objects in tablespace TEST might be unavailable
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
Impact: See impact for individual child failures
List of child failures for parent failure ID 142
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
145 HIGH OPEN 19-MAY-11 Datafile 6: '/u01/oracle/oradata
/testdb/test01.dbf' is offline
Impact: Some objects in tablespace TEST might be unavailable
RMAN>
RMAN> change failure 148 priority low;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
Do you really want to change the above failures (enter YES or NO)? yes
changed 1 failures to LOW priority
RMAN>
By Default, Oracle11g RMAN "list failure" command lists only the critical and high priority alerts as shown below
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN>
We changed the priority of failure id 148 from high to low. Hence the failure id 148 are not shown for the list failure command.
To list the low priority failures, either use "list failure all" or "list failure low" commands.
Now to change back the priority of failure id 148 to high :
RMAN> change failure 148 priority high;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 LOW OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
Do you really want to change the above failures (enter YES or NO)? yes
changed 1 failures to HIGH priority
RMAN>
$ rman target dev1
Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 20 15:29:11 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: DEV1 (DBID=3359724377)
RMAN> repair failure
2> ;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 05/20/2011 15:29:33
RMAN-06954: REPAIR command must be preceded by ADVISE command in same session
RMAN> repair failure 148;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "integer": expecting one of: "noprompt, no, prev
iew, using, ;"
RMAN-01007: at line 1 column 16 file: standard input
RMAN> repair failure noprompt;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 05/20/2011 15:30:30
RMAN-06954: REPAIR command must be preceded by ADVISE command in same session
RMAN>
RMAN> advice failure high;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "advise, allocat
e, alter, backup, @, catalog, change, configure, connect, convert, copy, create,
crosscheck, delete, drop, duplicate, exit, flashback, grant, host, import, list
, mount, open, print, quit, recover, register, release, repair, replace, report,
reset, restore, resync, revoke, run, send, set, show, shutdown, spool, sql, sta
rtup, switch, transport, unregister, upgrade, validate, {, "
RMAN-01008: the bad identifier was: advice
RMAN-01007: at line 1 column 1 file: standard input
As shown above, list failure, advise failure and repair failure commands are interdependent. i.e they have to follow in sequence in the same session as shown below
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=76 device type=DISK
analyzing automatic repair options complete
Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
Mandatory Manual Actions
========================
1. If file /u01/oracle/oradata/testdb/test01.dbf was unintentionally renamed or
moved, restore it
2. If you have an export of tablespace TEST, then drop and re-create the tablesp
ace and import the data.
3. Contact Oracle Support Services if the preceding recommendations cannot be us
ed, or if they do not fix the failures selected for repair
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
no automatic repair options available
RMAN>
SQL> alter tablespace test online;
Tablespace altered.
RMAN> validate database;
Starting validate at 20-MAY-11
using channel ORA_DISK_1
RMAN-06169: could not read file header for datafile 6 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 05/20/2011 15:40:07
RMAN-06056: could not access datafile 6
RMAN> validate database;
Starting validate at 20-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/oracle/oradata/testdb/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/testdb/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/testdb/undotbs01.dbf
input datafile file number=00005 name=/u01/oracle/oradata/testdb/example01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/testdb/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/testdb/test01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 13131 90884 2087390
File Name: /u01/oracle/oradata/testdb/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 61902
Index 0 12538
Other 0 3309
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 20164 89630 2087388
File Name: /u01/oracle/oradata/testdb/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 21854
Index 0 17196
Other 0 30386
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 385 14080 2087390
File Name: /u01/oracle/oradata/testdb/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 13695
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 255 640 1000225
File Name: /u01/oracle/oradata/testdb/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 39
Other 0 255
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1688 12804 1987513
File Name: /u01/oracle/oradata/testdb/example01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6603
Index 0 1261
Other 0 3248
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 121 128 1958860
File Name: /u01/oracle/oradata/testdb/test01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 7
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 594
Finished validate at 20-MAY-11
RMAN>
Thank you for your patience in reading, If you could drop me a line giving your feedback - good, bad or any suggestions to improve would be much appreciated.
Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 19 16:09:31 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: devdb (DBID=6959472717)
RMAN> list failure all;
using target database control file instead of recovery catalog
no failures found that match specification
RMAN> list failure all;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN> list failure critical;
no failures found that match specification
RMAN> list failure high;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN> list failure detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
Impact: Some objects in tablespace TEST might be unavailable
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
Impact: See impact for individual child failures
List of child failures for parent failure ID 142
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
145 HIGH OPEN 19-MAY-11 Datafile 6: '/u01/oracle/oradata
/testdb/test01.dbf' is offline
Impact: Some objects in tablespace TEST might be unavailable
RMAN>
RMAN> change failure 148 priority low;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
Do you really want to change the above failures (enter YES or NO)? yes
changed 1 failures to LOW priority
RMAN>
By Default, Oracle11g RMAN "list failure" command lists only the critical and high priority alerts as shown below
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN>
We changed the priority of failure id 148 from high to low. Hence the failure id 148 are not shown for the list failure command.
To list the low priority failures, either use "list failure all" or "list failure low" commands.
Now to change back the priority of failure id 148 to high :
RMAN> change failure 148 priority high;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
148 LOW OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
Do you really want to change the above failures (enter YES or NO)? yes
changed 1 failures to HIGH priority
RMAN>
$ rman target dev1
Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 20 15:29:11 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: DEV1 (DBID=3359724377)
RMAN> repair failure
2> ;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 05/20/2011 15:29:33
RMAN-06954: REPAIR command must be preceded by ADVISE command in same session
RMAN> repair failure 148;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "integer": expecting one of: "noprompt, no, prev
iew, using, ;"
RMAN-01007: at line 1 column 16 file: standard input
RMAN> repair failure noprompt;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of repair command at 05/20/2011 15:30:30
RMAN-06954: REPAIR command must be preceded by ADVISE command in same session
RMAN>
RMAN> advice failure high;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "advise, allocat
e, alter, backup, @, catalog, change, configure, connect, convert, copy, create,
crosscheck, delete, drop, duplicate, exit, flashback, grant, host, import, list
, mount, open, print, quit, recover, register, release, repair, replace, report,
reset, restore, resync, revoke, run, send, set, show, shutdown, spool, sql, sta
rtup, switch, transport, unregister, upgrade, validate, {, "
RMAN-01008: the bad identifier was: advice
RMAN-01007: at line 1 column 1 file: standard input
As shown above, list failure, advise failure and repair failure commands are interdependent. i.e they have to follow in sequence in the same session as shown below
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
148 HIGH OPEN 19-MAY-11 Tablespace 7: 'TEST' is offline
142 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
offline
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=76 device type=DISK
analyzing automatic repair options complete
Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
168 HIGH OPEN 19-MAY-11 One or more non-system datafiles are
missing
Mandatory Manual Actions
========================
1. If file /u01/oracle/oradata/testdb/test01.dbf was unintentionally renamed or
moved, restore it
2. If you have an export of tablespace TEST, then drop and re-create the tablesp
ace and import the data.
3. Contact Oracle Support Services if the preceding recommendations cannot be us
ed, or if they do not fix the failures selected for repair
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
no automatic repair options available
RMAN>
SQL> alter tablespace test online;
Tablespace altered.
RMAN> validate database;
Starting validate at 20-MAY-11
using channel ORA_DISK_1
RMAN-06169: could not read file header for datafile 6 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of validate command at 05/20/2011 15:40:07
RMAN-06056: could not access datafile 6
RMAN> validate database;
Starting validate at 20-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/oracle/oradata/testdb/system01.dbf
input datafile file number=00002 name=/u01/oracle/oradata/testdb/sysaux01.dbf
input datafile file number=00003 name=/u01/oracle/oradata/testdb/undotbs01.dbf
input datafile file number=00005 name=/u01/oracle/oradata/testdb/example01.dbf
input datafile file number=00004 name=/u01/oracle/oradata/testdb/users01.dbf
input datafile file number=00006 name=/u01/oracle/oradata/testdb/test01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 13131 90884 2087390
File Name: /u01/oracle/oradata/testdb/system01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 61902
Index 0 12538
Other 0 3309
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 20164 89630 2087388
File Name: /u01/oracle/oradata/testdb/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 21854
Index 0 17196
Other 0 30386
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 385 14080 2087390
File Name: /u01/oracle/oradata/testdb/undotbs01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 13695
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 255 640 1000225
File Name: /u01/oracle/oradata/testdb/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 91
Index 0 39
Other 0 255
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1688 12804 1987513
File Name: /u01/oracle/oradata/testdb/example01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6603
Index 0 1261
Other 0 3248
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 121 128 1958860
File Name: /u01/oracle/oradata/testdb/test01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 7
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 594
Finished validate at 20-MAY-11
RMAN>
Thank you for your patience in reading, If you could drop me a line giving your feedback - good, bad or any suggestions to improve would be much appreciated.
Subscribe to:
Posts (Atom)