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

What should I use “srvctl add serverpool” or “crsctl add serverpool”

I saw some doubts as to which utility to use and in what situation we should use.

I searched on some sites related to Oracle and saw that the people is still a bit confused about  which command we should use.

But before start there is a rule to a Clusterware Envorinment:

The “srvctl” is to be used to managed  resources with the prefix  ora.* resources and “crsctl” is to be used  to query or start/stop resources with prefix ora.*, but crsctl is not supported to modify or edit resources with prefix ora.* .

See this note on MOS:

Oracle Clusterware and Application Failover Management [ID 790189.1]

Using crs_* or crsctl commands on resources with the prefix ora.* (resources provided by Oracle) remains unsupported.

 

So, if you created a resource with “srvctl” this resource should be managed only by “srvctl”. If you create a resource with “crsctl” this resource should be managed using “crsctl” command.

Let’s talk about  the concept Policy-Based Cluster.

Oracle Clusterware 11g release 2 (11.2) introduces a different method of managing nodes and resources used by a database called policy-based management.

With Oracle Clusterware 11g release 2 (11.2) and later, resources managed by Oracle Clusterware are contained in logical groups of servers called server pools. Resources are hosted on a shared infrastructure and are contained within server pools. The resources are restricted with respect to their hardware resource (such as CPU and memory) consumption by policies, behaving as if they were deployed in a single-system environment.

Policy-based management:

  • Enables dynamic capacity assignment when needed to provide server capacity in accordance with the priorities you set with policies
  • Enables allocation of resources by importance, so that applications obtain the required minimum resources, whenever possible, and so that lower priority applications do not take resources from more important applications
  • Ensures isolation where necessary, so that you can provide dedicated servers in a cluster for applications and databases

Applications and databases running in server pools do not share resources. Because of this, server pools isolate resources where necessary, but enable dynamic capacity assignments as required. Together with role-separated management, this capability addresses the needs of organizations that have standardized cluster environments, but allow multiple administrator groups to share the common cluster infrastructure.

This is only a concept.

Therefore Oracle divided this concept to be used for two types of configuration

Policy-Managed Database and  Policy-Based Management to non-database.

Policy-Managed Database

A database that you define as a cluster resource. Management of the database is defined by how you configure the resource, including on which servers the database can run and how many instances of the database are necessary to support the expected workload.

To configure Policy managed database, Oracle already have pre-defined configuration for that.

So, the options are limited and specific to Database resources (such as Services,Database).

For that reason Oracle provided “srvctl add serverpool”.

 $ srvctl add serverpool -h

Adds a server pool to the Oracle Clusterware.

Usage: srvctl add srvpool -g <pool_name> [-l <min>] [-u <max>] [-i <importance>] [-n "<server_list>"] [-f]
 -g <pool_name> Server pool name
 -l <min> Minimum size of the server pool (Default value is 0)
 -u <max> Maximum size of the server pool (Default value is -1 for unlimited maximum size)
 -i <importance> Importance of the server pool (Default value is 0)
 -n "<server_list>" Comma separated list of candidate server names
 -f Force the operation even though some resource(s) will be stopped
 -h Print usage
 

http://docs.oracle.com/cd/E11882_01/rac.112/e16795/srvctladmin.htm#BAJDJFCJ

 Policy-Based Management to non-database.

To configure Non-Database resources, Oracle provided another command with much more options “crsctl add serverpool”.

This allow the DBA explore all options which Policy Managed can supply.

 $ crsctl add serverpool -h
 Usage:
 crsctl add serverpool <spName> [[-file <filePath>] | [-attr "<attrName>=<value>[,...]"]] [-i]
 where
 spName Add named server pool
 filePath Attribute file
 attrName Attribute name
 value Attribute value
 -i Fail if request cannot be processed immediately
 -f Force option

http://docs.oracle.com/cd/E11882_01/rac.112/e16794/crsref.htm#CWADD92179

So, we NEVER should not mix the serverpool used by database resource and serverpool used by non-database resource.

Also never use “crsctl” command to change Database Server Pool wich was created by “srvctl”.  Never put a database  in a serverpool created by using “crsctl” command.

Server Pool to database resource must be created by using “srvctl”.

Server Pool to non-database resource must be created by using “crsctl”

Question: Is possible change ora.* resources with “crsctl”?

Yes, It’s possible but not supported by Oracle.

Hope make this clear.

Enjoy…


RACcheck – RAC Configuration Audit Tool

RACcheck is a tool developed by the RAC Assurance development team for use by customers to automate the assessment of RAC systems for known configuration problems and best practices.

