ORA-16000: database open for read-only access when logging into replicated active dataguard database

Users encountered this error when connecting to a version 11.1.0.7.0 replicated active data guard database via ODBC:

“Error Text ”[IBM][ODBC Oracle Wire Protocol driver][Oracle]
ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access”.
The error has the following diagnostic information: SQL State ”HY000”
SQL Native Error Code ‘604’ SQL Error Text ”[IBM][ODBC Oracle Wire Protocol driver][Oracle]
ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access””

I verified the error when trying to log in to the same database with identical user information.

Earlier in the day, I had examined that particular user’s information and privileges using a cloning script executed from the primary database in the data guard pair. This script has been used numerous times before in other environments without modifying the database it was executed against as it only performs ‘select’ queries.

I checked the replication status on the primary and replicated environment with several queries. The queries and their output are as follows:

Executed against the primary database:

select name
from v$database;
select sequence#, applied
from v$archived_log
where trunc(first_time) = trunc(sysdate)
order by 1;

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 19 16:16:44 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

 SEQUENCE# APPLIED
---------- ---------
     16863 NO
     16863 YES
     16864 NO
     16864 YES
     16865 NO
     16865 YES
     16866 NO
     16866 YES
     16867 NO
     16867 YES

45 rows selected.

NOTE – Each set of archived log has two application entries – one for the primary itself which is always a value of ‘NO’ for applied in this view and the other for the replicated environment. Since the value of the latest archived log is ‘YES’, this log has been applied to the replicated (secondary) database.

These next queries are executed against the replicated (secondary) database:

select sequence#, applied
from v$archived_log
where trunc(first_time) = trunc(sysdate)
order by 1;

select process, sequence#, status
from v$managed_standby;

 SEQUENCE# APPLIED
---------- ---------
     16863 YES
     16864 YES
     16865 YES
     16866 YES
     16867 YES

15 rows selected.

PROCESS    SEQUENCE# STATUS
--------- ---------- ------------
ARCH	       16867 CLOSING
ARCH		   0 CONNECTED
ARCH	       16865 CLOSING
ARCH	       16866 CLOSING
MRP0	       16868 WAIT_FOR_LOG
RFS		   0 IDLE
RFS		   0 IDLE
RFS	       16868 IDLE
RFS		   0 IDLE

9 rows selected.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 
SQL> 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

The first query is simply verifying that the archived logs have been applied. The second query let’s me know what the data guard processes are doing. Looks like its waiting for the next log from the primary.

My next thought was to restart the replication process on the replicated database, but since this database was already in sync, this action would likely have done no good. I thought next to refresh the password of the user using the output obtained from the earlier execution of the cloning script I mentioned. I simply executed an ‘alter user’ statement on the primary changing the user’s password to the same value it currently was. I then executed ‘alter system switch logfile’ a few times to ensure propagation of the change to the replicated environment. This fixed the problem. Post-problem research did not yield any similar situations, possible causes, or bugs. I guess I will have to be satisfied with being able to fix this the next time it happens.

The contents of the user cloning script are included here for reference. This script has proven very useful and has been proven in versions from 10.2.0.4.0 to 11.2.0.4.0.

-- userclone.sql
-- 10/17/00 J. Adams
-- Purpose: Generates a SQL script to create a new user 
--          based on the priviledges of another user
--
-- Based upon this script...
-- Date Created:   June 20, 2000
-- Author: Mark Huber
-- Modified -
-- 4/24/02 J. Adams - Corrected erroneous quota grant statement
-- 7/14/03 J. Adams - Modified erroneous role section (there is no
--                    1 or 0 in the admin_option column).
-- 6/3/05  J. Adams - Fixed problem with object privileges not being displayed.
-- 12/5/05 J. Adams - Altered script to obtain password of original user for cloned user.
-- 10/13/09 J. Adams - Altered create user query to include Oracle version 11g

clear screen;
clear breaks;
clear buffer;

