IBM POWER7 AIX and Oracle Database performance considerations


This post is intended to provide DBA's with advice and website links to assist with IBM Power Systems running in an Oracle environment.
Most issues that show up on POWER7 are the result of not following best practices advice that applies to all Power Systems generations.

Metalink: IBM POWER7 AIX and Oracle Database performance considerations -- 10g & 11g (Doc ID 1507249.1)

IBM White paper: IBM POWER7 AIX and Oracle Database performance considerations


What is slow RMAN or Media Management Library?

Backup execution time  is slow.. where is the problem?

When we perform backup using third party software and backup of database is slow, there always is some uncertain who is causing the slowness.

The Database Administrator (DBA) says: is Media Management Library (aka MML) and Backup Operator says: is RMAN.

To end this conflict I ​​will show how to identify where is the problem.

I'll use the term RMAN (like RMAN spend time), but actually means Database spend time. Because RMAN is only a client. So typically the database is slow and not RMAN Client is slow.

Note: I will not diagnose what is causing it, I will only  help you  identify whether the problem is, in  MML or RMAN.

Media Management

The Oracle Media Management Layer (MML) API lets third-party vendors build a media manager, software that works with RMAN and the vendor's hardware to allow backups to sequential media devices such as tape drives. A media manager handles loading, unloading, and labeling of sequential media such as tapes.

RMAN Interaction with a Media Manager

RMAN does not issue specific commands to load, label, or unload tapes. When backing up, RMAN gives the media manager a stream of bytes and associates a unique name with this stream. When RMAN must restore the backup, it asks the media manager to retrieve the byte stream. All details of how and where that stream is stored are handled entirely by the media manager. For example, the media manager labels and keeps track of the tape and names of files on each tape, and automatically loads and unloads tapes, or signals an operator to do so.

RMAN provides a list of files requiring backup or restore to the media manager, which in turn makes all decisions regarding how and when to move the data.

Before RMAN making a call to any of functions in the media management API, the server create a EVENT WAIT. So, These with  EVENT WAIT  is possible to get the number of  wait (in seconds or number) that the server has been waiting for this call to return.

So, we can calculate it and find out how much time RMAN spend waiting MML (e.g writing  or querying a backup piece filename) processing request and return to RMAN.

Complete list  EVENT of MML

Oracle 11.2 or above:

SELECT NAME
FROM   V$EVENT_NAME
WHERE  NAME LIKE '%MML%';
NAME
----------------------------------------
Backup: MML initialization
Backup: MML v1 open backup piece
Backup: MML v1 read backup piece
Backup: MML v1 write backup piece
Backup: MML v1 close backup piece
Backup: MML v1 query backup piece
Backup: MML v1 delete backup piece
Backup: MML create a backup piece
Backup: MML commit backup piece
Backup: MML command to channel
Backup: MML shutdown
Backup: MML obtain textual error
Backup: MML query backup piece
Backup: MML extended initialization
Backup: MML read backup piece
Backup: MML delete backup piece
Backup: MML restore backup piece
Backup: MML write backup piece
Backup: MML proxy initialize backup
Backup: MML proxy cancel
Backup: MML proxy commit backup piece
Backup: MML proxy session end
Backup: MML datafile proxy backup?
Backup: MML datafile proxy restore?
Backup: MML proxy initialize restore
Backup: MML proxy start data movement
Backup: MML data movement done?
Backup: MML proxy prepare to start
Backup: MML obtain a direct buffer
Backup: MML release a direct buffer
Backup: MML get base address
Backup: MML query for direct buffers

Previous version of Oracle Database 11.2 the Event name MML does not exists because it’s was changed on version 11.2 from %STB% to %MML%.

So, If you are using Oracle 11.1 or previous you can query V$EVENT_NAME where NAME like '%sbt%'.

SELECT NAME
FROM   V$EVENT_NAME
WHERE  NAME LIKE '%sbt%';

Backup: sbtinit
Backup: ssbtopen
Backup: ssbtread
Backup: ssbtwrite
Backup: ssbtbackup
.
.
.

So, lets start...
Oracle store statistics (cumulative, since database was started) of these wait on v$system_event. I always use GV$ because is very common we admin RAC env on this days.