RACcheck is a RAC Configuration Audit tool  designed to audit various important configuration settings within a Real Application Clusters (RAC), Oracle Clusterware (CRS), Automatic Storage Management (ASM) and Grid Infrastructure environment. The tool audits configuration settings within the following categories:

  1. OS kernel parameters
  2. OS packages
  3. Many other OS configuration settings important to RAC.
  4. CRS/Grid Infrastructure
  5. RDBMS
  6. ASM
  7. Database parameters
  8. Many other database configuration settings important to RAC.

Features
1. RACcheck is NON-INTRUSIVE and does not change anything in the environment, except as detailed below:

– SSH user equivalence for the RDBMS software owner is assumed to be configured among all the database servers being audited in order for it to execute commands on the remote database server nodes. If the tool determines that this user equivalence is not established it will offer to set it up either temporarily or permanently at the option of the user. If the user chooses to set up SSH user equivalence temporarily then the script will do so for the duration of the execution of the tool but then it will return the system to the state in which it found SSH user equivalence originally. For those wishing to configure SSH user equivalence outside the tool (if not already configured), consult My Oracle Support Note: 372795.1.

– RACcheck creates a number of small output files into which the data necessary to perform the assessment is collected

– RACcheck creates and executes some scripts dynamically in order to accomplish some of the data collection

– RACcheck cleans up after itself any temporary files that are created and not needed as part of the collection.

2. RACcheck interrogates the system to determine the status of the Oracle stack components (ie., Grid Infrastructure, RDBMS, RAC, etc) and whether they are installed and/or running. Depending upon the status of each component, the tool runs the appropriate collections and audit checks. If due to local environmental configuration the tool is unable to properly determine the needed environmental information please refer to the TROUBLESHOOTING section.

3. Watchdog daemon – RACcheck automatically runs a daemon in the background to monitor command execution progress. If, for any reason, one of the commands run by the tool should hang or take longer than anticipated, the monitor daemon kills the hung command after a configurable timeout so that main tool execution can progress. If that happens then the collection or command that was hung is skipped and a notation is made in the log. If the default timeout is too short please see the TROUBLESHOOTING section regarding adjustment of the RAT_TIMEOUT, and RAT_ROOT_TIMEOUT parameters.

4. If RACcheck’s driver files are older than 90 days, the driver files are considered to be “stale” and the script will notify the user of a stale driver file. A new version of the tool and its driver files (kit) must be obtained from MOS Note 1268927.1.

5. When the RACcheck completes the collection and analysis it produces two reports, summary and detailed. A output .zip file is also produced by RACcheck. This output .zip file can be provided to Oracle Support for further analysis if an SR needs to be logged. The detailed report will contain Benefit/Impact, Risk and Action/Repair information. In many cases it will also reference publicly available documents with additional information about the problem and how to resolve it.

6. The results of the audit checks can be optionally uploaded into database tables for reporting purposes. See below for more details on this subject.

7. In some cases customers may want to stage RACcheck on a shared filesystem so that it can be accessed from various systems but be maintained in a single location rather than being copied to each cluster on which it may be used. The default behavior of the tool is to create a subdirectory and its output files in the location where the tool is staged. If that staging area is a read only filesystem or if the user for any reason would like the output to be created elsewhere then there is an environment variable which can be used for that purpose. The RAT_OUTPUT parameter can be set to any valid writable location and the output will be created there.

Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 11.2.0.2 – Release: 10.2 to 11.2

  • Linux x86
  • IBM AIX on POWER Systems (64-bit)
  • Oracle Solaris on SPARC (64-bit)
  • Linux x86-64

To download RAC Check tool use this note on MoS:
RACcheck – RAC Configuration Audit Tool [ID 1268927.1]

Example of report output:

raccheck Report

Enjoy


Tip & Hint: DB_CREATE_FILE_DEST Behavior when using ASM

Recently I saw database admin with issue during the Restore  Database using RMAN and ASM, when database is locate in more than one Diskgroup.
Situation:
The Datafiles and REDO were created in two different diskgroups.
It was necessary to perform a restore of the database, then the dba removed all datafiles in both diskgroup and executed restore the database.
During the restore RMAN was reported restoring the datafiles in the correct diskgroup and  showing original path/file on prompt of RMAN, but at the end of restore  all datafiles were restored in only one diskgroup, all datafiles were moved/renamed to diskgroup   specified in parameter DB_CREATE_FILE_DEST.
Question: It’s a normal behavior?
Yes…When using ASM and Oracle parameters such as DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n the dba must be careful to either use all OMF files or keep track of the files not created OMF. There is an order in which files are created with these parameters.
By default, when create a Database using DBCA storing datafiles in  ASM the parameter DB_CREATE_FILE_DEST  is set during creation of database, the parameter  DB_CREATE_ONLINE_LOG_DEST_n is not set.