set verify off;
set heading off;
set feedback off;
set linesize 150;

Accept user_in prompt "Enter the USER To create LIKE ..... "
Accept new_user_in prompt "Enter the NEW USER To create ..... "
--Accept new_user_pw_in prompt "Enter the NEW USER Password ..... "
Accept outfile prompt  "Enter the Output filename including path: "

col username noprint;
col lne newline;

spool &&outfile

col username noprint
col lne newline

-- ** create user
SELECT  username, 'CREATE USER '||UPPER('&&new_user_in')||' '||
        DECODE(nvl(a.password,spare4), 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
              'IDENTIFIED BY VALUES '||''''||nvl(a.password,spare4)||''''||' ') lne, 
        'DEFAULT TABLESPACE '||b.default_tablespace lne,
        'TEMPORARY TABLESPACE '||b.temporary_tablespace||';' lne
FROM    sys.USER$ a, dba_users b
where a.name = b.username
AND USERNAME = UPPER('&&user_in') 
/  

-- **create users tablespace quotas
SELECT username, 'ALTER USER '||upper('&&new_user_in')||' QUOTA '||
       DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
       ||' ON '||tablespace_name||';' 
  FROM DBA_TS_QUOTAS
WHERE USERNAME = UPPER('&&user_in')
/

-- ** create user roles
select          grantee     username,
        'Grant '||GRANTED_ROLE||' to'||' '||
        upper('&&new_user_in')||' '||
        DECODE(admin_option,NULL,null||';',
                                 'NO',null||';',
                                 'YES',' WITH GRANT OPTION;',
                                 null||';')
from        dba_role_privs
where       grantee =   upper('&&user_in')
/

-- ** create user system privs
SELECT          'Grant '||C.Name||' to'||' '||
        upper('&&new_user_in')||' '||
                DECODE(B.Option$,NULL,null||';',
                                 0,null||';',
                                 1,' WITH GRANT OPTION;',
                                 null||';') 
FROM   SYS.SYSAUTH$ B , SYSTEM_PRIVILEGE_MAP C , SYS.USER$ D
WHERE  D.Name           =   upper('&&user_in')      AND
       D.User#          =       B.Grantee#      AND
       B.Privilege#     =       C.Privilege 
/

-- ** create object privs
SELECT          'Grant '||C.Name||' ON '||
        A.Owner||'.'||A.Object_name||
        ' TO '||upper('&&new_user_in')||
        DECODE(B.Option$,NULL,null||';',
                                 0,null||';',
                                 1,' WITH GRANT OPTION;',
                                 null||';') 
FROM   ALL_OBJECTS A , SYS.OBJAUTH$ B , TABLE_PRIVILEGE_MAP C , SYS.USER$ D
WHERE  
       D.Name           =   upper('&&user_in')     AND
       D.User#          =       B.Grantee#  AND
       A.Object_Id      =       B.Obj#      AND
       B.Privilege#     =       C.Privilege 
/


-- Create any synonyms owned by cloned user

select 'create synonym '||upper('&&new_user_in')||'.'||synonym_name||' for '||
table_owner||'.'||table_name||';'
from dba_synonyms
where owner = upper('&&user_in')
order by synonym_name
/

spool off;

set verify on;
set heading on;
set feedback on;
Advertisements

Method-R Profiler – Find out where the time goes!

If anyone has spent time supporting a database in a client-server environment, you know the user cries of ‘the system is slow’ and it is up to you avoid being labeled guilty by proving that it is not a database issue.  This would involve pulling up AWR reports and looking at system stats that usually proves it is not a database issue because the database is virtually idle.  This type of back and forth is all too common and I have lost count the number of times it has happened to me.

It would be great to have some proof that the users could understand that would clear the DBA’s good name. Enter the Method-R Profiler, part of the Method-R Workbench set of tools. You can find the tools and information here.

The Method-R Profiler, named profiler from this point on, is a tool you can drag-and-drop Oracle trace files into and out comes very detailed HTML output that show where the database spent its time.

Using this tool, I was able to diagnose a problem in an environment where the code in question was issuing hundreds of database calls resulting in no data returned and the majority of them being duplicate calls.  The number of calls increased exponentially for every user connection, which explained the complaint concerning the more users connecting, the slower the environment.  In another situation, I used the profiler to determine that over 90% of time spent in an application was used for data fetches across the network with a default Java cache size.  Increasing the cache size sped up the system.

The profiler is based on methodology from an Oracle book that should be on every DBA’s bookshelf.  You can find that book here.  This information has been revised in this book.

Update 4/16/15

Another time when the profiler would come in handy is when a developer called to ask my help on a problem they were having with stored procedure calls through PHP.  They were making identical calls to two databases of the same version with the same init parameters running on the same server.  The only differences being that a call to one database was virtually instantaneous and the other took 20 seconds.  The one taking 20 seconds had been refreshed days ago with production data while the other had not.  After being assured by the developer that a difference in data could not possibly be the cause, I prepared to trace his session and use the profiler to determine where the 20 seconds was being spent.  However, when I called the developer to announce that I was ready to trace his session, he informed me that he had been running the wrong stored procedure.  After slapping my forehead several times, I decided to update this post as a means to demonstrate another situation where the profiler would be useful and to vent a bit.

11.1.0.7.0 RAC – Failure 1 contacting Cluster Synchronization Services daemon

After a recent storage migration, a version 11.1.0.7.0 two-node cluster would not start after reboot.  I performed a ‘crsctl start crs’ command as root and no services were started after several minutes.  Although I suspected something had gone wrong with the storage migration, I needed more proof.  I then check the crs as root:

# /u01/oracle/product/crs/bin/crsctl check crs
Failure 1 contacting Cluster Synchronization Services daemon
Cannot communicate with Cluster Ready Services
Cannot communicate with Event Manage

After a quick search, I found a link to a site that had good information on this problem here.  I will paraphrase a bit of the information I found there.

Check the processes:

# ps -aef | grep "init\."
root      4124     1  0 12:08 ?        00:00:00 /bin/sh /etc/init.d/init.evmd run
root      4125     1  0 12:08 ?        00:00:00 /bin/sh /etc/init.d/init.cssd fatal
root      4126     1  0 12:08 ?        00:00:00 /bin/sh /etc/init.d/init.crsd run
root      4710  4124  0 12:08 ?        00:00:00 /bin/sh /etc/init.d/init.cssd startcheck
root      5031  4125  0 12:08 ?        00:00:00 /bin/sh /etc/init.d/init.cssd startcheck
root      5289  4126  0 12:08 ?        00:00:00 /bin/sh /etc/init.d/init.cssd startcheck

The ‘startcheck’ processes are a good sign indicating that the crs is not disabled.  If the crs was disabled, these processes would not be present.  You can enable and disable the crs by executing the ‘crsctl <enable/disable> crs’ as root.  Disabling the crs to prevent it from starting automatically is a good precaution when the underlying OS needs to be patched or maintenance occurs that could potentially cause multiple reboots of the nodes.

At this point most DBA’s would proceed to the log files in the CRS_HOME, but Surachat Opun in the URL above suggested reviewing the /var/log/messages file on one of the nodes.  Viewing this log, I found many messages similar to the following:

Apr  3 12:11:59 oratest01 logger: Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.5289.

Checking one of these files under /tmp revealed more information:

Oracle Cluster Registry initialization failed accessing Oracle Cluster Registry device: PROC-26: Error while accessing the physical storage

So, the problem was with the OCR.  I viewed the contents of the /etc/oracle/ocr.loc file to find where the cluster was trying to find the OCR:

ocrconfig_loc=/dev/raw/raw101
ocrmirrorconfig_loc=/dev/raw/raw201
local_only=FALSE

I then reviewed the individual devices:

root@dfw1wui1 [/etc/init.d]
# ll /dev/raw/raw101
crw------- 1 oracle dba 162, 101 Mar 11 19:07 /dev/raw/raw101

# ll /dev/raw/raw201
ls: /dev/raw/raw201: No such file or directory

Notice the use of raw devices that is no longer supported in newer versions of Oracle.  Ok, it can’t find the OCR mirror location.  Fortunately, I was working with a system engineer during the migration and was able to rely on their assistance.   The problem was a typo for the second disk in the rc.local file.  Once the system engineer corrected the typo and rebooted the nodes, the cluster came up automatically as expected.

Thanks to Surachat Opun’s information, I found out something new concerning the ‘startcheck’ services and that Oracle was writing error files to /tmp.  I also learned a new method of troubleshooting cluster issues.  Previously, I would have gone to the CRS_HOME logs after determining that the cluster would not start, but going to the /var/log/messages file may have saved me some time in coming to the same conclusion.

Oracle 12c Data Redaction Flaw

There is a nice new feature in Oracle 12c that allows you to mask data during query parse time and does not need to have virtual private database, label security, or database vault configured. All it takes is a simple policy based on the named account as I will demonstrate using version 12.1.0.2.  However, there is a flaw in this feature that I will show at the end.

1)    I created test users – SUPERVISOR and WORKER
2)    I created a policy to restrict the contents of the SALARY column in the HR.EMPLOYEES table to anyone who is not the SUPERVISOR with this code:

set serveroutput on
BEGIN
DBMS_REDACT.ADD_POLICY(OBJECT_SCHEMA=>'HR',object_name=>'EMPLOYEES',policy_name=>'POLITICA_TESTE',expression=>'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SUPERVISOR''');
DBMS_REDACT.ALTER_POLICY(OBJECT_SCHEMA=>'HR',object_name=>'EMPLOYEES',policy_name=>'POLITICA_TESTE',action=>DBMS_REDACT.ADD_COLUMN,column_name=>'"SALARY"',function_type=>DBMS_REDACT.FULL);
END;
/

3)     I then queried the HR.EMPLOYEES table as SUPERVISOR

SQL> select employee_id, last_name, first_name, salary
from hr.employees
where salary between 10000 and 15000
order by last_name;
2    3    4
EMPLOYEE_ID LAST_NAME                 FIRST_NAME               SALARY
----------- ------------------------- -------------------- ----------
174 Abel                      Ellen                     11000
204 Baer                      Hermann                   10000
169 Bloom                     Harrison                  10000
148 Cambrault                 Gerald                    11000
147 Errazuriz                 Alberto                   12000
108 Greenberg                 Nancy                     12008
201 Hartstein                 Michael                   13000
205 Higgins                   Shelley                   12008
156 King                      Janette                   10000
168 Ozer                      Lisa                      11500
146 Partners                  Karen                     13500
114 Raphaely                  Den                       11000
145 Russell                   John                      14000
150 Tucker                    Peter                     10000
162 Vishney                   Clara                     10500
149 Zlotkey                   Eleni                     10500

