ORA-15040: diskgroup is incomplete

I received this error while I was trying to bring up cluster on the second in my 2-node cluster.

When I tried to start cluster, ora.asm resource was found in intermediate state.

[root@host02 ~]# crsctl start cluster
CRS-5702: Resource 'ora.evmd' is already running on 'host02'
CRS-2800: Cannot start resource 'ora.asm' as it is already in the INTERMEDIATE state on server 'host02'
CRS-4000: Command Start failed, or completed with errors.

– Since asm was not up, OCR could not be read and hence cluster could not come up

[root@host02 ~]# crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  INTERMEDIATE host02                   OCR not started

– The alert log showed that DATA diskgroup could not be mounted.

2013-09-04 13:55:38.896
[/u01/app/11.2.0/grid/bin/oraagent.bin(28674)]CRS-5019:All OCR locations are on ASM disk groups [DATA], and none of these disk groups are mounted. Details are at “(:CLSN00100:)” in “/u01/app/11.2.0/grid/log/host02/agent/ohasd/oraagent_grid/oraagent_grid.log”.

– I tried to mount DATA diskgroup using SQL but I got ORA-15040 which indicated that disk “0” could not be read

SQL> alter diskgroup data mount;
alter diskgroup data mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "0" is missing from group number "1" 

– When I listed ASM disks on second node, ASMDISK01 was not listed

[root@host02 bin]# oracleasm listdisks
ASMDISK010
ASMDISK011
ASMDISK012
ASMDISK013
ASMDISK014
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
ASMDISK07
ASMDISK08
ASMDISK09

– I issued oracleasm scandisks which led to discovery of ASMDISK01

[root@host02 bin]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "ASMDISK01"

– Now when I issued oracleasm listdisks, ASMDISK01 was also listed.

[root@host02 bin]# oracleasm listdisks
ASMDISK01
ASMDISK010
ASMDISK011
ASMDISK012
ASMDISK013
ASMDISK014
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
ASMDISK07
ASMDISK08
ASMDISK09

— Now I could mount DATA diskgroup successfully

SQL> alter diskgroup data mount;

Diskgroup altered.

Reference:http://oracleinaction.com/ora-15040/

Oracle RAC Instance not restarting automatically

Oracle RAC Instance not restarting automatically


Hi everybody,

we've got problems with 11.2 instances not restarting automatically. We've got a 2 node RAC, the Grid version is 11.2, 
but with 10.2 databases instances (asm instance in 11.2). All instances restart correctly so far. 
Recently, I created 2 new instances in version 11.2. Now when I restart the cluster, the 2 new instances (11.2) do not start automatically. 
Since I'm a newbie with rac, I don't find what's wrong. I've done some checks with srvctl, but since there's 2 oracle's home, I'm a bit confused.


Thanks for your suggestions.




Hi,


1. crsctl status res ora.orcl.db -p|grep -i AUTO_START
if result is "AUTO_START=never", execute step 3.  if result is restore: it behave in this way "Restores the resource to the same state that it was in when the server stopped". so you can still decided to change it to always.

2.  crsctl modify res ora.orcl.db -attr "AUTO_START=always"
srvctl modify database -d orcl -y automatic

Rgds
Satheesh



For the srvtctl I know that it must be started from the correct home. Satheesh, as you stated it, my AUTO_START was in "restore".
Since I shutdown both instances with srvctl before I restart the Linux cluster, the instances have stayed down. 


Thank you satheesh for solving my issue.

RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20035: invalid high RECID

RMAN backup error 

Solution :

After the refresh this error is normal, because the catalog database could not identify the database because it holds the old information. So in order to let the catalog database identify the database we have to unregister and register the database once again to identify the database.

$rman target=/ catalog=rman/rman@rmancatalog

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 13 09:20:30 2015

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

connected to target database: ORCLL (DBID=2353077066)

connected to recovery catalog database

RMAN> unregister database;

database name is "ORCL" and DBID is 2353077066

Do you really want to unregister the database (enter YES or NO)? YES

database unregistered from the recovery catalog

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN>

ORA-01008: not all variables bound while doing RMAN backup

ORA-01008: not all variables bound while doing RMAN backup

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 17 15:00:22 2012

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

RMAN>

connected to target database: MADM (DBID=xvxvxvxv)

RMAN>

connected to recovery catalog database

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

allocated channel: t1

channel t1: SID=1726 device type=SBT_TAPE