Before start backup I'll take a snapshot intial of gv$system_event...by creating a table RMAN_MML_EVENT_T1.

Click on icon "View Source" to see formated text.

CREATE TABLE  RMAN_MML_EVENT_T1 AS
SELECT inst_id,
  event,
  TOTAL_WAITS,
  TOTAL_TIMEOUTS,
  TIME_WAITED,
  AVERAGE_WAIT,
  TIME_WAITED_MICRO,
  sysdate as SNAPSHOT_TIME
FROM gv$system_event
WHERE event LIKE 'Backup%';

SQL> select * from RMAN_MML_EVENT_T1;

   INST_ID EVENT                                   TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO SNAPSHOT_TIME
---------- --------------------------------------- ----------- -------------- ----------- ------------ ----------------- -----------------
         1 Backup: MML initialization                      371              0       54365       146.54         543651136 08-08-12 17:11:05
         1 Backup: MML create a backup piece               450              0        4827        10.73          48270960 08-08-12 17:11:05
         1 Backup: MML commit backup piece                 450              0        7417        16.48          74172281 08-08-12 17:11:05
         1 Backup: MML shutdown                            371              0          47          .13            469267 08-08-12 17:11:05
         1 Backup: MML query backup piece                  894              0       11222        12.55         112222166 08-08-12 17:11:05
         1 Backup: MML extended initialization             371              0           0            0              3655 08-08-12 17:11:05
         1 Backup: MML delete backup piece                 444              0        5348        12.05          53480530 08-08-12 17:11:05
         1 Backup: MML write backup piece              1378078              0     3053683         2.22        3.0537E+10 08-08-12 17:11:05

8 rows selected.

I started backup using RMAN and MML (Tivoli Storage Manager). When backup finished you can query V$RMAN_BACKUP_JOB_DETAILS to get accurate time of backup

SELECT START_TIME,
  END_TIME,
  ROUND(INPUT_BYTES  /1024/1024/1024,2) IMPUT_GBYTES ,
  ROUND(OUTPUT_BYTES /1024/1024/1024,2) OUTPUT_GBYTES,
  INPUT_TYPE,
  ELAPSED_SECONDS
FROM V$RMAN_BACKUP_JOB_DETAILS
WHERE TRUNC(START_TIME) = TRUNC(SYSDATE)
AND INPUT_TYPE LIKE 'DB%';

START_TIME        END_TIME          IMPUT_GBYTES OUTPUT_GBYTES INPUT_TYPE    ELAPSED_SECONDS
----------------- ----------------- ------------ ------------- ------------- ---------------
08-08-12 17:23:44 08-08-12 17:26:38        12.85         10.06 DB FULL                   174

In my case the backup full take 174 seconds to backup read 12.85GB and Write on MML 10.06GB of data

So, after backup finish I take the 2nd snapshot by creating the table RMAN_SNAPSHOT_T2.


CREATE TABLE  RMAN_SNAPSHOT_T2 AS
SELECT inst_id,
  event,
  TOTAL_WAITS,
  TOTAL_TIMEOUTS,
  TIME_WAITED,
  AVERAGE_WAIT,
  TIME_WAITED_MICRO,
  sysdate as SNAPSHOT_TIME
FROM gv$system_event
WHERE event LIKE 'Backup%';

SQL> select * from RMAN_MML_EVENT_T2;

   INST_ID EVENT                                   TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO SNAPSHOT_TIME
---------- --------------------------------------- ----------- -------------- ----------- ------------ ----------------- -----------------
         1 Backup: MML initialization                      373              0       54665       146.56         546652333 08-08-12 17:27:45
         1 Backup: MML create a backup piece               454              0        4860        10.71          48604759 08-08-12 17:27:45
         1 Backup: MML commit backup piece                 454              0        7482        16.48          74820999 08-08-12 17:27:45
         1 Backup: MML shutdown                            373              0          47          .13            471590 08-08-12 17:27:45
         1 Backup: MML query backup piece                  900              0       11281        12.53         112808077 08-08-12 17:27:45
         1 Backup: MML extended initialization             373              0           0            0              3665 08-08-12 17:27:45
         1 Backup: MML delete backup piece                 446              0        5373        12.05          53727006 08-08-12 17:27:45
         1 Backup: MML write backup piece              1419274              0     3067298         2.16        3.0673E+10 08-08-12 17:27:45