16 rows selected.

4)    Now as WORKER:

SQL> select employee_id, last_name, first_name, salary
from hr.employees
where salary between 10000 and 15000
order by last_name;
2    3    4
EMPLOYEE_ID LAST_NAME                 FIRST_NAME               SALARY
----------- ------------------------- -------------------- ----------
174 Abel                      Ellen                         0
204 Baer                      Hermann                       0
169 Bloom                     Harrison                      0
148 Cambrault                 Gerald                        0
147 Errazuriz                 Alberto                       0
108 Greenberg                 Nancy                         0
201 Hartstein                 Michael                       0
205 Higgins                   Shelley                       0
156 King                      Janette                       0
168 Ozer                      Lisa                          0
146 Partners                  Karen                         0
114 Raphaely                  Den                           0
145 Russell                   John                          0
150 Tucker                    Peter                         0
162 Vishney                   Clara                         0
149 Zlotkey                   Eleni                         0

16 rows selected.

Notice anything interesting?  Even though I cannot see the actual values of the salary column, I can still narrow the search via comparison operators to get the same results.  This is the flaw.  You can use comparison and equivalence operators to not only get values in the ballpark but even get the exact value:

SQL> SQL> connect supervisor/supervisor
Connected.
SQL> select employee_id, last_name, first_name, salary
from hr.employees
where salary = 14000
order by last_name;