channel t1: Veritas NetBackup for Oracle - Release 6.5 (2009050106)

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 01/17/2012 15:00:36

RMAN-03014: implicit resync of recovery catalog failed

ORA-01008: not all variables bound

RMAN>

Recovery Manager complete.

I noticed that resync catalog or crosscheck archivelog all did not help in this case and this error appears due to a BUG in 11.2.0.2. You can read more about this issue at Oracle Metalink doc ID: 1280447.1

Current fix is to flush shared_pool and you will be able to start rman backup again.

SQL> alter system flush shared_pool;

RMAN error: RMAN-03022 and ORA-03114 error

I am getting this RMAN-03022 and ORA-03114 error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 19/13/2015 21:13:22
ORA-03114: not connected to ORACLE

How do I fix this ORA-03114 and RMAN-03002 error?

Option 1) Try flushing the shared as below and check whether it is working now.

Connect to the database and execute the below

sql>alter system flush shared_pool;

Once the shared pool is flushed RMAN should start working.

If the above doesn't help, try next step

Option 2) 

sql>alter system set cursor_sharing=exact scope=both;

Managing space in the PERFSTAT tablespace

You can create the following procedure in your perfstat schema if you want to delete
the old statistics and rebuild the objects to free up the space in PERFSTAT tablespace.

create or replace procedure perfstat_freespace is
 cursor c1 is
        SELECT table_name from user_tables ;
 cursor c2 is
        SELECT index_name from user_indexes ;
 l_str varchar2(200) ;
begin
  delete from stats$snapshot where snap_time < sysdate - 10 ;
  commit ;
  for i in c1 loop
     l_str := 'alter table '||i.table_name||' move ' ;
     execute immediate l_str ;
  end loop ;
  for i in c2 loop
     l_str := 'alter index '||i.index_name||' rebuild ' ;
     execute immediate l_str ;
  end loop ;
end ;

You can submit a job to execute the procedure every 10 day to delete the old statistics :

 declare
  jobno number;
 begin
   dbms_job.submit(:jobno, '  perfstat_freespace ;',
          trunc(sysdate+1) + 6/24, 'sysdate + 1');
   commit ;
 end ;

MRP0: Background Media Recovery terminated with error 1111

MRP0: Background Media Recovery terminated with error 1111

I noticed that Primary can transport archive log to standby but standby can not apply archive log and giving errors at standby alert.log such as:

MRP0: Background Media Recovery terminated with error 1111 and MRP0: Background Media Recovery process shutdown (PROD00DG)