8 rows selected.

Now I can calculate the values from RMAN_MML_EVENT_T2 minus RMAN_MML_EVENT_T1 to get the real time spend on MML.
Note:
EVENT: Name of the wait event
TOTAL_WAITS: Total number of waits for the event
TOTAL_TIMEOUTS: Total number of timeouts for the event
TIME_WAITED: Total amount of time waited for the event (in hundredths of a second)
AVERAGE_WAIT: Average amount of time waited for the event (in hundredths of a second)
TIME_WAITED_MICRO: Total amount of time waited for the event (in microseconds)

SELECT t1.inst_id,
  t1.event,
  t2.total_waits       - t1.total_waits total_waits,
  t2.total_timeouts    -t1.total_timeouts total_timeouts,
  t2.time_waited       - t1.time_waited time_waited,
  t2.time_waited_micro - t1.time_waited_micro time_waited_micro
FROM RMAN_MML_EVENT_T1 T1,
  RMAN_MML_EVENT_T2 T2
WHERE t1.inst_id = t2.inst_id
AND t1.event     = t2.event;

   INST_ID EVENT                                   TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED TIME_WAITED_MICRO
---------- --------------------------------------- ----------- -------------- ----------- -----------------
         1 Backup: MML initialization                        2              0         300           3001197
         1 Backup: MML create a backup piece                 4              0          33            333799
         1 Backup: MML commit backup piece                   4              0          65            648718
         1 Backup: MML shutdown                              2              0           0              2323
         1 Backup: MML query backup piece                    6              0          59            585911
         1 Backup: MML extended initialization               2              0           0                10
         1 Backup: MML delete backup piece                   2              0          25            246476
         1 Backup: MML write backup piece                41196              0       13615         136141912

8 rows selected.

As I can see above the MML spend more time writing backup piece.

So, I'll sum the time to get total time spend on MML.

SELECT SUM (total_waits) total_waits,
  SUM(total_timeouts) total_timeouts ,
  SUM (time_waited)/100 time_waited_in_second,
  SUM (time_waited_micro) time_waited_micro
FROM
  (SELECT t1.inst_id,
    t1.event,
    t2.total_waits       - t1.total_waits total_waits,
    t2.total_timeouts    -t1.total_timeouts total_timeouts,
    t2.time_waited       - t1.time_waited time_waited,
    t2.time_waited_micro - t1.time_waited_micro time_waited_micro
  FROM RMAN_MML_EVENT_T1 T1,
    RMAN_MML_EVENT_T2 T2
  WHERE t1.inst_id = t2.inst_id
  AND t1.event     = t2.event
  )

TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED_IN_SECOND TIME_WAITED_MICRO
----------- -------------- --------------------- -----------------
      41218              0                140.97         140960346

Calculating time total of backup window, time spend on MML and time spend of RMAN.

Note: TIME_SPEND_BY_RMAN = (ELAPSED_SECOND_BACKUP-TIME_SPEND_BY_MML_SECOND)

ELAPSED_SECONDS_BACKUP          TIME_SPEND_BY_MML_SECOND       TIME_SPEND_BY_RMAN_SECOND
------------------------------ ------------------------------ -------------------
174                             140.97                         33.03

Summarizing:
Total time of backup : 174
Time spend by MML: 141
Time spend by RMAN : 33

If this backup is slow is because MML take (141*100/174) 81% of time spend of backup window.

Additional info:
As my backup was done over Lan:
(10.06GB * 1024 = 10301MB)
10301MB / 144 = 71Mbytes/second

As I'm using network interface of 1 Gbit I can consider a normal throughput.

Also you can monitoring in real time where is wait.

Just execute this script above:

Note : if you are using previous version of 11.2 change %MML% to %sbt%.

vi monitoring_mml.sh
sqlplus -s sys/<password>@<db_name> as sysdba<<EOF
set echo off
COLUMN EVENT FORMAT a17
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a15
COLUMN CLIENT_INFO FORMAT a30
set linesize 200

select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') actual_date from dual;

SELECT p.SPID, sw.EVENT, sw.SECONDS_IN_WAIT AS SEC_WAIT, 
       sw.STATE, CLIENT_INFO