2    3    4
EMPLOYEE_ID LAST_NAME                 FIRST_NAME               SALARY
----------- ------------------------- -------------------- ----------
145 Russell                   John                      14000

SQL> SQL> connect worker/worker
Connected.
SQL> select employee_id, last_name, first_name, salary
from hr.employees
where salary = 14000
order by last_name;

2    3    4
EMPLOYEE_ID LAST_NAME                 FIRST_NAME               SALARY
----------- ------------------------- -------------------- ----------
145 Russell                   John                          0

This feature may be a good fit for other situations, but it will not stop a user with a little SQL knowledge and the ability to adhoc query the database.

More information on this feature is here:

http://www.oracle.com/technetwork/articles/database/data-redaction-odb12c-2331480.html

Disabling Diagnostic and Tuning Pack and AWR (10g and 11g)

The Oracle Tuning and Diagnostics is a very useful feature.  Unfortunately, it is a separately licensed feature and its price point can be discouraging.  For Oracle version 11g, the diagnostic and tuning package and AWR are enabled by default. We can disable them and save the extra license cost by setting the init parameter CONTROL_MANAGEMENT_PACK_ACCESS to NONE and Install the Oracle package to disable the AWR snapshots.

Example AWR report for TEST db on HOST01 in Oracle 11g,

 SQL> @awrrpt
 Instance DB Name Snap Id Snap Started Level
---------------------------------------------
 TEST TEST 3829 11 Mar 2013 08:00 1
 3830 11 Mar 2013 09:00 1
 3831 11 Mar 2013 10:00 1
 3832 11 Mar 2013 11:00 1
 3833 11 Mar 2013 12:00 1
 3834 11 Mar 2013 13:00 1
 3835 11 Mar 2013 14:00 1
 Specify the Begin and End Snapshot Ids
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Enter value for begin_snap:

1.To check the current init parameter value:

 SQL> show parameter control_management_pack_access
 NAME TYPE VALUE
----------------------------------------------
 control_management_pack_access string DIAGNOSTIC+TUNING

2. Adjust the parameter to disable tuning and diagnostics.  This parameter is dynamic, so a database restart is unnecessary.

 SQL> ALTER SYSTEM SET control_management_pack_access=NONE scope=both;
 System altered.

3. Verify the parameter value again after disable command:

 SQL> show parameter control_management_pack_access
 NAME TYPE VALUE
----------------------------------------------
 control_management_pack_access string NONE

4. Install the package to disable AWR snapshots by downloading the package from MOS 1909073.1 and installing it as SYSDBA, then executing it as SYS from SQL*Plus:

SQL> @dbmsnoawr.plb
 Package created.
 Package body created.
 
SQL> begin dbms_awr.disable_awr();
 2 end;
 3 /
 PL/SQL procedure successfully completed.

5. Verify AWR again after disabling

(This check was done after 15:00 and the last snapshot is still 14:00, so AWR snapshots will not be taken anymore in DB level.)

 SQL> @awrrpt
 3834 11 Mar 2013 13:00 1
 3835 11 Mar 2013 14:00 1
 
Specify the Begin and End Snapshot Ids
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

6. Optional – Execute ~/rdbms/admin/catnoawr.sql to remove all the AWR repository objects to free up space in the default tablespace where the AWR objects were located.