On primary alert.log
————————
RC8: Archive log rejected (thread 1 sequence 75698) at host ‘PROD00DG_private_odm_izm’
FAL[server, ARC8]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance PROD001 – Archival Error. Archiver continuing.
I noticed that MPR has been stopped and standby.
On standby:
—————
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
at alert:
————–
Slave exiting with ORA-1111 exception
Errors in file /oracle11g/app/oracle/diag/rdbms/PROD00dg/PROD00DG/trace/PROD00DG_pr00_17891406.trc:
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
ORA-01157: cannot identify/lock data file 1285 – see DBWR trace file
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (PROD00DG)
at standby trace:
———————-
MRP0: Background Media Recovery terminated with error 1111
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
ORA-01157: cannot identify/lock data file 1285 – see DBWR trace file
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
*** 2012-12-01 19:41:03.428
Completed Media Recovery
Managed Recovery: Not Active posted.
*** 2012-12-01 19:41:04.133
Slave exiting with ORA-1111 exception
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
ORA-01157: cannot identify/lock data file 1285 – see DBWR trace file
ORA-01111: name for data file 1285 is unknown – rename to correct file
ORA-01110: data file 1285: ‘/oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285’
I have checked below query ouputs:
a.) select file#, error from v$recover_file;
b.) select file#, name, status from v$datafile;
Outputs are:
——————
SQL> select file#, error from v$recover_file;
FILE# ERROR
———- —————————–
1268
1269
1270
1275
1276
1277
1278
1279
1280
1281
1282
FILE# ERROR
———- ——————————
1283
1284
1285 FILE MISSING
SQL> select file#, name, status from v$datafile;
file# name status
—— ———– ———
1285 /oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285 RECOVER
After make some search, I have found MOS Recovering the primary database’s datafile using the physical standby, and vice versa [ID 453153.1]
A backup of the one datafile from the primary can be made and then used to restore on the standby database, as indicated in the following note:
The document walks you through the process starting about one-half way down, in the section titled:
“Recovering the Standby’s Datafile”
I followed below steps:
1. Backup related file at primary
On primary:
——————–
$ rman target /
RMAN> backup datafile 1285 format ‘/tmp/1285_pr.bk’ tag ‘PRIMARY_1285′;
2. Transfer the file to the standby site using an operating system utility such as scp, NFS, ftp etc
3. At the standby site, catalog the backuppiece and confirm it’s available for use:
On standby:
——————–
$ rman target /
RMAN> catalog backuppiece’/tmp/1285_pr.bk’;
RMAN> list backuppiece’/tmp/1285_pr.bk’;
RMAN> list backup of datafile 1285;
4. Stop redo apply on the physical standby database
On standby:
——————–
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
For my issue my redo apply has been already stopped.
5. On the standby site restore the datafile:
On standby:
——————–
$ rman target /
RMAN> restore datafile 1285;
At steps 5 I got error:
RMAN> restore datafile 1285;
Starting restore at 01-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=438 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/01/2012 21:46:50
RMAN-06085: must use SET NEWNAME command to restore datafile /oracle11g/app/oracle/11.2.0/dbs/UNNAMED01285
So I need to run below command for can restore datafile 1285:
RUN {
SET NEWNAME FOR DATAFILE 1285 to ‘+ORADATA’;
RESTORE DATAFILE 1285;
SWITCH DATAFILE 1285;
}
6. Check status of files:Restart redo apply on the physical standby database
On standby:
——————–
a.) select file#, error from v$recover_file;
b.) select file#, name, status from v$datafile;
On standby:
——————–
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
from log:
Successfully added datafile 1285 to media recovery
Datafile #1285: ‘+ORADATA/PROD00dg/datafile/tPRODspace_2012_ernst.1574.800921533’
Successfully added datafile 1285 to media recovery
Datafile #1286: ‘+ORADATA/PROD00dg/datafile/tPRODspace_2012_ernst.1574.800921533′
SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;
THREAD#    MAX(SEQUENCE#)
———-         ————–
1                        75677
2                         72871
Reference:
—————
Recovering the primary database’s datafile using the physical standby, and vice versa [ID 453153.1]
How to Recover from a Lost or Deleted Datafile with Different Scenarios [ID 198640.1]
MRP0: Background Media Recovery terminated with error 1274 [ID 739618.1]

ORA-00245: control file backup operation failed with RMAN-03009 error

ORA-00245: control file backup operation failed with RMAN-03009 error

We started to get ORA-00245: control file backup operation failed &
RMAN-03009: failure of Control File and SPFILE Autobackup command on C1 channel xxx errors 
after we have been enabled block change tracking to can take Incremental backup on our system.

Our current RMAN setting is:

RMAN > CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+ORADATA/PROD00/CONTROLFILE/snapcf_prod00.f';

Error message during Incremental(Level 1):

RMAN-03009: failure of backup command on C1 channel at 04/04/2012 08:50:55
ORA-00245: control file backup operation failed
continuing other job steps, job failed will not be re-run

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 04/04/2012 08:53:52

RMAN-03009: failure of backup command on C13 channel at 04/04/2012 08:50:55
ORA-00245: control file backup operation failed

After making some search We get below metalink document:

RMAN Controlfile Must Reside on Shared Device for RAC database in 11G [ID 1263621.1]

We changed our parameter as below:

RMAN > CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘+ORADATA/PBAL00/CONTROLFILE/snapcf_pbal00.f’;

Reference:
RMAN Controlfile Must Reside on Shared Device for RAC database in 11G [ID 1263621.1]
In RAC environment from 11.2 onwards Backup Or Snapshot controlfile needs to be in shared location [ID 1472171.1] 
Top RMAN 11.2 issues [ID 1473914.1]

Recreating the Spfile for RAC Instances

Recreating the Spfile for RAC Instances

In this post, I am going to explain how we can create Recreating the Spfile for RAC Instances.


By default init.ora file represent at $ORACLE_HOME/dbs path.
In this case the parameter file was saved to /u01/app/oracle/11.2.0/dbs/initPROD001.ora


1. Using this text based format of the parameter file after having corrected the parameters that have caused the issue, start one of the RAC instances to the mount phase.

SQL> startup mount pfile=’/u01/app/oracle/11.2.0/home/oracle/initPROD01.ora’;


2. The current location for spfile is +oradata/PROD00/spfilePROD00.ora so the new file will need to replace with this file. 

