ACFS-9459: ADVM/ACFS is not supported on this OS version: ‘unknown’

Oracle Grid Infrastructure is supported on Operation System, but ACFS isn’t. Why?

OLYMPUS DIGITAL CAMERA

The Grid Infrastructure is a Software that use OS Library, so Grid Infrastructure isn’t Kernel dependent but OS Package dependent.

ACFS is a module of Grid Infrastructure that have drivers installed/configured  into OS Kernel, then ACFS is Kernel Dependent.

An Oracle ACFS file system is installed as a dynamically loadable vendor operating system (OS) file system driver and tool set that is developed for each supported operating system platform. The driver is implemented as a Virtual File System (VFS) and processes all file and directory operations directed to a specific file system.

The ACFS  is composed by three components:

The Oracle ACFS, Oracle Kernel Services (OKS) and Oracle ADVM drivers, they are dynamically loaded when the Oracle ASM instance is started.

  • Oracle ACFS
    This driver processes all Oracle ACFS file and directory operations.
  • Oracle ADVM
    This driver provides block device services for Oracle ASM volume files that are used by file systems for creating file systems.
  • Oracle Kernel Services Driver (OKS)
    This driver provides portable driver services for memory allocation, synchronization primitives, and distributed locking services to Oracle ACFS and Oracle ADVM.

Before upgrade your OS Kernel you must place into your check list the ACFS Drivers, as you do with others OS Drives.

How to Oracle support the ACFS into future Kernels?

By releasing Patch Set Updates (PSUs), they are proactive cumulative patches containing recommended bug fixes that are released on a regular and predictable schedule.

How it’s works?

Check this EXAMPLE below:

acfs_release_date

Note: This table is only a example where these kernel and dates are fictitious.

On image above we can see.

In jan/2016 the  GI 11.2.0.4 and 12.0.1.2 don’t need apply PSU because already have supported drives into base release, but 11.2.0.3 need PSU 11.2.0.3.2 to get supported drivers to kernel 2.6.32-100.

In Feb/2016 was released the kernel 2.6.65-30, so ACFS Deployment take some time until developers build new ACFS Drives. So, in FeB/2016 none release is supported for the kernel 2.6.65-30.

In Mar/2016 Oracle release the PSU where all ACFS versions was supported under that PSU.

With example above we can conclude: Does not matter what Grid Infrastructure base version  (such as 11.2.0.4 or 12.0.1.2 ) you are using,  what matter is, the Grid Infrastructure must have supported  ACFS Drivers for that Kernel.

Where to find Certification Matrix for ACFS ?

Oracle Support have a detailed MOS Note : ACFS Support On OS Platforms (Certification Matrix). (Doc ID 1369107.1)

You need ALWAYS check above mos note before any kernel updates for environments that have ACFS Configured.

 

Advertisements

News: Now is Supported BACKUPSET/ARCHIVELOG/DUMPSET files on ACFS 11.2.0.3

Starting with Oracle Automatic Storage Management 11g Release 2 (11.2.0.3), Oracle ACFS supports RMAN backups (BACKUPSET file type), archive logs (ARCHIVELOG file type), and Data Pump dumpsets (DUMPSET file type). Note that Oracle ACFS snapshots are not supported with these files.

 

ASM best practices we recommend to have two ASM disk groups:

DATAOH Diskgroup contains:
  • Database files (Database files must be stored directly in ASM and not on ACFS)
  • Oracle Clusterware files (OCR/Vote files)
  • ASM Spfile
  • ADVM volumes/ACFS filesystems for database related data or general-purpose use

 

FRA Diskgroup

This disk group will be used to store database recovery related files; such as archived log files, RMAN backups, and Flashback logs

  • Datapump dumpsets , RMAN backup files, etc. This was supported in ACFS 11.2.0.3 and above; however, ACFS does not currently support snapshots of filesystems housing  these files.
  • Additionally, database ORACLE_HOME backups (possibly zipped backups), can stored in a filesystem carved from the FRA disk group
ACFS Technical Overview and Deployment Guide [ID 948187.1]

http://docs.oracle.com/cd/E11882_01/server.112/e18951/asmfilesystem.htm#CACJFGCD

 

Enjoy…

 

 


CFGMGR fails: startacfsctl defacfsctl.bin: Dependent module libhasgen11.so could not be loaded.

Recently I faced an error when I execute the cfgmgr command on AIX.

root@aix:/ > cfgmgr
Method error (/usr/lib/methods/startacfsctl):
        0514-068 Cause not known.
Could not load program /usr/lib/methods/defacfsctl.bin:
        Dependent module libhasgen11.so could not be loaded.
Could not load module libhasgen11.so.
System error: No such file or directory

At startup  of AIX some services are not started automatically (eg: sshd and many others), the cause of issue is because “cfgmgr” command is returning error and it hold start of services.

At first sight I thought it was a AIX problem, but the lib “defacfsctl.bin” was not loaded because a module dependent ‘libhasgen11.so “was not loaded.

root@aix:/ >ldd /usr/lib/methods/defacfsctl.bin
/usr/lib/methods/defacfsctl.bin needs:
         /u01/app/grid/grid_has/lib/libhasgen11.so
         /u01/app/grid/grid_has/lib/libttsh11.so
         /usr/lib/libcfg.a(shr_64.o)
         /usr/lib/libodm.a(shr_64.o)
         /usr/lib/libc.a(shr_64.o)
         /usr/lib/libpthreads.a(shr_xpg5_64.o)
         /u01/app/grid/grid_has/lib/libskgxn2.so
         /u01/app/grid/grid_has/lib/libocr11.so
         /u01/app/grid/grid_has/lib/libocrutl11.so
         /usr/lib/libdl.a(shr_64.o)
         /usr/lib/libc.a(aio_64.o)
         /usr/lib/libperfstat.a(shr_64.o)
         /unix
         /usr/lib/libcrypt.a(shr_64.o)
         /u01/app/grid/grid_has/lib/libocrb11.so
         /usr/lib/liblvm.a(shr_64.o)
         /usr/lib/libcorcfg.a(shr_64.o)
         /usr/lib/libsrc.a(shr_64.o)

Since lib “libhasgen11.so” is an lib of Oracle Database the issue is related to the AIX and Oracle.

Cause:

The Filesystem where Grid Infrastructure was installed is not mounted or avaliable.
Oracle Grid Infrastructure 11.2 has a feature called ACFS (ASM Cluster Filesystem) that has own drivers called “Oracle Kernel Services Driver” (OKS).

The OKS drivers are installed  into the native operating system, these drivers are required for managing the filesystem (ACFS) using OS commands. (e.g mount).

You can find these drivers in “$GUI_HOME/usm/V/powerpc/bin/”.

Is It Possible to Avoid ADVM Driver Install During Grid Infrastructure Install ?
The answer is – NO. It is not possible to disable that. The reasoning behind this is that Clusterware components are not user configurable.
ACFS is not mandatory to use, but it is mandatory to initially install and config ACFS (drivers).

In my case the host was a contingency, so do not want the filesystem where GUI are installled available.

Solution:

1°: The easy solution is to make the filesystem of GUI available and retry command “cfgmgr”.

2°: If you are not using ACFS feature, we can disable them upon the installation.
To do that follow the next steps:
1) Dismount the ACFS filesystem first
2) Verify the ACFS filesystem is dismounted
3) Stop the OHAS services
4) Offload the ACFS/ADVM modules from memory (as root)
$GUI_HOME/bin/acfsload stop
5) Then remove the ACFS/ADVM modules installation (as root)
$GUI_HOME/bin/acfsroot uninstall

If after these steps you keep getting this error, or if OH was already removed:

root@aix:/ >cfgmgr
Method error (/usr/lib/methods/startadvmctl):
        0514-068 Cause not known.
sh: /usr/lib/methods/startadvmctl:  not found

Solution:

odmdelete -q rule=/usr/lib/methods/startacfsctl -o Config_Rules
odmdelete -q rule=/usr/lib/methods/startadvmctl -o Config_Rules

Download Oracle Database 11g Release 2 (11.2.0.3) Patch Set 2

Oracle Database 11g Release 2 (11.2.0.3) is available in the latest media pack.

Patch Number 10404530 Oracle Database Family: Patchset
11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER

..

Installation Type Zip File
Oracle Database (includes Oracle Database and Oracle RAC)Note: you must download both zip files to install Oracle Database. p10404530_112030_platform_1of7.zipp10098816_112020_platform_2of7.zip
Oracle Grid Infrastructure (includes Oracle ASM, Oracle Clusterware, and Oracle Restart) p10404530_112030_platform_3of7.zip
Oracle Database Client p10404530_112030_platform_4of7.zip
Oracle Gateways p10404530_112030_platform_5of7.zip
Oracle Examples p10404530_112030_platform_6of7.zip
Deinstall p10404530_112030_platform_7of7.zip

Click here to download your Patch Set 11.2.0.3.0

Note: You must have access (it’s not free) on My Oracle Support to Download the PatchSet.


How Migrate All Files on ASM to Non-ASM (Unix/Linux)

In this post I’ll show a way to migrate the database stored in ASM to Filesystem.
I’ll use PL/SQL scripts and RMAN commands to accomplish this.

I don’t like to use Oracle Managed File (OMF) for filesystem because I prefer readable names when we use non-ASM filesystem.
We have some others options which is litte bit easier, for example, migrating using OMF (filesystem) and RMAN, this way will be covered on this post.

I ask you if modify the PL/SQL code used in this post, leaving more efficiently, please send it back so I can make available to others by updating this post. (thank you)

Env Info:

DATABASE NAME : DROP
ORACLE DATAFILES/CONTROLFILE/TEMPFILE = /u01/app/oracle/oradata/drop
ORACLE FLASH RECOVERY AREA= /u01/app/oracle/flash_recovery_area/

Migration  steps:

  1. Controlfile
  2. Datafile and Tempfile
  3. Online Logs (redo)
  4. Archived Online logs (archivelogs) and BackupSet in FRA
  5. Server Parameter File (SPFILE)

1.  Controlfiles

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DG_DATA/drop/controlfile/current.275.761683397
+DG_FRA/drop/controlfile/current.281.761683397

 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1119043584 bytes
Fixed Size                  2218888 bytes
Variable Size             855639160 bytes
Database Buffers          251658240 bytes
Redo Buffers                9527296 bytes

SQL> alter system set
control_files='/u01/app/oracle/oradata/drop/control01.ctl',
'/u01/app/oracle/flash_recovery_area/drop/control02.ctl'
SCOPE=SPFILE SID='*';

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Sep 12 18:36:53 2011

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    1119043584 bytes

Fixed Size                     2218888 bytes
Variable Size                855639160 bytes
Database Buffers             251658240 bytes
Redo Buffers                   9527296 bytes

RMAN> restore controlfile from '+DG_DATA/drop/controlfile/current.275.761683397';

Starting restore at 12-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/drop/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/drop/control02.ctl
Finished restore at 12-SEP-11

RMAN> startup mount

database is already started
database mounted
released channel: ORA_DISK_1

RMAN> exit

Recovery Manager complete.

 

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 12 18:53:19 2011

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

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/drop/control01.ctl
/u01/app/oracle/flash_recovery_area/drop/control02.ctl

2.  Datafiles and Tempfiles

SQL> select file#, name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +DG_DATA/drop/datafile/system.292.761712355
         2 +DG_DATA/drop/datafile/sysaux.291.761712395
         3 +DG_DATA/drop/datafile/undotbs1.290.761712439
         4 +DG_DATA/drop/datafile/users.289.761712455
         5 +DG_DATA/drop/datafile/users.288.761712457
         6 +DG_DATA/drop/datafile/users.287.761712457
         7 +DG_DATA/drop/datafile/users.277.761712459
         8 +DG_DATA/drop/datafile/users.278.761712461
         9 +DG_DATA/drop/datafile/users.279.761712461
        10 +DG_DATA/drop/datafile/users.276.761712463

10 rows selected.

SQL> select file#, name from v$tempfile;

     FILE# NAME
---------- --------------------------------------------------
         2 +DG_DATA/drop/tempfile/temp.286.761687721
         1 +DG_DATA/drop/tempfile/temp.293.761712609

Using this PL/SQL is enough to generate RMAN command to migrate all datafiles and tempfiles at same time.

SET serveroutput ON;
DECLARE
  vcount  NUMBER:=0;
  vfname VARCHAR2(1024);
  CURSOR df
  IS
    SELECT file#,
      rtrim(REPLACE(name,'+DG_DATA/drop/datafile/','/u01/app/oracle/oradata/drop/'),'.0123456789') AS name
    FROM v$datafile;
  CURSOR tp
  IS
    SELECT file#,
      rtrim(REPLACE(name,'+DG_DATA/drop/tempfile/','/u01/app/oracle/oradata/drop/'),'.0123456789') AS name
    FROM v$tempfile;