Controlling Diagnostic and Tuning Pack Usage and Disabling AWR for Oracle 10g

1. There is no need to change any init parameter. Just perform the process under step 4 above
from SQL*Plus to disable AWR:

SQL> @dbmsnoawr.plb
 Package created.
 Package body created.
 
SQL> begin dbms_awr.disable_awr();
 2 end;
 3 /
 PL/SQL procedure successfully completed.

2. Verify AWR again after disabled

(This check was done after 15:00 and the last snapshot is still 14:00, so AWR snapshots will not be taken anymore in DB level.)

 SQL> @awrrpt
 3834 11 Mar 2013 13:00 1
 3835 11 Mar 2013 14:00 1
 
Specify the Begin and End Snapshot Ids
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3. Optional – Execute ~/rdbms/admin/catnoawr.sql to remove all the AWR repository objects to free up space in SYSAUX

The Orphan of Backups

Recently I was privileged to participate in a major storage outage where not only did an
entire network storage appliance fail, but the data located on it was lost as well. While this
may sound a nightmare to most IT professionals, it was also a once-in-a-lifetime
opportunity to verify our various recovery strategies. Too often backup procedures
are put in place and not tested.

The majority of the data lost was designated for test and development databases and
in most cases, our recovery strategies worked. But, one of the items we were missing in
most of the recovery strategies was a backup copy of the database spfile. We had stored our spfiles in an ASM diskgroup and it was lost with the rest of the ASM data. The
Oracle Recovery Manager (RMAN) has the capability to backup the spfile with the
rest of the database files, but it needs some settings other than the defaults.

So, while I am snowed-in at home this weekend, I thought I would share my
experience in setting up spfile backups. According to Oracle documentation,
to backup the spfile automatically, all you need to do is enable controlfile
autobackup, like this:

[oracle@rhel1 scripts]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Feb 28 11:33:30 2015

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

connected to target database: JON11G (DBID=856759094)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name JON11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$BACKUPDIR/snapcf_LVL0_${ORACLE_SID}.f';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backups/JON11G/snapcf_LVL0_JON11G.f';

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name JON11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$BACKUPDIR/snapcf_LVL0_${ORACLE_SID}.f';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/backups/JON11G/snapcf_LVL0_JON11G.f';

RMAN>

I use these RMAN commands to backup my database:

run  {
configure snapshot controlfile name to '$BACKUPDIR/snapcf_LVL0_${ORACLE_SID}.f';
allocate channel dsk_1 type disk;
allocate channel dsk_2 type disk;
backup incremental level=0 filesperset=1
database format '$BACKUPDIR/%d_LVL0_%T_s%s_p%p_%u';
sql 'alter system archive log current';
backup
archivelog all
format '$BACKUPDIR/%d_ARCH_%T_s%s_p%p_%u'
delete input;
backup current controlfile format '$BACKUPDIR/%d_cntrl_LVL0_%T_s%s_p%p_%u';
release channel dsk_1;
release channel dsk_2;
}

After executing a backup, I tried to recover my spfile from my latest backup:

[oracle@rhel1 scripts]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Feb 28 11:43:56 2015

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

connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initJON11G.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2224584 bytes
Variable Size                 92278328 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5439488 bytes

RMAN> set DBID=856759094;

executing command: SET DBID

(Setting the DBID is a MUST since there is no controlfile in play yet)

RMAN> restore spfile;

Starting restore at 28-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/28/2015 11:45:17
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

(Ok, I will use ‘from autobackup’)

RMAN> restore spfile from autobackup;

Starting restore at 28-FEB-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150228
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150227
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150226
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150225
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150224
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150223
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150222
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/28/2015 11:45:28
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN>

What? I set the controlfile autobackup to ‘ON’ and the spfile backup is
not in my backup set? As a side note, unless you specify MAXDAYS during the
restore spfile command, it will go back seven days by default.