ASM itself stores the spfile in +oradata/PROD00/PARAMETERFILE/spfile.267.737949031 and links or aliases the spfile in the location +oradata/PROD00/spfilePROD00.ora


We can also check where spfile is in ASM diskgroup by using the ASMCMD command

For example(Do not forget to set ASM env):

ASMCMD [+oradata/PROD00] > ls -l
Type Redund Striped Time Sys Name
Y CHANGETRACKING/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfilePROD00.ora => +ORADATA/PROD00/PARAMETERFILE/spfile.267.737949031


3. Before start be ensure one of the database instances is mounted in RAC system to recreate the spfile.

SQL> select INSTANCE_NAME,HOST_NAME,STATUS from v$instance
INSTANCE_NAME HOST_NAME STATUS
—————- —————– ————
PROD001 orapdb11 mount


4. Create the new spfile

SQL> create spfile=’+oradata/PROD00/spfilePROD00.ora’ from pfile=’/u01/app/oracle/11.2.0/dbs/initPROD001.ora’;
File created.


5. ASMCMD will show that a new spfile has been created as the alias spfilePROD00.ora is now pointing to a new spfile under the PARAMETER directory in ASM.

ASMCMD> pwd
+oradata/PROD00

ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfilePROD00.ora =>
+oradata/PROD00/PARAMETERFILE/spfile.298.731252301


6. Shutdown the instance and restart the database using srvctl using the newly created spfile.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

[oracle@node1 ~]$ srvctl start database -d PROD00


7. ASMCMD will now show a number of spfiles exist in the PARAMETERFILE directory for this database.

 We need to remove old spfile .

ASMCMD> pwd
+oradata/PROD00

ASMCMD> cd PARAMETERFILE

ASMCMD> ls -l
Type Redund Striped Time Sys Name
ASMCMD [+oradata/PROD00/PARAMETERFILE] > ls -l
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE MAR 06 16:00:00 Yspfile.267.737949031
PARAMETERFILE UNPROT COARSE MAR 06 16:40:00 Yspfile.298.731252301

ASMCMD> rm spfile.267.737949031

ASMCMD> ls
spfile.298.731252301

Reference:
Recreating the Spfile for RAC Instances Where the Spfile is Stored in ASM [ID 554120.1]

CRS-4995: The command ‘Modify resource’ is invalid in crsctl. Use srvctl for this command

 I encountered above error message when I was trying to modify ACL of an ASM cluster file system created on volume VOL1 in DATA diskgroup as follows:

[root@host01 ~]# crsctl modify resource ora.data.vol1.acfs -attr "ACL='owner:root:rwx,pgrp:dba:rwx,other::r--'"

CRS-4995: The command 'Modify resource' is invalid in crsctl. Use srvctl for this command.

I resolved the above problem by using the unsupported flag as follows:

[root@host01 ~]# crsctl modify resource ora.data.vol1.acfs -attr "ACL='owner:root:rwx,pgrp:dba:rwx,other::r--'" -unsupported

How to resolve ORA-00490 error?

How to resolve ORA-00490 error?

Sometimes all of a sudden our Instance will be crashed and when looked at alert log file we found below error message

ORA-00490: PSP process terminated with error

While PSP process itself terminated due to any error the whole instance is crashed with ORA-00490 error message.

On further check you may found one more error also in the alert log file which describes as

ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

When operating system is encountering with some unknown error like insufficient space in temp Area or swap Area or insufficient system resources then Oracle throws above errors

Same time PMON is terminating instance with following error with process id of PMON process. Because Oracle processes are being unmanageable of Oracle database instance.

PMON: terminating instance due to error 490
Instance terminated by PMON, pid = 20094

Root Cause:

This error will occur when there is no free space available in swap area of System for spawning new process of Oracle. Due to this reason Process SPwaner process PSP0 (with ORA-00490 error code of Oracle) terminated because it doesn’t able to manage or create Oracle processes. Result is Oracle instance crashed by PMON process with errorstack 490 (which is pointing out ORA-00490). If lack of system resource found then also same situation can be occurring.

Solution :

There are 2 solutions for this problem which are mentioned below

1. Check your swap space and increase swap area in system. Because due to lack of space in swap are Oracle unable to create new process and PSP0 Process SPwaner is unable to manage Oracle process.

2. Check “ulimit” setting for Oracle. “ulimit” is for user shell limitation. If maximum shell limit is reached then also PSP0 process becomes unstable to manage other Oracle processes.Increase the “ulimit” setting for Oracle user.