Datafiles Behavior

The restore behavior is the same as with file creation behavior for OMF files. When OMF files are restored, they follow an order of precedence. That order is:

  1. If “SET NEWNAME” is specified, RMAN will use that name for restore.
  2.  If the original file exists, RMAN will use the original filename for restore.
  3.  If the DB_CREATE_FILE_DEST is set and original filename does not exist, RMAN will use the diskgroup name specified
  4.  If no DB_CREATE_FILE_DEST is set and the original file does not exist, then RMAN will create another name for that file in the original disk group.

So, If you do not want surprise after or during the restore, you must unset the parameter DB_CREATE_FILE_DEST before start restore.

alter system set DB_CREATE_FILE_DEST='' scope=memory;

After Restore/Recover (i.e during open database) you can have suprise if DB_CREATE_FILE_DEST is not set.

So, I recommend you set DB_CREATE_FILE_DEST  between   after RECOVER DATABASE and before “OPEN RESETLOGS”  or set  DB_CREATE_ONLINE_LOG_DEST_n, because during “open resetlogs” Oracle will create Online Logs files if it does not exists.

Online Logs (Redo) Behavior

 When OMF files are restored, they follow an order of precedence. That order is:

  1. If the original file exists, RMAN will use the original filename for restore.
  2. If the DB_CREATE_ONLINE_LOG_DEST_n  is set and original filename does not exist, RMAN will use the diskgroup name specified in DB_CREATE_ONLINE_LOG_DEST_n
  3. If no DB_CREATE_ONLINE_LOG_DEST_n  is set and the original file does not exist  but  DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST  (FRA) is set, RMAN will use the diskgroup name specified in both (DB_CREATE_FILE_DEST & DB_RECOVERY_FILE_DEST)  to multiplex ONLINELOG.
  4. If no DB_CREATE_ONLINE_LOG_DEST_n and DB_RECOVERY_FILE_DEST   is set and the original file does not exist  but  DB_CREATE_FILE_DEST is set,  RMAN will use the diskgroup name specified in DB_CREATE_FILE_DEST.
  5. If no DB_CREATE_ONLINE_LOG_DEST_n and DB_CREATE_FILE_DEST   is set and the original file does not exist  but  DB_RECOVERY_FILE_DEST is set,  RMAN will use the diskgroup name specified in DB_RECOVERY_FILE_DEST.
  6. If no DB_CREATE_ONLINE_LOG_DEST_n, DB_CREATE_FILE_DEST and  DB_RECOVERY_FILE_DEST is set and the original file does not exist, Oracle will raise “ORA-19801: initialization parameter DB_RECOVERY_FILE_DEST is not set” during “alter database open resetlogs“.
Hope this help

Enjoy…


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…


Oracle Architecture (9i,10g,11g) and Tuning on AIX (5.3,6.1,7.1)

This paper is intended for IBM Power Systems customers, IBM Technical Sales Specialists, and consultants who are interested in learning more the requirements involved in building and tuning an Oracle RDBMS system for optimal performance on the AIX platform.

This white paper contains best practices which have been collected during the extensive period of time my team colleagues and I have spent working in the Oracle RDBMS based environment. It is focused on the AIX versions 5.3, 6.1, 7.1 and Oracle 9i, 10g and 11g.

This paper begins with a short descript ion of the most important Oracle DB architectural elements. It continues with an overview of the AIX-related tuning elements that are most crucial for optimal DB activity.

This document can be expanded into many different OS or DB-related directions. Addit ional informationon related topics is included in Appendix A of this paper, as well as references to supporting documentation. However, this paper is not focused on the application tuning area. Application performance tuning is a subject too broad to be covered in a white paper of this length.

See link below..
Oracle Architecture and Tuning on AIX (v 2.10)
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100883
Enjoy..

Enhancing Oracle Database Protection With EMC RecoverPoint

This paper explains the benefits of using EMC RecoverPoint local and remote replication to provide operation and disaster recovery of Oracle environments.

RecoverPoint provides crash-consistent and application-consistent recovery points that can be utilized in response to a number of possible scenarios, enhancing the native availability within an Oracle environment. Oracle support third-party enterprise replication techologies to protect Oracle environments.

EM RecoverPoint provides full support for data replication and disaster recovery when working with Oracle Databases. RecoverPoint supports Oracle whether the Oracle databases are stored as raw disks or on a file system.

Click link below

recoverpoint_oracle_db_protect_wp

“RecoverPoint EMC”

Enjoy