So, where did my spfile backup go? The backup goes to the AUTOBACKUP
location, which in this case was the db_recovery_file_dest value from
my spfile. In the absence of this value, the spfile backup will go
to the $ORACLE_HOME/dbs location. I looked in my FRA location and
found these files:

[oracle@rhel1 autobackup]$ ls
2015_02_26  2015_02_27  2015_02_28
[oracle@rhel1 autobackup]$ cd 2015_02_28
[oracle@rhel1 2015_02_28]$ ll
total 19232
-rw-r----- 1 oracle oinstall 9830400 Feb 28 11:43 o1_mf_s_872854987_bh3zkd9r_.bkp
-rw-r----- 1 oracle oinstall 9830400 Feb 28 11:43 o1_mf_s_872854996_bh3zknpg_.bkp

I plugged one of these file names into my restore command and it worked:

RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/JON11G/autobackup/2015_02_28/o1_mf_s_872854987_bh3zkd9r_.bkp';

Starting restore at 28-FEB-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/JON11G/autobackup/2015_02_28/o1_mf_s_872854987_bh3zkd9r_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 28-FEB-15

RMAN> startup force;

Oracle instance started
database mounted
database opened

Total System Global Area    2071076864 bytes

Fixed Size                     2228032 bytes
Variable Size               1191182528 bytes
Database Buffers             872415232 bytes
Redo Buffers                   5251072 bytes

RMAN>

Wanting to keep all my backups in the same place, especially since that location has
built-in redundancy, I need to include my spfile backup commands with my other backup
commands:

run  {
configure snapshot controlfile name to '$BACKUPDIR/snapcf_LVL0_${ORACLE_SID}.f';
allocate channel dsk_1 type disk;
allocate channel dsk_2 type disk;
backup incremental level=0 spfile format '$BACKUPDIR/spfile_%d_%s_%T.bak'filesperset=1
database format '$BACKUPDIR/%d_LVL0_%T_s%s_p%p_%u';
sql 'alter system archive log current';
backup
archivelog all
format '$BACKUPDIR/%d_ARCH_%T_s%s_p%p_%u'
delete input;
backup current controlfile format '$BACKUPDIR/%d_cntrl_LVL0_%T_s%s_p%p_%u';
release channel dsk_1;
release channel dsk_2;
}

These changes worked to create an spfile backup in my designated backup location
with a name of my choosing:

[oracle@rhel1 2015_02_28]$ ll /backups/JON11G/
total 1102460
drwxr-xr-x 2 oracle oinstall      4096 Feb 28 12:09 HIST
-rw-r----- 1 oracle oinstall    459776 Feb 28 12:09 JON11G_ARCH_20150228_s66_p1_22q0devf
-rw-r----- 1 oracle oinstall      2560 Feb 28 12:09 JON11G_ARCH_20150228_s67_p1_23q0devf
-rw-r--r-- 1 oracle oinstall      5434 Feb 28 12:09 JON11G_backup.log
-rw-r----- 1 oracle oinstall   9797632 Feb 28 12:09 JON11G_cntrl_LVL0_20150228_s68_p1_24q0devh
-rw-r----- 1 oracle oinstall   9748480 Feb 28 12:09 JON11G_controlfile.trc
-rw-r----- 1 oracle oinstall 698966016 Feb 28 12:08 JON11G_LVL0_20150228_s62_p1_1uq0deuk
-rw-r----- 1 oracle oinstall 398934016 Feb 28 12:08 JON11G_LVL0_20150228_s63_p1_1vq0deuk
-rw-r--r-- 1 oracle oinstall      1776 Feb 28 12:09 JON11G_restore.cmd
-rw-r----- 1 oracle oinstall   9748480 Feb 28 12:09 snapcf_LVL0_JON11G.f
-rw-r----- 1 oracle oinstall     98304 Feb 28 12:09 spfile_JON11G_64_20150228.bak

Simple and effective