ORA-16649: possible failover to another database prevents this database from being opened

ORA-16649: possible failover to another database prevents this database from being opened

We have two-node primary RAC database & two-node standby RAC database. For the business testing purpose, they will want both side of the database in the READ WRITE Mode. So we have decided to disabled the log shipping and made the standby database as a new primary database using failover database.

During the standby failover process, we made the existing primary database down. We have successfully failover the standby database as a new primary database. Once the process completed, we have disabled the DG configuration.

When we try to start the old primary database, we go the below error message. After analyzing further, we found the DG configuration file has been update from the standby database DG configuration file. Since the standby database become a new primary. So it has update the DG configuration file in the old primary database.

ORA-16649: possible failover to another database prevents this database from being opened

SOLUTION

After disabling the DG_BROKER_START=FALSE;  the old primary database able to start in the READ WRITE mode.

SQL> startup force

ORACLE instance started.

Total System Global Area 7.2958E+10 bytes

Fixed Size                  2235808 bytes

Variable Size            1.9059E+10 bytes

Database Buffers         5.3687E+10 bytes

Redo Buffers              209866752 bytes

Database mounted.

ORA-16649: possible failover to another database prevents this database from being opened

SQL> select database_role from v$database;

DATABASE_ROLE

----------------

PRIMARY

SQL> alter system set dg_broker_start=false scope=both sid='*';

System altered.

SQL> alter database open;

Database altered.

.patch_storage in RDBMS_HOME & GI_HOME

.patch_storage in RDBMS_HOME & GI_HOME

Can we delete .patch_storage in RDBMS_HOME & GI_HOME directoryy

Yes, Please follow the below document. read it very carefully before clearing the files

Is it necessary to keep the $ORACLE_HOME/.patch_storage directory after successful installation of a patch?

Yes, It is necessary to keep the $ORACLE_HOME/.patch_storage directory even after successful installation of a patch. 

Reason for ORACLE storing the patch binaries & patch information on .patch_storage directory

* When you apply an interim patch to an Oracle home, OPatch stores the patch information in $ORACLE_HOME/.patch_storage directory. Inside this directory, there are separate directories created for each patch applied to the Oracle home. Interim patches are bug fixes that are made available to customers in response to specific bugs. CPU (Critical Patch Update) is also a kind of. The latest CPU will rollback the previously applied CPU as CPU is cumulative. So this rollback information is from the .patch_storage directory.

* You may come across a bug conflict and might want to remove the conflicting patch during the patch installation. This process is known as patch rollback. During patch installation, OPatch saves copies of all the files that were replaced by the new patch before the new versions of these files are loaded and stores it in $ORACLE_HOME/.patch_storage/patch ID/. These saved files are called rollback files and are the key to making patch rollback possible. 

To Clean up the .patch_storage directory, oracle has come up with option called 

"opatch util cleanup"  option, please read the below document.

How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space. [ID 550522.1]

spfile in DB_UNKNOWN Directory - Oracle RAC

restored spfile in DB_UNKNOWN Directory

In the below scenario, I am placing the spfile correctly after the RMAN restored the spfile in the DB_UNKNOWN directory in the diskgroup.

This is oracle bug( Bug 5370663: RMAN RESTORES SPFILE IN ASM TO DB_UNKNOWN’)

Note: 1) we have to enable the autobackup mode in the RMAN to restore the spfile from the backup. If autobackup mode is not enabled then we can’t restore the spfile from backup

      2) I have started with one instance on the RAC database. Till I completed the restore, I will be performing all the activity from the same node. Keep the second instance down.

      3) Remove the init_.ora parameter file from the $ORACLE_HOME/dbs folder. This file will contain the old spfile details. When you start the database with the srvctl command, this file will be created automatically by the oracle agent in the $ORACLE_HOME/dbs folder.

We need to identify the DB_NAME, DBID & DB_UNIQUE_NAME details for recovering the database using RMAN

To start the database in the force mode, only DB_NAME is sufficient

oracle BHU_1 bhuora01> cat initBHU.ora

*.db_name='BHU'

*.db_unique_name='BHU_A'

Start the instance with the force option to the nomount stage

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:28:28 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount force pfile='/home/oracle/initBHU.ora';

ORACLE instance started.

Total System Global Area  304861184 bytes

Fixed Size                  2225872 bytes

Variable Size             159385904 bytes

Database Buffers          134217728 bytes