BEGIN
  dbms_output.put_line('CONFIGURE CONTROLFILE AUTOBACKUP ON;');
  FOR dfrec IN df
  LOOP
    IF dfrec.name  != vfname THEN
      vcount      :=1;
      vfname     := dfrec.name;
    ELSE
      vcount := vcount+1;
      vfname:= dfrec.name;
    END IF;
    dbms_output.put_line('backup as copy datafile ' || dfrec.file# ||' format  "'||dfrec.name ||vcount||'.dbf";');
  END LOOP;
  dbms_output.put_line('run');
  dbms_output.put_line('{');
  FOR dfrec IN df
  LOOP
    IF dfrec.name  != vfname THEN
      vcount      :=1;
      vfname     := dfrec.name;
    ELSE
      vcount := vcount+1;
      vfname:= dfrec.name;
    END IF;
    dbms_output.put_line('set newname for datafile ' || dfrec.file# ||'  to  '''||dfrec.name ||vcount||'.dbf'' ;');
  END LOOP;
  FOR tprec IN tp
  LOOP
    IF tprec.name  !=  vfname THEN
      vcount      :=1;
      vfname     := tprec.name;
    ELSE
      vcount := vcount+1;
      vfname:= tprec.name;
    END IF;
    dbms_output.put_line('set newname for tempfile ' || tprec.file# ||'  to  '''||tprec.name ||vcount||'.dbf'' ;');
    END LOOP;
    dbms_output.put_line('switch tempfile all;');
    dbms_output.put_line('switch datafile all;');
    dbms_output.put_line('restore database;');
    dbms_output.put_line('recover database;');
    dbms_output.put_line('}');
    dbms_output.put_line('alter database open;');
    dbms_output.put_line('exit');
END;
/

Database must be Mounted


SQL> SET serveroutput ON;
SQL> DECLARE
.
.
.
 57      dbms_output.put_line('exit');
 58  END;
 59  /

CONFIGURE CONTROLFILE AUTOBACKUP ON;
backup as copy datafile 1 format  "/u01/app/oracle/oradata/drop/system1.dbf";
backup as copy datafile 2 format  "/u01/app/oracle/oradata/drop/sysaux1.dbf";
backup as copy datafile 3 format  "/u01/app/oracle/oradata/drop/undotbs1.dbf";
backup as copy datafile 4 format  "/u01/app/oracle/oradata/drop/users1.dbf";
backup as copy datafile 5 format  "/u01/app/oracle/oradata/drop/users2.dbf";
backup as copy datafile 6 format  "/u01/app/oracle/oradata/drop/users3.dbf";
backup as copy datafile 7 format  "/u01/app/oracle/oradata/drop/users4.dbf";
backup as copy datafile 8 format  "/u01/app/oracle/oradata/drop/users5.dbf";
backup as copy datafile 9 format  "/u01/app/oracle/oradata/drop/users6.dbf";
backup as copy datafile 10 format  "/u01/app/oracle/oradata/drop/users7.dbf";
run
{
set newname for datafile 1  to  '/u01/app/oracle/oradata/drop/system1.dbf' ;
set newname for datafile 2  to  '/u01/app/oracle/oradata/drop/sysaux1.dbf' ;
set newname for datafile 3  to  '/u01/app/oracle/oradata/drop/undotbs1.dbf' ;
set newname for datafile 4  to  '/u01/app/oracle/oradata/drop/users1.dbf' ;
set newname for datafile 5  to  '/u01/app/oracle/oradata/drop/users2.dbf' ;
set newname for datafile 6  to  '/u01/app/oracle/oradata/drop/users3.dbf' ;
set newname for datafile 7  to  '/u01/app/oracle/oradata/drop/users4.dbf' ;
set newname for datafile 8  to  '/u01/app/oracle/oradata/drop/users5.dbf' ;
set newname for datafile 9  to  '/u01/app/oracle/oradata/drop/users6.dbf' ;
set newname for datafile 10  to  '/u01/app/oracle/oradata/drop/users7.dbf' ;
set newname for tempfile 1  to  '/u01/app/oracle/oradata/drop/temp1.dbf' ;
set newname for tempfile 2  to  '/u01/app/oracle/oradata/drop/temp2.dbf' ;
switch tempfile all;
switch datafile all;
restore database;
recover database;
}
alter database open;
exit

Important: Setting AUTOBACKUP ON is mandatory (Backup Fails ORA-27038 Creating Automatic Controlfile Backup [ID 382989.1])

Create a RMAN script file “migrate_db.rcv” and paste command generate by PL/SQL.

nohup rman target / cmdfile migrate_db.rcv log migrate_db.log &

cat migrate_db.log

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Starting backup at 13-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DG_DATA/drop/datafile/system.292.761712355
output file name=/u01/app/oracle/oradata/drop/system1.dbf tag=TAG20110913T025233 RECID=68 STAMP=761712766
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvxlw3_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DG_DATA/drop/datafile/sysaux.291.761712395
output file name=/u01/app/oracle/oradata/drop/sysaux1.dbf tag=TAG20110913T025253 RECID=69 STAMP=761712784
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvy5h5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DG_DATA/drop/datafile/undotbs1.290.761712439
output file name=/u01/app/oracle/oradata/drop/undotbs1.dbf tag=TAG20110913T025312 RECID=70 STAMP=761712796
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvyjkb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DG_DATA/drop/datafile/users.289.761712455
output file name=/u01/app/oracle/oradata/drop/users1.dbf tag=TAG20110913T025321 RECID=71 STAMP=761712802
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvymrv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DG_DATA/drop/datafile/users.288.761712457
output file name=/u01/app/oracle/oradata/drop/users2.dbf tag=TAG20110913T025326 RECID=72 STAMP=761712807
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvys74_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DG_DATA/drop/datafile/users.287.761712457
output file name=/u01/app/oracle/oradata/drop/users3.dbf tag=TAG20110913T025332 RECID=73 STAMP=761712812
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvyydl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DG_DATA/drop/datafile/users.277.761712459
output file name=/u01/app/oracle/oradata/drop/users4.dbf tag=TAG20110913T025335 RECID=74 STAMP=761712816
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvz1qq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG_DATA/drop/datafile/users.278.761712461
output file name=/u01/app/oracle/oradata/drop/users5.dbf tag=TAG20110913T025338 RECID=75 STAMP=761712819
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvz4xm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+DG_DATA/drop/datafile/users.279.761712461
output file name=/u01/app/oracle/oradata/drop/users6.dbf tag=TAG20110913T025342 RECID=76 STAMP=761712822
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvz878_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-11

Starting backup at 13-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=+DG_DATA/drop/datafile/users.276.761712463
output file name=/u01/app/oracle/oradata/drop/users7.dbf tag=TAG20110913T025345 RECID=77 STAMP=761712825
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 13-SEP-11

Starting Control File and SPFILE Autobackup at 13-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_13/o1_mf_s_761712652_76xvzcjb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-SEP-11

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/drop/temp1.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/drop/temp2.dbf in control file

datafile 1 switched to datafile copy
input datafile copy RECID=68 STAMP=761712766 file name=/u01/app/oracle/oradata/drop/system1.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=69 STAMP=761712784 file name=/u01/app/oracle/oradata/drop/sysaux1.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=70 STAMP=761712796 file name=/u01/app/oracle/oradata/drop/undotbs1.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=71 STAMP=761712802 file name=/u01/app/oracle/oradata/drop/users1.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=72 STAMP=761712807 file name=/u01/app/oracle/oradata/drop/users2.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=73 STAMP=761712812 file name=/u01/app/oracle/oradata/drop/users3.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=74 STAMP=761712816 file name=/u01/app/oracle/oradata/drop/users4.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=75 STAMP=761712819 file name=/u01/app/oracle/oradata/drop/users5.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=76 STAMP=761712822 file name=/u01/app/oracle/oradata/drop/users6.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=77 STAMP=761712825 file name=/u01/app/oracle/oradata/drop/users7.dbf

Starting restore at 13-SEP-11
using channel ORA_DISK_1

skipping datafile 1; already restored to file /u01/app/oracle/oradata/drop/system1.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/drop/sysaux1.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/drop/undotbs1.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/drop/users1.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/drop/users2.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/drop/users3.dbf
skipping datafile 7; already restored to file /u01/app/oracle/oradata/drop/users4.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/drop/users5.dbf
skipping datafile 9; already restored to file /u01/app/oracle/oradata/drop/users6.dbf
skipping datafile 10; already restored to file /u01/app/oracle/oradata/drop/users7.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-SEP-11

Starting recover at 13-SEP-11
using channel ORA_DISK_1

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

Finished recover at 13-SEP-11

database opened

Recovery Manager complete.
SQL> select file#, name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/drop/system1.dbf
         2 /u01/app/oracle/oradata/drop/sysaux1.dbf
         3 /u01/app/oracle/oradata/drop/undotbs1.dbf
         4 /u01/app/oracle/oradata/drop/users1.dbf
         5 /u01/app/oracle/oradata/drop/users2.dbf
         6 /u01/app/oracle/oradata/drop/users3.dbf
         7 /u01/app/oracle/oradata/drop/users4.dbf
         8 /u01/app/oracle/oradata/drop/users5.dbf
         9 /u01/app/oracle/oradata/drop/users6.dbf
        10 /u01/app/oracle/oradata/drop/users7.dbf

10 rows selected.

SQL> select file#, name from v$tempfile;

     FILE# NAME
---------- --------------------------------------------------
         2 /u01/app/oracle/oradata/drop/temp2.dbf
         1 /u01/app/oracle/oradata/drop/temp1.dbf

SQL>

3.  Online Logs (Redo)

Database must be Open

SQL> select group#,member from v$logfile order by 1;

    GROUP# MEMBER
---------- ------------------------------------------------------------------------------------------
         1 +DG_DATA/drop/onlinelog/group_1.285.761755579
         2 +DG_DATA/drop/onlinelog/group_2.284.761755615
         3 +DG_DATA/drop/onlinelog/group_3.278.761755651
         4 +DG_DATA/drop/onlinelog/group_4.283.761754951
         5 +DG_DATA/drop/onlinelog/group_5.276.761754957
         6 +DG_DATA/drop/onlinelog/group_6.282.761754963
         7 +DG_DATA/drop/onlinelog/group_7.279.761754967

SQL> SELECT group# grp,
      thread# thr,
      bytes/1024 bytes_k,
      'NO' srl,
      status
    FROM v$log
  UNION
  SELECT group# grp,
    thread# thr,
    bytes/1024 bytes_k,
    'YES' srl,
    status
  FROM v$standby_log
  ORDER BY 1;

       GRP        THR    BYTES_K SRL STATUS
---------- ---------- ---------- --- ----------------
         1          1     102400 NO  INACTIVE
         2          1     102400 NO  CURRENT
         3          1     102400 NO  UNUSED
         4          1     102400 YES UNASSIGNED
         5          1     102400 YES UNASSIGNED
         6          1     102400 YES UNASSIGNED
         7          1     102400 YES UNASSIGNED

Another PL/SQL, will work only if database have more than 2 Logfile Group

DECLARE
  vgroup NUMBER;
  CURSOR rlc
  IS
    SELECT group# grp,
      thread# thr,
      bytes/1024 bytes_k,
      'NO' srl,
      status
    FROM v$log
  UNION
  SELECT group# grp,
    thread# thr,
    bytes/1024 bytes_k,
    'YES' srl,
    status
  FROM v$standby_log
  ORDER BY status DESC;
  stmt    VARCHAR2(2048);
  swtstmt VARCHAR2(1024) := 'alter system switch logfile';
  ckpstmt VARCHAR2(1024) := 'alter system checkpoint global';
BEGIN
  FOR rlcRec IN rlc
  LOOP
    IF (rlcRec.srl = 'YES') THEN
      BEGIN
        SELECT group#
        INTO vgroup
        FROM v$standby_log
        WHERE group# = rlcRec.grp
        AND thread#  = rlcRec.thr
        AND (status  ='CURRENT'
        OR status    = 'ACTIVE');
        stmt        := 'alter database drop standby logfile group ' || rlcRec.grp;
        EXECUTE IMMEDIATE swtstmt;
        EXECUTE IMMEDIATE ckpstmt;
        EXECUTE immediate stmt;
        stmt := 'alter database add standby logfile thread ' || rlcRec.thr ||' group '||rlcRec.grp||' ''/u01/app/oracle/oradata/drop/redo_stby'||rlcRec.grp||'_'||rlcRec.thr||'_a.log'' size ' || rlcRec.bytes_k || 'K';
        EXECUTE immediate stmt;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        BEGIN
          EXECUTE immediate ckpstmt;
          stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
          EXECUTE immediate stmt;
          stmt := 'alter database add standby logfile thread ' || rlcRec.thr ||' group '||rlcRec.grp||' ''/u01/app/oracle/oradata/drop/redo_stby'||rlcRec.grp||'_'||rlcRec.thr||'_a.log'' size ' || rlcRec.bytes_k || 'K';
          EXECUTE immediate stmt;
        END;
      END;
    ELSE
      BEGIN
        SELECT group#
        INTO vgroup
        FROM v$log
        WHERE group# = rlcRec.grp
        AND thread#  = rlcRec.thr
        AND (status  ='CURRENT'
        OR status    = 'ACTIVE');
        stmt        := 'alter database drop logfile group ' || rlcRec.grp;
        EXECUTE IMMEDIATE swtstmt;
        EXECUTE IMMEDIATE ckpstmt;
        EXECUTE immediate stmt;
        stmt := 'alter database add logfile thread ' || rlcRec.thr || ' group '||rlcRec.grp||'  ''/u01/app/oracle/oradata/drop/redo_'||rlcRec.grp||'_'||rlcRec.thr||'_a.log'' size ' || rlcRec.bytes_k || 'K';
        EXECUTE immediate stmt;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        BEGIN
          EXECUTE immediate ckpstmt;
          stmt := 'alter database drop logfile group ' || rlcRec.grp;
          EXECUTE immediate stmt;
          stmt := 'alter database add logfile thread ' || rlcRec.thr || ' group '||rlcRec.grp||'  ''/u01/app/oracle/oradata/drop/redo_'||rlcRec.grp||'_'||rlcRec.thr||'_a.log'' size ' || rlcRec.bytes_k || 'K';
          EXECUTE immediate stmt;
        END;
      END;
    END IF;
  END LOOP;
END;
/
SQL> select group#,member from v$logfile order by 1;

    GROUP# MEMBER
---------- ------------------------------------------------------------------------------------------
         1 /u01/app/oracle/oradata/drop/redo_1_1_a.log
         2 /u01/app/oracle/oradata/drop/redo_2_1_a.log
         3 /u01/app/oracle/oradata/drop/redo_3_1_a.log
         4 /u01/app/oracle/oradata/drop/redo_stby4_1_a.log
         5 /u01/app/oracle/oradata/drop/redo_stby5_1_a.log
         6 /u01/app/oracle/oradata/drop/redo_stby6_1_a.log
         7 /u01/app/oracle/oradata/drop/redo_stby7_1_a.log

7 rows selected.

SQL> SELECT group# grp,
      thread# thr,
      bytes/1024 bytes_k,
      'NO' srl,
      status
    FROM v$log
  UNION
  SELECT group# grp,
    thread# thr,
    bytes/1024 bytes_k,
    'YES' srl,
    status
  FROM v$standby_log
  ORDER BY 1;  2    3    4    5    6    7    8    9   10   11   12   13   14

       GRP        THR    BYTES_K SRL STATUS
---------- ---------- ---------- --- ----------------
         1          1     102400 NO  CURRENT
         2          1     102400 NO  UNUSED
         3          1     102400 NO  UNUSED
         4          1     102400 YES UNASSIGNED
         5          1     102400 YES UNASSIGNED
         6          1     102400 YES UNASSIGNED
         7          1     102400 YES UNASSIGNED

4.  Archived Online logs (archivelogs) and BackupSet stored in FRA

Migrating BackupSet and Archivelogs from FRA (Flash Recovery Area) on ASM to FRA on NON-ASM

The first step is set new FRA.

SQL> show parameter db_recovery_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DG_FRA
db_recovery_file_dest_size           big integer 100G

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' scope=both;

SQL> show parameter db_recovery_file
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 100G

Migrating Archivelog

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name DROP
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
80      1    57      A 16-SEP-11
        Name: +DG_FRA/drop/archivelog/2011_09_16/thread_1_seq_57.343.762030975

81      1    58      A 16-SEP-11
        Name: +DG_FRA/drop/archivelog/2011_09_16/thread_1_seq_58.333.762030975
.
.
.
89      1    66      A 16-SEP-11
        Name: +DG_FRA/drop/archivelog/2011_09_16/thread_1_seq_66.340.762030987

90      1    67      A 16-SEP-11
        Name: +DG_FRA/drop/archivelog/2011_09_16/thread_1_seq_67.341.762030989

$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 16 19:20:01 2011

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

connected to target database: DROP (DBID=3027542406)

RMAN>  BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;

Starting backup at 16-SEP-11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=57 RECID=92 STAMP=762031143
output file name=/u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_57_777lwyyp_.arc RECID=105 STAMP=762031215
.
.
.
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_69_777lww4t_.arc RECID=104 STAMP=762031212
Finished backup at 16-SEP-11

Starting Control File and SPFILE Autobackup at 16-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031234_777lxlx5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-SEP-11

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name DROP
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
105     1    57      A 16-SEP-11
        Name: /u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_57_777lwyyp_.arc

106     1    58      A 16-SEP-11
        Name: /u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_58_777lx0dh_.arc

.
.
.
116     1    68      A 16-SEP-11
        Name: /u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_68_777lxhfl_.arc

117     1    69      A 16-SEP-11
        Name: /u01/app/oracle/flash_recovery_area/DROP/archivelog/2011_09_16/o1_mf_1_69_777lxjwv_.arc
 

Migrating Backupset

 list backupset summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
49      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190201
50      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190208
51      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190235
52      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190237
53      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190919
54      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190921
55      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190937
56      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190939

RMAN> LIST BACKUPPIECE TAG TAG20110916T190201;

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
49      49      1   1   AVAILABLE   DISK        +DG_FRA/drop/backupset/2011_09_16/annnf0_tag20110916t190201_0.332.762030121

RMAN> BACKUP DEVICE TYPE DISK BACKUPSET ALL DELETE INPUT;

Starting backup at 16-SEP-11
using channel ORA_DISK_1
channel ORA_DISK_1: input backup set: count=107, stamp=762030121, piece=1
channel ORA_DISK_1: starting piece 1 at 16-SEP-11
channel ORA_DISK_1: backup piece +DG_FRA/drop/backupset/2011_09_16/annnf0_tag20110916t190201_0.332.762030121
piece handle=/u01/app/oracle/flash_recovery_area/DROP/backupset/2011_09_16/o1_mf_annnn_TAG20110916T190201_777ly4p4_.bkp comment=NONE
.
.
.
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:00
channel ORA_DISK_1: input backup set: count=153, stamp=762031234, piece=1
channel ORA_DISK_1: starting piece 1 at 16-SEP-11
skipping backup piece handle /u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031234_777lxlx5_.bkp; already exists
channel ORA_DISK_1: finished piece 1 at 16-SEP-11
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:00
Finished backup at 16-SEP-11

Starting Control File and SPFILE Autobackup at 16-SEP-11
piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031278_777lyz23_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-SEP-11

RMAN> LIST BACKUPSET SUMMARY;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
49      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190201
50      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190208
51      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190235
52      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190237
53      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190919
54      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190921
55      B  A  A DISK        16-SEP-11       1       1       NO         TAG20110916T190937
56      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T190939
57      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T191630
58      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T191920
59      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T192034
60      B  F  A DISK        16-SEP-11       1       1       NO         TAG20110916T192118

RMAN> LIST BACKUPPIECE TAG TAG20110916T190201;

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
60      49      1   2   AVAILABLE   DISK        /u01/app/oracle/flash_recovery_area/DROP/backupset/2011_09_16/o1_mf_annnn_TAG20110916T190201_777ly4p4_.bkp

5.  Server Parameter File (SPFILE)

SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DG_DATA/drop/spfiledrop.ora

srvctl config database -d drop |grep Spfile
Spfile: +DG_DATA/drop/spfiledrop.ora

rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 16 19:56:33 2011

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

connected to target database: DROP (DBID=3027542406)

RMAN> restore spfile to '$ORACLE_HOME/dbs/spfiledrop.ora';

Starting restore at 16-SEP-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=$ORACLE_HOME/dbs/spfiledrop.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031278_777lyz23_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DROP/autobackup/2011_09_16/o1_mf_s_762031278_777lyz23_.bkp tag=TAG20110916T192118
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 16-SEP-11

# edit initdrop.ora on $ORACLE_HOME/dbs
vi initdrop
spfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledrop.ora'

# If you are using Oracle Restart you must change configuration with srvct.
$ srvctl modify database -d drop -p $ORACLE_HOME/dbs/spfiledrop.ora
$ srvctl config database -d drop |grep Spfile
Spfile: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledrop.ora

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1119043584 bytes
Fixed Size                  2218888 bytes
Variable Size             855639160 bytes
Database Buffers          251658240 bytes
Redo Buffers                9527296 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledrop.ora

You can comment … I accept hints ….

Rodrigo Murillo G (Quote)

I did a little change in the PL/SQL script which generates rman commands for backup and restore. The change allows numbers on the datafile names (i.e. datafiles for table partitions tablespaces by years, quarters, etc). Also, the restore database and switch commands were changed in their order (originally: first switch then restore), and the order that worked for me was first restore then switch.

SET serveroutput ON;
DECLARE
vcount NUMBER:=0;
vfname VARCHAR2(1024);
CURSOR df
IS
SELECT file#,
replace (substr ( name, 1, instr( name, '.', 1 , 1 ) -1) || '_', '+DATA', '/data_oracle' ) AS name
FROM v$datafile
order by name;
CURSOR tp
IS
SELECT file#,
rtrim(REPLACE(name,'+DATA/ictxcyc/tempfile/','/data_oracle/ictxcyc/datafile/'),'.0123456789′) AS name
FROM v$tempfile;
BEGIN
dbms_output.put_line('CONFIGURE CONTROLFILE AUTOBACKUP ON;');
FOR dfrec IN df
LOOP
IF dfrec.name != vfname THEN
vcount :=1;
vfname := dfrec.name;
ELSE
vcount := vcount+1;
vfname:= dfrec.name;
END IF;
dbms_output.put_line('backup as copy datafile ' || dfrec.file# ||' format "'||dfrec.name ||vcount||'.dbf";');
END LOOP;
dbms_output.put_line('run');
dbms_output.put_line('{');
FOR dfrec IN df
LOOP
IF dfrec.name != vfname THEN
vcount :=1;
vfname := dfrec.name;
ELSE
vcount := vcount+1;
vfname:= dfrec.name;
END IF;
dbms_output.put_line('set newname for datafile ' || dfrec.file# ||' to "'||dfrec.name ||vcount||'.dbf" ;' );
END LOOP;
FOR tprec IN tp
LOOP
IF tprec.name != vfname THEN
vcount :=1;
vfname := tprec.name;
ELSE
vcount := vcount+1;
vfname:= tprec.name;
END IF;
dbms_output.put_line('set newname for tempfile ' || tprec.file# ||' to "'||tprec.name ||vcount||'.dbf" ;');
END LOOP;
dbms_output.put_line('restore database;');
dbms_output.put_line('switch tempfile all;');
dbms_output.put_line('switch datafile all;');
dbms_output.put_line('recover database;');
dbms_output.put_line('}');
dbms_output.put_line('alter database open;');
dbms_output.put_line('exit');
END;
/

Enjoy…


Using ACFS Snapshot to create a consistent recovery point of ORACLE_HOME

ACFS Snapshots

ACFS provides snapshotting capability for the respective filesystem. This snapshot uses the First Copy-on-Write (FCOW) methodology to enable a consistent, version-based, online view of the source filesystem.
Snapshots are immutable views of the source file system as it appeared at a specific point in time.
Snapshots are initially a sparse filesystem, as the source filesystem files change, the before-image extent of that file is copied into the snapshot directory. The before-image granularity is an ACFS extent, thus if any byte in an extent is modified, the extent is COW’ed and any subsequent changes in that extent requires no action for the snap.

I’ll do some tests using the ACFS Snapshot.

I go apply the Critical Patch Update July 2011 (ID 12419321) on ORACLE_HOME (11.2.0.2) and apply CPU on Database (SQL files).

After finish Patch ORACLE_HOME and Database I go perform recovery point in ORACLE_HOME and Database to bring back point before applying a database patchset.

Things I did not like in the ACFS Snapshot.

  • To recover a file we must perform using standard file copy or replace commands from O.S . (No exists restore command to ACFS Snapshot)
  • I cannot change default location of Snapshots,  if no space available to Snapshot the  filesystem will be exhausted.
Let’s Start.

Backup a Database

I’ll not use Restore Database using (Restore Database ) from RMAN , I will use feature GRP  (guaranteed restore point) is much more fast than Restore all Database files.

It is mandatory do Backup of Database through RMAN, even using GRP because GRP must be discarded after upgrade/downgrade.

$ export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jul 29 11:49:29 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB11G (DBID=227286804)

RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount
connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1185853440 bytes
Fixed Size                     2225912 bytes
Variable Size                788531464 bytes
Database Buffers             385875968 bytes
Redo Buffers                   9220096 bytes

RMAN> backup database plus archivelog delete input;

Starting backup at Jul 29 2011 11:50:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=194 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=1 STAMP=757770541
...
Finished backup at Jul 29 2011 11:50:18

Starting backup at Jul 29 2011 11:50:18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DG_DATA/db11g/datafile/system.256.757617145
...
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
...
Finished backup at Jul 29 2011 11:50:47

RMAN> exit

After backup finish leave Database in Mount State.

Guaranteed Restore Points

Like a normal restore point, a guaranteed restore point serves as an alias for an SCN in recovery operations.
A principal difference is that guaranteed restore points never age out of the control file and must be explicitly dropped. In general, you can use a guaranteed restore point as an alias for an SCN with any command that works with a normal restore point. Except as noted, the information about where and how to use normal restore points applies to guaranteed restore points as well.

A guaranteed restore point ensures that you can use Flashback Database to rewind a database to its state at the restore point SCN, even if the generation of flashback logs is disabled. If flashback logging is enabled, then a guaranteed restore point enforces the retention of flashback logs required for Flashback Database to any SCN after the earliest guaranteed restore point. Thus, if flashback logging is enabled, you can rewind the database to any SCN in the continuum rather than to a single SCN only.

http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/flashdb.htm#CFHGFGFH

Let’s use feature Guaranteed Restore Points to save time at moment of restore.

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jul 29 14:56:45 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> CREATE RESTORE POINT before_upgrade_12419321 GUARANTEE FLASHBACK DATABASE;
Restore point created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Creating Snapshot of ORACLE_HOME

The snapshot must be created with root privileges.

$ sudo /sbin/acfsutil snap create pre_patch_12419321 /u01/app/oracle/product/11.2.0/dbhome_1
acfsutil snap create: Snapshot operation is complete.

$ sudo /sbin/acfsutil info fs /u01/app/oracle/product/11.2.0/dbhome_1
/u01/app/oracle/product/11.2.0/dbhome_1
    ACFS Version: 11.2.0.2.0
    flags:        MountPoint,Available
    mount time:   Thu Jul 28 15:45:27 2011
    volumes:      1
    total size:   16106127360
    total free:   11288350720
    primary volume: /dev/asm/db112_dbh1-220
        label:
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 112641
        size:                  16106127360
        free:                  11288350720
        ADVM diskgroup         DG_ORAHOME11GR2_1
        ADVM resize increment: 268435456
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  1
    snapshot space usage: 19959808

The Database and ORACLE_HOME are ready for patching.

Patching ORACLE_HOME

$ opatch lsinventory
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-07-29_12-08-55PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2011-07-29_12-08-55PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.

$ ls -l
total 3142372
drwxrwxr-x 17 oracle oinstall       4096 May 26 10:20 12419321
-rw-r--r--  1 oracle oinstall    2982182 Jul 29 11:35 p12419321_112020_Linux-x86-64.zip
$ cd 12419321/

$ opatch napply -skip_subset -skip_duplicate
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

UTIL session

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-07-29_12-10-03PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "napply"
Checking conflict among patches...
Checking if Oracle Home has components required by patches...
Checking skip_duplicate
Checking skip_subset
Checking conflicts against Oracle Home...
OPatch continues with these patches:   11830776  11830777  11830778  12419321  12586486  12586487  12586488  12586489  12586490  12586491  12586492  12586493  12586494  12586495  12586496

Do you want to proceed? [y|n]
y
User Responded with: Y

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch 'NApply' for restore. This might take a while...
.....
Verifying the update...
Inventory check OK: Patch ID 12586496 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 12586496 are present in Oracle Home.
Running make for target client_sharedlib
Running make for target client_sharedlib
Running make for target ioracle

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.

$ opatch lsinventory
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-07-29_14-04-23PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2011-07-29_14-04-23PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0
There are 1 products installed in this Oracle Home.

Interim patches (15) :

Patch  12586496     : applied on Fri Jul 29 12:16:24 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 06:02:42 hrs PST8PDT
   Bugs fixed:
     12586496

Patch  12586495     : applied on Fri Jul 29 12:16:17 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 06:00:31 hrs PST8PDT
   Bugs fixed:
     12586495

Patch  12586494     : applied on Fri Jul 29 12:16:11 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 05:58:48 hrs PST8PDT
   Bugs fixed:
     12586494

Patch  12586493     : applied on Fri Jul 29 12:15:58 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 05:55:56 hrs PST8PDT
   Bugs fixed:
     12586493

Patch  12586492     : applied on Fri Jul 29 12:15:55 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 05:52:39 hrs PST8PDT
   Bugs fixed:
     12586492

Patch  12586491     : applied on Fri Jul 29 12:15:42 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 05:46:21 hrs PST8PDT
   Bugs fixed:
     12586491

Patch  12586490     : applied on Fri Jul 29 12:15:35 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 05:35:20 hrs PST8PDT
   Bugs fixed:
     12586490

Patch  12586489     : applied on Fri Jul 29 12:15:08 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 05:29:23 hrs PST8PDT
   Bugs fixed:
     12586489

Patch  12586488     : applied on Fri Jul 29 12:15:04 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 05:27:10 hrs PST8PDT
   Bugs fixed:
     12586488

Patch  12586487     : applied on Fri Jul 29 12:14:51 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 05:24:30 hrs PST8PDT
   Bugs fixed:
     12586487

Patch  12586486     : applied on Fri Jul 29 12:14:27 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 03:35:49 hrs PST8PDT
   Bugs fixed:
     12586486

Patch  12419321     : applied on Fri Jul 29 12:14:03 BRT 2011
Unique Patch ID:  13783102
   Created on 26 May 2011, 06:16:06 hrs PST8PDT
   Bugs fixed:
     12419321, 11724984

Patch  11830778     : applied on Fri Jul 29 12:14:01 BRT 2011
Unique Patch ID:  13783102
   Created on 4 Apr 2011, 02:41:43 hrs PST8PDT
   Bugs fixed:
     11830778

Patch  11830777     : applied on Fri Jul 29 12:13:46 BRT 2011
Unique Patch ID:  13783102
   Created on 4 Apr 2011, 02:36:22 hrs PST8PDT
   Bugs fixed:
     11830777

Patch  11830776     : applied on Fri Jul 29 12:13:17 BRT 2011
Unique Patch ID:  13783102
   Created on 4 Apr 2011, 02:27:33 hrs PST8PDT
   Bugs fixed:
     11830776

--------------------------------------------------------------------------------
OPatch succeeded.

Patching Database


$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jul 29 12:19:12 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size                  2225912 bytes
Variable Size             788531464 bytes
Database Buffers          385875968 bytes
Redo Buffers                9220096 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/catbundle.sql cpu apply

PL/SQL procedure successfully completed.

Executing script file...

.....

Session altered.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_CPU_DB11G_APPLY_2011Jul29_12_20_14.log
SQL> exit

SQL> select * from registry$history where BUNDLE_SERIES='CPU';

ACTION_TIME                    ACTION     NAMESPACE  VERSION     ID COMMENTS                       BUNDLE_SERIES
------------------------------ ---------- ---------- ---------- --- ------------------------------ ------------------------------
29-JUL-11 03.08.31.254298 PM   APPLY      SERVER     11.2.0.2     2 CPUJul2011                     CPU

Performing Patch Deinstallation

Peforming restore point of Database using GRP.

I love the feature GRP, I have performed the restore in 3 seconds.
Using command RESTORE of RMAN depending on the size of the database could take hours

SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM V$RESTORE_POINT
NAME                                     SCN TIME                                        DI GUA STORAGE_SIZE
------------------------------ ------------- ----------------------------------- ---------- --- ------------
BEFORE_UPGRADE_12419321              1236543 29-JUL-11 02.57.54.000000000 PM              3 YES     15941632

RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1185853440 bytes

Fixed Size                     2225912 bytes
Variable Size                788531464 bytes
Database Buffers             385875968 bytes
Redo Buffers                   9220096 bytes

RMAN> FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE_12419321;

Starting flashback at Jul 29 2011 15:22:52
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=194 device type=DISK

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

Finished flashback at Jul 29 2011 15:22:56

RMAN> alter database open resetlogs;
database opened

SQL> select * from registry$history;
ACTION_TIME                         ACTION     NAMESPACE  VERSION            ID COMMENTS             BUNDLE_SERIES
----------------------------------- ---------- ---------- ---------- ---------- -------------------- ---------------
05-SEP-10 06.22.14.370943 AM        APPLY      SERVER     11.2.0.2            0 Patchset 11.2.0.2.0  PSU
27-JUL-11 05.14.47.164924 PM        APPLY      SERVER     11.2.0.2            0 Patchset 11.2.0.2.0  PSU

SQL> shutdown immmediate;

Recover the filesystem (ORACLE_HOME) leveraging the snapshot

I use the cp command from O.S and you must use flag -p (to preserve mode,ownership,timestamps) and flag -R (to copy directories recursively) using root privileges.

I took 16 minutes to copy all files from Snapshot to ORACLE_HOME. (the copy took a long time)

$ sudo time cp -Rp /u01/app/oracle/product/11.2.0/dbhome_1/.ACFS/snaps/pre_patch_12419321/* /u01/app/oracle/product/11.2.0/dbhome_1/

$ opatch lsinventory
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-07-29_15-45-53PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2011-07-29_15-45-53PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------
OPatch succeeded.

Restoring with the conventional method using OPatch

The OPatch took 5 minutes to rollback the patch.


$ opatch nrollback -idFile $ORACLE_HOME/cpu/CPUJul2011/rollback_all.lst
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

UTIL session

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-07-29_15-54-23PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Invoking utility "nrollback"

Patches will be rolled back in the following order:
   11830776   11830777   11830778   12419321   12586486   12586487   12586488   12586489   12586490   12586491   12586492   12586493   12586494   12586495   12586496

Running prerequisite checks...
The following patch(es) will be rolled back: 11830776  11830777  11830778  12419321  12586486  12586487  12586488  12586489  12586490  12586491  12586492  12586493  12586494  12586495  12586496

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch 'NRollback' for restore. This might take a while...

Rolling back patch 11830776...

RollbackSession rolling back interim patch '11830776' from OH '/u01/app/oracle/product/11.2.0/dbhome_1'

....

Running make for target client_sharedlib
Running make for target client_sharedlib
Running make for target ioracle

The local system has been patched and can be restarted.

UtilSession: N-Rollback done.

OPatch succeeded.

$ opatch lsinventory
Invoking OPatch 11.2.0.1.1

Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.1
OUI version       : 11.2.0.2.0
OUI location      : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-07-29_16-01-27PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2011-07-29_16-01-27PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0
There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

--------------------------------------------------------------------------------
OPatch succeeded.

Clean Up

After the maintenance is mandatory to clean, because if we not disable ACFS SNAPSHOT and GRP, it will continue to generate data for each modification of the database and files in Oracle home.

$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Tue Aug 9 14:55:14 2011
connected to target database: DB11G (DBID=227286804)

RMAN>  LIST RESTORE POINT ALL;
using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
1236543                    GUARANTEED 29-JUL-11 BEFORE_UPGRADE_12419321

$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 9 14:57:18 2011
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

SQL> DROP RESTORE POINT BEFORE_UPGRADE_12419321;
Restore point dropped.

$sudo /sbin/acfsutil snap delete  pre_patch_12419321 /u01/app/oracle/product/11.2.0/dbhome_1
acfsutil snap delete: Snapshot operation is complete.

Conclusion

The snapshot feature is good when updated/patch software or files that do not allow the rollback  on case of failures.

I still prefer the conventional method using OPATCH to perform the rolling back a patch.

Rolling back a patch using the OPatch utility is more safe and faster than copying files fromACFS Snapshot. OPatch performs all tasks necessary to leave the ORACLE_HOME with integrity. The copy of data via the CP command must be executed with caution and we should run the relink the binaries (if necessary) manually.

When we applying patch the utility OPATCH change few binary files on Oracle Home, but Oracle files are relinked by OPATCH, so at this moment a lot files are changed and all this files changed will be copied into the snapshot directory with before-image extent.

So, when we rolling back all files using ACFS SNAPSHOT a lot files will be copied to Oracle Home, because that  OPATCH to rolling back it’s more fast, he back few files and relink again all files on Oracle Home.

Enjoy..


Oracle ACFS Filesystem managed by OHAS on Oracle Restart

Bad news from Oracle 11.2.0.2:

Oracle ACFS and Oracle Restart


Oracle Restart does not support root-based Oracle ACFS resources for this release. Consequently, the following operations are not automatically performed:

Loading Oracle ACFS drivers
Mounting Oracle ACFS file systems listed in the Oracle ACFS mount registry
Mounting resource-based Oracle ACFS database home file systems
The Oracle ACFS resources associated with these actions are not created for Oracle Restart configurations.

While Oracle ACFS resource management is fully supported for Oracle Grid Infrastructure configurations, the Oracle ACFS resource-based management actions must be replaced with alternative, sometimes manual, operations in Oracle Restart configurations.

http://download.oracle.com/docs/cd/E11882_01/server.112/e16102/asmfs_extra.htm#CACBDGCC

Big Question!
Why use ACFS (i.e Cluster Filesystem) on Single Node Installation? It makes no sense!!!
Using O.S filesystem I have all resources of ACFS (Performance, Manageability and Availability).
And using ACFS on Single Node (Oracle Restart) is very poor many task must be performed manually (like load drivers, mountfilesystem,etc) .
Particularly I do not use ACFS on Single Nodes, I see no need for that.

I created this post just for fun and to help those  wich use ACFS on Oracle Restart.

BUT…
ACFS has a feature that does not have on S.O filesystem.

ACFS Snapshots

ACFS provides snapshotting capability for the respective filesystem. This snapshot uses the First Copy-on-Write (FCOW) methodology to enable a consistent, version-based, online view of the source filesystem.
Snapshots are immutable views of the source file system as it appeared at a specific point in time.
Snapshots are initially a sparse filesystem, as the source filesystem files change, the before-image extent of that file is copied into the snapshot directory.
Accessing the snapshot will always provide a point-in-time view of a file, thus ACFS snapshots can be very useful for file-based recovery or for filesystem backups. If file level recovery is needed (for the base filesystem), it can be performed using standard file copy or replace commands.
A possible use case scenario for snapshots can be to create a consistent recovery point set between the database ORACLE_HOME and the database.
This is useful, for example, when a recovery point needs to be established before applying a database patchset.

Important: Be very careful using Oracle ACFS filesystem to ORACLE_HOME. When you create a database using this ORACLE_HOME this database is automatically registred on OHAS (Oracle Restart). If you restart Server OHAS will try start this database automatically, but the filesystem (ACFS) of ORACLE_HOME is not mounted automatically must be done manually , therefore all databases of this ORACLE_HOME must be started manually too.

I’ll show you how to automate the process of load and drivers of ACFS and mounting/umounting filesystem using OHAS.

LINUX PLATAFORM

Create a ACFS Filesystem.

I will create a ACFS Filesystem on DISKGROUP “DG_ORAHOME11GR2_1”

Create ACFS for Database Home

Configure Oracle Home location, Size (GB), user and group.

Creating ACFS…

ASMCA does not support root-based Oracle ACFS resources, so must be done manually.

Oracle automatically generate a script to mount this filesystem.

Volume created…and ENABLED

ACFS MOUNTED…..

If you restart your Server, the ASCS drivers will be not loaded automatically, therefore te TAB (Volumes and ASM Cluster File Systems) of ASMCA will be inactive.

Loading ACFS Drivers Automatically

To configure Autostart of ACFS drivers during node reboot on Linux

Add an acfsload start command in /etc/rc.local.so they are automatically loaded during node bootup

cat /etc/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local

/sbin/modprobe hangcheck-timer
/u01/app/grid/product/11.2.0/grid/bin/acfsload start –s
devlabel restart

Setting up  AUTO MOUNT/AUTO UMOUNT of Filesystem on OHAS. 

We can use OHAS to start, stop, monitor and restart applications. Using feature RESOURCE of OHAS.

Let’s create a script to mount/umount ACFS Filesystem.

Using Oracle user (oracle)

cd /u01/app/grid/product/11.2.0/grid/crs/script

vi acfs_ORAHOME11GR2.sh
#!/bin/sh
case $1 in
'start')
# Check if Volume is Enabled if not enable volume

if [ ! -f /dev/asm/db112_dbh1-220 ]; then
ORACLE_HOME=/u01/app/grid/product/11.2.0/grid
ORACLE_SID=+ASM
$ORACLE_HOME/bin/asmcmd <<EOF
volenable -G DG_ORAHOME11GR2_1 db112_dbh1
EOF
fi
# Mount filesystem

/usr/bin/sudo /bin/mount -t acfs /dev/asm/db112_dbh1-220 /u01/app/oracle/product/11.2.0/dbhome_1

# Change permission of Filesystem
 if [ $? = "0" ]; then
 /usr/bin/sudo  /bin/chown oracle:oinstall /u01/app/oracle/product/11.2.0/dbhome_1
 /usr/bin/sudo  /bin/chmod 775 /u01/app/oracle/product/11.2.0/dbhome_1
  exit 0
 fi
 RET=$?
;;

# Stop Filesystem
'stop')
/usr/bin/sudo /bin/umount -t acfs /dev/asm/db112_dbh1-220
 RET=$?
;;
'clean')
/usr/bin/sudo  /bin/umount -t acfs /dev/asm/db112_dbh1-220
  RET=$?
    ;;
# Check if Filesystem is Mounted
'check')
  OUTCMD=`/bin/mount |grep '/dev/asm/db112_dbh1-220' |wc -l`
  if [ $OUTCMD = 1 ]; then
  RET=0
  else
  RET=1
  fi
;;
esac
# 0: success; 1 : error
if [ $RET -eq 0 ]; then
exit 0
else
exit 1
fi

I used the “sudo” to perform root-tasks. So you must allow user oracle perform root actions without password.

Using root privileges (or root user)  type “visudo” to edit sudoers file.

# visudo

Comment the line "Defaults    requiretty"
# Defaults    requiretty

Add user Oracle above this:
# %wheel        ALL=(ALL)       NOPASSWD: ALL
oracle          ALL=(ALL)       NOPASSWD: /bin/chown, /bin/chmod, /bin/mount, /bin/umount

Registering Resource in OHAS

Register Resource “acfs.orahome11gR2_1.fs” on OHAS creating dependency of DISKGROUP “DG_ORAHOME11GR2_1”

START_DEPENDENCIES: Specifies a set of relationships that OHAS considers when starting a resource.
You can specify a space-separated list of dependencies on several resources and resource types on which a particular resource can depend.
hard: Specify a hard start dependency for a resource when you want the resource to start only when a particular resource or resource of a particular type starts.
Oracle recommends that resources with hard start dependencies also have pullup start dependencies.
pullup: When you specify the pullup start dependency for a resource, then this resource starts as a result of named resources starting.

So, We must specify the START_DEPENDENCIES referencing to DISKGROUP DG_ORAHOME11GR2_1 where are ORACLE_HOME filesystem, that’s means when you try start the resource “acfs.orahome11gR2_1.fs” the DISKGROUP DG_ORAHOME11GR2_1 must be started, if DISKGROUP DG_ORAHOME11GR2_1 is not started “pullup” will try start DISKGROUP DG_ORAHOME11GR2_1 before try start resource “acfs.orahome11gR2_1.fs”.

STOP_DEPENDENCIES: Specifies a set of relationships that OHAS considers when stopping a resource.
hard: Specify a hard stop dependency for a resource that you want to stop when named resources or resources of a particular resource type stop.

So, if we try to stop (using OHAS) DISKGROUP DG_ORAHOME11GR2_1 when ACFS (ORACLE_HOME) remain mounted (ONLINE) OHAS must raise CRS-2529

CRS-2529: Unable to act on  because that would require stopping or relocating ,
but the force option was not specified

 

crsctl add resource acfs.orahome11gR2_1.fs \
-type local_resource \
-attr "\
ACTION_SCRIPT=/u01/app/grid/product/11.2.0/grid/crs/script/acfs_ORAHOME11GR2.sh,\
AUTO_START=always,\
START_TIMEOUT=100,\
STOP_TIMEOUT=100,\
CHECK_INTERVAL=10,\
START_DEPENDENCIES=hard(ora.DG_ORAHOME11GR2_1.dg)pullup(ora.DG_ORAHOME11GR2_1.dg),\
STOP_DEPENDENCIES='hard(ora.DG_ORAHOME11GR2_1.dg)'"

More info about attributes used here you can found here:
http://download.oracle.com/docs/cd/E11882_01/rac.112/e16794/resatt.htm

$ crsctl status resource acfs.orahome11gR2_1.fs
NAME=acfs.orahome11gR2_1.fs
TYPE=local_resource
TARGET=OFFLINE
STATE=OFFLINE

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       54G  9.0G   42G  18% /
/dev/sda1              99M   31M   64M  33% /boot
tmpfs                 2.5G  176M  2.3G   8% /dev/shm

# Mounting Filesystem with OHAS:
$ crsctl start resource acfs.orahome11gR2_1.fs
CRS-2672: Attempting to start 'acfs.orahome11gR2_1.fs' on 'macedonia'
CRS-2676: Start of 'acfs.orahome11gR2_1.fs' on 'macedonia' succeeded

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       54G  9.0G   42G  18% /
/dev/sda1              99M   31M   64M  33% /boot
tmpfs                 2.5G  176M  2.3G   8% /dev/shm
/dev/asm/db112_dbh1-220
                       15G  10M   15G  1% /u01/app/oracle/product/11.2.0/dbhome_1

$ crsctl status resource acfs.orahome11gR2_1.fs
NAME=acfs.orahome11gR2_1.fs
TYPE=local_resource
TARGET=ONLINE
STATE=ONLINE on macedonia

Trying stop DISKGROUP without umount filesystem

$ srvctl stop diskgroup -g DG_ORAHOME11GR2_1
PRCR-1065 : Failed to stop resource ora.DG_ORAHOME11GR2_1.dg
CRS-2529: Unable to act on 'ora.DG_ORAHOME11GR2_1.dg' because that would require stopping or relocating 'acfs.orahome11gR2_1.fs', but the force option was not specified

Now you can restart your server and make sure wich the filesystem will be mounted at startup of OHAS.
If everything is ok you can install Oracle Software on ACFS.

I created a Database (db11g) using ORACLE_HOME on ACFS.

When DBCA create a Database (Oracle Restart) he doesn’t create dependencies between ACFS Mount and Database.

So, when OHAS start a Database (db11g)  he will try start only DISKGROUPs where database files are and just it. As ORACLE_HOME is not mounted Database will not start automatically.

We must create a dependencie between ACFS Mount and Database (like oracle does with Diskgroup).

Using Resource name of Database (ora.db11g.db) let’s create dependencies.

To Start Database (db11g) the ACFS Filesystem must be mounted. And to Stop ACFS Filesystem Database must be OFFLINE. If we try start Database without ACFS Filesystem mounted  OHAS will try start ACFS Filesystem before start Database.

$ crsctl modify resource ora.db11g.db -attr "\
START_DEPENDENCIES='hard(acfs.orahome11gR2_1.fs)pullup(acfs.orahome11gR2_1.fs)',\
STOP_DEPENDENCIES='hard(acfs.orahome11gR2_1.fs)'"

Creating this dependencies does not affect config of Database in OHAS.

$ srvctl config database -d db11g -a
Database unique name: db11g
Database name: db11g
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DG_DATA/db11g/spfiledb11g.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DG_DATA,DG_FRA
Services:
Database is enabled

Testing:

All Online:

crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
acfs.orahome11gR2_1.fs
               ONLINE  ONLINE       macedonia
ora.DG_DATA.dg
               ONLINE  ONLINE       macedonia
ora.DG_FRA.dg
               ONLINE  ONLINE       macedonia
ora.DG_ORAHOME11GR2_1.dg
               ONLINE  ONLINE       macedonia
ora.LISTENER.lsnr
               ONLINE  ONLINE       macedonia
ora.asm
               ONLINE  ONLINE       macedonia                Started
ora.ons
               OFFLINE OFFLINE      macedonia
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       macedonia
ora.db11g.db
      1        ONLINE  ONLINE       macedonia                Open
ora.diskmon
      1        ONLINE  ONLINE       macedonia
ora.evmd
      1        ONLINE  ONLINE       macedonia

Stopping Diskgroup DG_ORAHOME11GR2_1

$ srvctl stop database -d db11g
$ crsctl stop resource acfs.orahome11gR2_1.fs
CRS-2673: Attempting to stop 'acfs.orahome11gR2_1.fs' on 'macedonia'
CRS-2677: Stop of 'acfs.orahome11gR2_1.fs' on 'macedonia' succeeded
$ srvctl stop diskgroup -g DG_ORAHOME11GR2_1

### Database, Filesystem and Diskgroup is Offline
$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
acfs.orahome11gR2_1.fs
               OFFLINE OFFLINE      macedonia
ora.DG_DATA.dg
               ONLINE  ONLINE       macedonia
ora.DG_FRA.dg
               ONLINE  ONLINE       macedonia
ora.DG_ORAHOME11GR2_1.dg
               OFFLINE OFFLINE      macedonia
ora.LISTENER.lsnr
               ONLINE  ONLINE       macedonia
ora.asm
               ONLINE  ONLINE       macedonia                Started
ora.ons
               OFFLINE OFFLINE      macedonia
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       macedonia
ora.db11g.db
      1        OFFLINE OFFLINE                               Instance Shutdown
ora.diskmon
      1        ONLINE  ONLINE       macedonia
ora.evmd
      1        ONLINE  ONLINE       macedonia

Starting only database, all resources dependents must be UP automatically.


$ srvctl start database -d db11g

$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
acfs.orahome11gR2_1.fs
               ONLINE  ONLINE       macedonia
ora.DG_DATA.dg
               ONLINE  ONLINE       macedonia
ora.DG_FRA.dg
               ONLINE  ONLINE       macedonia
ora.DG_ORAHOME11GR2_1.dg
               ONLINE  ONLINE       macedonia
ora.LISTENER.lsnr
               ONLINE  ONLINE       macedonia
ora.asm
               ONLINE  ONLINE       macedonia                Started
ora.ons
               OFFLINE OFFLINE      macedonia
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       macedonia
ora.db11g.db
      1        ONLINE  ONLINE       macedonia                Open
ora.diskmon
      1        ONLINE  ONLINE       macedonia
ora.evmd
      1        ONLINE  ONLINE       macedonia

Everthing is fine.

This solution is not supported by Oracle this is a Workaround to start ACFS Filesystem at boot of Server

Enjoy…