FROM   gV\$SESSION_WAIT sw, gv\$SESSION s, gV\$PROCESS p
WHERE  sw.EVENT LIKE '%MML%'
AND    s.SID=sw.SID
AND    s.PADDR=p.ADDR;
EOF
exit

Using shell execute the command above, and you will see in real time the wait on MML.

while true
do
sh monitoring_mml.sh
sleep 1
done

.

Find us on Google+


Oracle Real Application Clusters on IBM AIX – Best practices in memory tuning and configuring for system stability

Customers who experience Oracle Real Application Clusters (RAC) node evictions due to  excessive AIX kernel paging should carefully review and implement these recommended best practices. Testing and experience have found that memory over commitments may
cause scheduling delays for Oracle’s ‘oprocd’ process in Oracle RAC versions prior to 11.2 which may result in node evictions. Implementing all of these recommendations will reduce scheduling delays and corresponding oprocd initiated evictions for Oracle RAC versions prior to 11.2. For Oracle RAC versions 11.2 and later, implementing all of these recommendations will ensure optimal performance and scalability

 

http://www.oracle.com/technetwork/products/clusterware/overview/rac-aix-system-stability-131022.pdf

If link above is broke see below  click below:

rac-aix-system-stability-131022

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:/ &gt; 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:/ &gt;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:/ &gt;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

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…


IBM – Live Partition Mobility for Oracle RAC

This paper documents the concept and recommendation of using Live Partition Mobility (LPM) in an Oracle RAC environment.
It describes the configuration of the IBM Power systems infrastructure and Oracle RAC to perform the Live Partition Mobility of an Oracle RAC node.
The paper describes two scenarios, the first is given as example for test purpose in order to setup the configuration and understand the interaction of all components.
The second scenario is the officially supported LPM process for an Oracle RAC environment.
This paper is illustrated with a real example of the LPM of a RAC node from a Porwer6 processor based source server to a Power7 target server.

Introduction

Live Partition Mobility (LPM) is a feature of PowerVM Enterprise Edition which allows for moving an LPAR from an IBM Power system to another physical IBM Power system.
LPM increases availability of the application and improves workload management. LPM improves flexibility of the entire infrastructure as you are able to continuous run the applications during planned maintenance of your server, by migrating without disruption the logical partitions to another server.
Also you are able to easily manage the applications workload by migrating LPARs and get free CPU and Memory resources for your most important workload production running on the source server.
Both source and target systems can be POWER6 or POWER7 processor based. The I/O adapters configured in the AIX LPAR must be defined as virtual devices and requires that the network and the SAN disk be accessed though a Virtual I/O Server Partition. LPM feature is not compatible with physical resources, so they have to be removed at least for the LPM operation, and if any, reattached to the LPAR after the migration.
Remember that you can keep physical adapters configured in the LPAR and also configure virtual adapters as a backup path for the network access and heterogeneous multi-path for the SAN disk access.
Also all the disks have to be defined from the SAN and shared to VIO servers of both source and target servers.
LPM process consist of several steps, such as reading the LPAR configuration on the source server to create it on the target server, creating the virtual devices at the target VIO Server, copying the physical memory blocks from the source to the target server, activating the LPAR on the target server and starting the AIX processes on the target server partition.
Once the processes are running on the target server partition, the virtual devices corresponding to the LPAR are removed from the source VIO Server and the LPAR is deleted on the source server.
The major migration step is the copy of the logical memory blocks (LMBs) though the network while AIX processes are running. At the end of the memory copy, almost 95%, a checkpoint operation is done and the processes are run from the target server. Depending on the amount of memory assigned to the LPAR and on the memory activity of the running processes, this checkpoint operation may freeze the processes for a short duration.

From an Oracle RAC environment point of view, this step is the most critical and requires some certification tests. That is the reason why as the time of writing, LPM is supported for Oracle single instance database and Oracle RAC is not officially supported.
In the following you will see a functional example of LPM operation in Oracle RAC environment, so you can run it as a test without support from both IBM and Oracle.
The supported process for LPM operation in an Oracle RAC environment consists in stopping Oracle for the migration step while keeping AIX alive.

Live Partition Mobility for Oracle RAC

LPM_a_RAC_node_July 27 2011
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..