Redo Buffers                9031680 bytes

SQL> exit

Note: 1) you can identify the DBID from the level 0 or 1 backup log file

     2) Some time oracle writes the DBID in the alert log file

We have to connect to the database using rman and provide the DBID for the database. If you have the recovery catalog then there is no need to specify the DBID.

oracle BHU_1 bhuora01> rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jan 17 15:28:59 2012

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

connected to target database: BHU (not mounted)

RMAN> set dbid=263762951

executing command: SET DBID

RMAN> run {

ALLOCATE CHANNEL 'dev_1' type 'SBT_TAPE';

restore spfile to '+BHU_DATA1' from autobackup;

}

2> 3> 4>

using target database control file instead of recovery catalog

allocated channel: dev_1

channel dev_1: SID=175 device type=SBT_TAPE

channel dev_1: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_

Starting restore at 17-JAN-2012

channel dev_1: looking for AUTOBACKUP on day: 20120117

channel dev_1: AUTOBACKUP found: c-263762951-20120117-00

channel dev_1: restoring spfile from AUTOBACKUP c-263762951-20120117-00

channel dev_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 17-JAN-2012

released channel: dev_1

Spfile has been restored to the DB_UNKNOWN directory under the diskgroup which we specified in the restore

ASMCMD [+BHU_DATA1] > ls -lt

Type  Redund  Striped  Time             Sys  Name

                                        Y    BHU_A/

                                        Y    DB_UNKNOWN/

ASMCMD [+BHU_DATA1] > cd D*

ASMCMD [+BHU_DATA1/DB_UNKNOWN] > cd p*

ASMCMD [+BHU_DATA1/DB_UNKNOWN/PARAMETERFILE] > ls -lt

Type           Redund  Striped  Time             Sys  Name

PARAMETERFILE  UNPROT  COARSE   JAN 17 15:00:00  Y    SPFILE.271.772818033

Once the spfile is restored. We have create a new pfile from the restore spfile from the ASM diskgroup

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:42:08 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> create pfile='/home/oracle/BHU_afterbackup.ora' from spfile='+BHU_DATA1/DB_UNKNOWN/PARAMETERFILE/SPFILE.271.772818033';

File created.

Now, I am removing the DB_UNKNOWN directory from the diskgroup

Don’t remove before create a pfile from it.

ASMCMD [+BHU_DATA1] > ls -lt

Type  Redund  Striped  Time             Sys  Name

                                        Y    BHU_A/

                                        Y    DB_UNKNOWN/

ASMCMD [+BHU_DATA1] > ls

DB_UNKNOWN/

BHU_A/

ASMCMD [+BHU_DATA1] > rm -rf db*

We are stopping the database and Re-start the database with the newly created pfile.

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:43:30 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount pfile='/home/oracle/zs1_afterbackup.ora';

ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated

ORA-32006: STANDBY_ARCHIVE_DEST initialization parameter has been deprecated

ORACLE instance started.

Total System Global Area 1.4431E+10 bytes

Fixed Size                  2240272 bytes

Variable Size            3892314352 bytes

Database Buffers         1.0503E+10 bytes

Redo Buffers               34148352 bytes

SQL> create spfile='+BHU_DATA1' from pfile='/home/oracle/bhu_afterbackup.ora';

File created.

SQL>

We could see that the spfile has been created in the right directory structure.

ASMCMD [+BHU_DATA1/ZS2_A/PARAMETERFILE] > ls

spfile.268.772818375

+BHU_DATA1/ZS2_A/PARAMETERFILE/spfile.268.772818375

Now we are changing the configuration details in the server control

oracle BHU_1 bhuora01> srvctl modify database -d BHU_A -p +BHU_DATA1/BHU_A/PARAMETERFILE/spfile.268.772818375

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jan 17 15:48:07 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

If you start the database with sqlplus, Oracle can’t able to identify the spfile or pfile in the $ORACLE_HOME/dbs directory.

So we have to start the database with the srvctl command, which has the right spfile details.

If you start the database with the srvctl command, oracle creates pfile for the respective instance on the database.

SQL> startup nomount

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/oracle/BHU/112_64/dbs/initBHU_1.ora'

SQL> exit

oracle BHU_1 bhuora01> srvctl start database -d BHU_A -o nomount

oracle BHU_1 bhuora01> sqlplus / as sysdba

SQL> show parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string  +BHU_DATA1/BHU_a/parameterfile

                                                 /spfile.268.772818375