Using  compression in a not exist filter is a very bad idea.

A few days ago  ; one of customers had a problem about daily running jobs.

When they rebuilt some tables ; a huge performance problem occured. We had to spent some diagonastcs and analyzes to find out the root cause .

There a a not exist fileter ;

and the tablspace was default compression enabled ;  when the filter table was re-created it was in a compressed format ; which caused .

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

| Id  | Operation                         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | INSERT STATEMENT                  |                  |       |       |   150M(100)|          |        |      |            |

|   1 |  LOAD AS SELECT                   |                  |       |       |            |          |        |      |            |

|   2 |   FILTER                          |                  |       |       |            |          |        |      |            |

|   3 |    PX COORDINATOR                 |                  |       |       |            |          |        |      |            |

|   4 |     PX SEND QC (RANDOM)           | :TQ10002         |  9409K|   511M|   628  (35)| 00:00:04 |  Q1,02 | P->S | QC (RAND)  |

|   5 |      HASH JOIN BUFFERED           |                  |  9409K|   511M|   628  (35)| 00:00:04 |  Q1,02 | PCWP |            |

|   6 |       PX RECEIVE                  |                  |  8984K|   171M|   142  (63)| 00:00:01 |  Q1,02 | PCWP |            |

|   7 |        PX SEND HASH               | :TQ10000         |  8984K|   171M|   142  (63)| 00:00:01 |  Q1,00 | P->P | HASH       |

|   8 |         PX BLOCK ITERATOR         |                  |  8984K|   171M|   142  (63)| 00:00:01 |  Q1,00 | PCWC |            |

|   9 |          TABLE ACCESS STORAGE FULL| ACCTSTATUS       |  8984K|   171M|   142  (63)| 00:00:01 |  Q1,00 | PCWP |            |

|  10 |       PX RECEIVE                  |                  |  7383K|   260M|   471  (25)| 00:00:03 |  Q1,02 | PCWP |            |

|  11 |        PX SEND HASH               | :TQ10001         |  7383K|   260M|   471  (25)| 00:00:03 |  Q1,01 | P->P | HASH       |

|  12 |         PX BLOCK ITERATOR         |                  |  7383K|   260M|   471  (25)| 00:00:03 |  Q1,01 | PCWC |            |

|  13 |          TABLE ACCESS STORAGE FULL| KEY_SUBSCRIPTION |  7383K|   260M|   471  (25)| 00:00:03 |  Q1,01 | PCWP |            |

|  14 |    TABLE ACCESS BY INDEX ROWID    | SUBSCR_STATUS    |     1 |    35 |    16   (0)| 00:00:01 |        |      |            |

|  15 |     INDEX RANGE SCAN              | PK_SUBSCR_STATUS |    13 |       |     3   (0)| 00:00:01 |        |      |            |

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

on the left one ;   1M record is searhed just in 71 seconds;
on the right one; 185k record is searhed  in 125 seconds ; and counting on

roughtly  10X worse. 

how to clear copy/multiple restored datafile copies

Let’s say you tried a restore , some files have been restored but operation has interrupted.

All  files in ASM and generated names differ during eact restore attempt. Or you copied files manually via asmcmd ,
but you are not sure which files in use, which ones are interrupted restore sessions ruin ?

A clear way is to catalog copies , and manage files via rman. 

Othervise checkin all files one by one is high risk and takes much effort.

Let’s do it ..

Facts:

1- RMAN treats all user-managed backups as image copies. Note that during cataloging, RMAN does not check whether the file was correctly copied by the operating system utility: it just checks the header.
2- You cannot use CATALOG to catalog a file that belongs to a different database.

SQL> select name from v$datafile;

NAME

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

+ORCLDG/orcl/datafile/system.256.900898563

+ORCLDG/orcl/datafile/sysaux.257.900898563

+ORCLDG/orcl/datafile/undotbs1.258.900898563

+ORCLDG/orcl/datafile/users.259.900898563

+ORCLDG/orcl/datafile/tbs1.270.900911509

+ORCLDG/orcl/datafile/tbs2.271.900911533

+ORCLDG/orcl/datafile/tbs2.273.900937577

+ORCLDG/orcl/datafile/tbs1.274.900937597

+ORCLDG/orcl/datafile/tbstelco.314.907164167

SQL> shu immediate;

SQL> startup mount ;    

RMAN> backup as copy datafile 1;   -- datafile image copy via rman

Starting backup at 14.04.2016 23.11.54

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 copy

input datafile file number=00001 name=+ORCLDG/orcl/datafile/system.256.900898563

output file name=+ORCLDG/orcl/datafile/system.322.909184317 tag=TAG20160414T231155 RECID=1 STAMP=909184386

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:16

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=+ORCLDG/orcl/controlfile/backup.324.909184393 tag=TAG20160414T231155 RECID=2 STAMP=909184406

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 14.04.2016 23.13.39

channel ORA_DISK_1: finished piece 1 at 14.04.2016 23.13.46

piece handle=+ORCLDG/orcl/backupset/2016_04_14/nnsnf0_tag20160414t231155_0.325.909184421 tag=TAG20160414T231155 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

Finished backup at 14.04.2016 23.13.46

RMAN>

--- manual copy ; controlfile is not aware of operation (simulation for interrupted restore .. )

ASMCMD [+ORCLDG/orcl/datafile] > cp SYSTEM.256.900898563 system.copybytamer

copying +ORCLDG/orcl/datafile/SYSTEM.256.900898563 -> +ORCLDG/orcl/datafile/system.copybytamer

--- mis usage , everytime I make that mistake J

RMAN> catalog start with '+ORCLDG/orcl/*';    à do not use like in ls -* ; syntax error

searching for all files that match the pattern +ORCLDG/orcl/*

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

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

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

RMAN-03002: failure of catalog command at 04/14/2016 23:17:21

ORA-00600: internal error code, arguments: [krbmosptr_not_osm], [+ORCLDG/orcl/*], [], [], [], [], [], [], [], [], [], []

ORA-15122: ASM file name '+ORCLDG/orcl/*' contains an invalid file number

RMAN> catalog start with '+ORCLDG/orcl';    -- correct usage

searching for all files that match the pattern +ORCLDG/orcl/

List of Files Unknown to the Database

=====================================

File Name: +orcldg/ORCL/spfileORCL.ora

File Name: +orcldg/ORCL/DATAFILE/system.copybytamer

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: +orcldg/ORCL/DATAFILE/system.copybytamer

RMAN> list copy of datafile 1;

List of Datafile Copies

=======================

Key     File S Completion Time     Ckp SCN    Ckp Time           

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

3       1    A 14.04.2016 23.19.52 1754346    14.04.2016 23.09.26

        Name: +ORCLDG/orcl/datafile/system.copybytamer

1       1    A 14.04.2016 23.13.06 1754346    14.04.2016 23.09.26

        Name: +ORCLDG/orcl/datafile/system.322.909184317

        Tag: TAG20160414T231155

You can delete whole copies or defined ones via name or tag.

RMAN>  delete copy of datafile 1 tag=TAG20160414T231155;

RMAN> delete copy of datafile 1  like '+ORCLDG/orcl/datafile/system.copybytamer%';

RMAN>  delete copy of datafile 1 ;

oracleasm same label in different diskgroups prevents mount

After a long time ;  my post .
Again a real-life customer  problem .
Problem definition:
For creating test environment ; a disk based snap is used ; and whole database is processed  via masking ;..etc then used by test teams. And to prevent any developed code,prepared data , a parallel run is required. But both of  old and new databases have to operate in same hardware, because of source unavaliability.  No one wants to spent more than production to test systems :) 

What else ;
oracleasm is used for ASM disk labeling; and because of using storage based snap ; a disk will be presented twice to server (2 identical copied with a time difference). Which causes the label to exist twice in different disks. Also we had change the diskgroup names , because same named diskgroup cannot be mounted simultaneously.  we have a suffix for disk group names  like RCDBv5DG ; RCDBv6DG third from last character represents  year.
5  represents 2015
6  represents 2016


Special thanks to my dear colleague Mustafa YALÇIN , for his contribution .



But if you try to mount; the error raises : 

ORA-15032: not all alterations performedORA-15040: diskgroup is incompleteORA-15042: ASM disk "24" is missing from group number "1" ORA-15042: ASM disk "14" is missing from group number "1" ERROR: ALTER DISKGROUP ALL MOUNT

--------------------- alert log for asm instance
 SQL> ALTER DISKGROUP RCDBDG MOUNT  /* asm agent *//* {0:0:79} */
 NOTE: Assigning number (1,21) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK09)
 NOTE: Assigning number (1,23) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK11)
 NOTE: Assigning number (1,22) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK10)
 NOTE: Assigning number (1,25) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK13)
 NOTE: Assigning number (1,19) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK07)
 NOTE: Assigning number (1,16) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK04)
 NOTE: Assigning number (1,15) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK03)
 NOTE: Assigning number (1,17) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK05)
 NOTE: Assigning number (1,18) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK06)
 NOTE: Assigning number (1,13) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK01)
 NOTE: Assigning number (1,20) to disk (/dev/oracleasm/disks/RCDB_NEW_DISK08)

 NOTE: Disk RCDB_NEW_DISK01 in mode 0x7f marked for de-assignment
 NOTE: Disk  in mode 0x7f marked for de-assignment
 NOTE: Disk RCDB_NEW_DISK03 in mode 0x7f marked for de-assignment
 NOTE: Disk RCDB_NEW_DISK04 in mode 0x7f marked for de-assignment
 NOTE: Disk RCDB_NEW_DISK05 in mode 0x7f marked for de-assignment
 NOTE: Disk RCDB_NEW_DISK06 in mode 0x7f marked for de-assignment
 NOTE: Disk RCDB_NEW_DISK07 in mode 0x7f marked for de-assignment
 NOTE: Disk RCDB_NEW_DISK08 in mode 0x7f marked for de-assignment
 NOTE: Disk RCDB_NEW_DISK09 in mode 0x7f marked for de-assignment
 NOTE: Disk RCDB_NEW_DISK10 in mode 0x7f marked for de-assignment
 NOTE: Disk RCDB_NEW_DISK11 in mode 0x7f marked for de-assignment

before : 

[grid@server03 ~]$ oracleasm  querydisk /dev/mapper/ASMDISK*p1

Device "/dev/mapper/ASMDISK01p1" is marked an ASM disk with the label "RCDB_NEW_DISK20"

command change label= 

oracleasm renamedisk -f /dev/mapper/ASMDISK01p1 RCDB_2016_DISK20

after: 

[grid@server03 ~]$ oracleasm  querydisk /dev/mapper/ASMDISK*p1

Device "/dev/mapper/ASMDISK01p1" is marked an ASM disk with the label "RCDB_2016_DISK20"

Then mount was succesful.

 GROUP_NUMBER NAME            STATE           TYPE                TOTAL_MB      FREE_MB OFFLINE_DISKS USABL
------------ --------------- --------------- --------------- ------------ ------------ ------------- -----
           1 RCDBV5DG        MOUNTED         EXTERN               6655909      1010362             0    
           2 RCDBDG          MOUNTED         EXTERN               7167902       842899             0    

NAME                     GROUP_NUMBER DISK_NUMBER STATE        HEADER_STATU MOUNT_S PATH                
------------------------ ------------ ----------- ------------ ------------ ------- ----------------------
                                    0           0 NORMAL       UNKNOWN      CLOSED  ORCL:RCDB_NEW_DISK20
RCDB_NEW_DISK01                     1          13 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK01
RCDB_NEW_DISK02                     1          14 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK02
RCDB_NEW_DISK03                     1          15 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK03
RCDB_NEW_DISK04                     1          16 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK04
RCDB_NEW_DISK05                     1          17 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK05
RCDB_NEW_DISK06                     1          18 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK06
RCDB_NEW_DISK07                     1          19 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK07
RCDB_NEW_DISK08                     1          20 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK08
RCDB_NEW_DISK09                     1          21 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK09
RCDB_NEW_DISK10                     1          22 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK10
RCDB_NEW_DISK11                     1          23 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK11
RCDB_NEW_DISK12                     1          24 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK12
RCDB_NEW_DISK13                     1          25 NORMAL       MEMBER       CACHED  ORCL:RCDB_NEW_DISK13
RCDB_NEW_DISK20                     2           0 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK20
RCDB_NEW_DISK01                     2          13 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK01
RCDB_NEW_DISK02                     2          14 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK02
RCDB_NEW_DISK03                     2          15 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK03
RCDB_NEW_DISK04                     2          16 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK04
RCDB_NEW_DISK05                     2          17 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK05
RCDB_NEW_DISK06                     2          18 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK06
RCDB_NEW_DISK07                     2          19 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK07
RCDB_NEW_DISK08                     2          20 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK08
RCDB_NEW_DISK09                     2          21 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK09
RCDB_NEW_DISK10                     2          22 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK10
RCDB_NEW_DISK11                     2          23 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK11
RCDB_NEW_DISK12                     2          24 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK12
RCDB_NEW_DISK13                     2          25 NORMAL       MEMBER       CACHED  ORCL:RCDB_2016_DISK13 

How To Change DiskGroup Name (DG name) 10g 11g ASM

11gr2  only binaty software is requşred. You can even  clone from another server,
I  applied many times by only copying , (keeping $OH same)
2 phase operaiton ; first control ; then see no errors , then write to asm disk headers.
dg should be dismounted .


before :


GROUP_NUMBER NAME            STATE           TYPE                TOTAL_MB      FREE_MB OFFLINE_DISKS
------------ --------------- --------------- --------------- ------------ ------------ -------------
           0 MASTERPRPDG     DISMOUNTED                                 0            0             0
           1 BSCSPRPDG       MOUNTED         EXTERN              19968000      2710125             0
           2 LIKYAPRPDG      MOUNTED         EXTERN               7168000       448741             0
           4 THSPRPDG        MOUNTED         EXTERN               3788800       369063             0
 
   SQL> alter diskgroup  THSPRPDG dismount ;


 renamedg phase=both dgname=THSPRPDG newdgname=THSV2DG asm_diskstring='/dev/ORACLE/ASMDISKS/thsbgfx*'  clean=true verbose=true keep_voting_files=true check=true

 renamedg phase=both dgname=THSPRPDG newdgname=THSV2DG asm_diskstring='/dev/ORACLE/ASMDISKS/thsbgfx*'  clean=true verbose=true keep_voting_files=true check=false


after :

GROUP_NUMBER NAME            STATE           TYPE                TOTAL_MB      FREE_MB OFFLINE_DISKS
------------ --------------- --------------- --------------- ------------ ------------ -------------
           0 THSV2DG         DISMOUNTED                                 0            0             0
           0 MASTERPRPDG     DISMOUNTED                                 0            0             0
           1 BSCSPRPDG       MOUNTED         EXTERN              19968000      2710125             0
           2 LIKYAPRPDG      MOUNTED         EXTERN               7168000       448741             0



REFERENCE
http://docs.oracle.com/cd/E18283_01/server.112/e16102/asmdiskgrps.htm#CHDJAGHE
How To Rename A Diskgroup On ASM 11gR2? (Doc ID 948040.1)
How to rename diskgroup in 10g ASM (Pre-11gR2 release) (Doc ID 948605.1)
http://oracleabout.wordpress.com/category/kfndg-00305-file-not-found/

11gr2 mysteriously changing asm_diskgroup parameter.

A few days ago , I was checking a 11gr2 (11.2.0.1) Rac database , residing on AIX 6.1.

2 disk groups named DATA and FRA were created, and all files were on DATA.

I realized that FRA was never mounted by grid processes after a reboot or crs stop/start.

Here is the ways I tried , and costed me a couple of hours to understand what was happening.

1-ASM_DISKGROUPS parameter changed. RESULT: FAIL

2-creating a pfile , to check if settings are correct.

3-realizing instance scope settings.

4-change instance scope variables. but no help . RESULT: FAIL

5-understanding what was exactly happening, why the "ASM_DISKGROUPS " parameter changed.

6-setting dependency , solving problem.

1-

I just thougt that it was because of asm_diskgroups parameter. And just changed it.

ALTER SYSTEM SET ASM_DISKGROUPS='DATA','FRA' scope=both;

After changing that , I was expecting everything would became normal. But not.

I created a pfile from spfile to double check .

2-

create pfile='/tmp/hede8' from spfile='+DATA/personel/asmparameterfile/registry.253.733514437';

cat /tmp/hede8

+ASM1.asm_diskgroups='FRA'#Manual Mount

*.asm_diskgroups='DATA','FRA'

+ASM1.asm_diskgroups='FRA'#Manual Mount

*.asm_power_limit=1

*.diagnostic_dest='/oracle/app/oracle'

*.instance_type='asm'

*.large_pool_size=12M

*.remote_login_passwordfile='EXCLUSIVE'

3-

and saw the fallowing lines. Then there was a precedence.

+ASM1.asm_diskgroups='FRA'#Manual Mount

4-

again changed paramters as fallows

ALTER SYSTEM SET ASM_DISKGROUPS='DATA','FRA' scope=both sid='+ASM1';

After instance scope variables I was again expecting a good result. But what is that again. The parameter has seen changed again. I created pfile once more and yes the paramters which ı had changed in step 3 were gone .There must be something buggy if there is is not a ghost dba doing nasty things :) In one of the reference notes ; it is said to be resolved 11.2.0.2 Not tested yet.

5-What happens then ?

Some metalink search guided the solution as usual.

11gr2 asm was updating the asm_diskgroup parameter as diskgroups mounted,dismounted.

While stopping adn starting Grid you will see in asm alert.logfile:

Fri Jan 07 09:47:36 2011

SQL> ALTER DISKGROUP DATA DISMOUNT /* asm agent */

Fri Jan 07 09:47:37 2011

SQL> ALTER DISKGROUP FRA DISMOUNT /* asm agent */

Fri Jan 07 09:47:47 2011

Then ofcourse parameter will change.And because of none of the files reside in FRA ; only DATA is mounted while starting rdbms instance, so there is no dependency in rdbms and FRA asm diskgroup.

If I add a dependency then everthing will be as expected.

To add a depedency :

srvctl modify database -d trt4 -a 'DATA,FRA'

To see details :

srvctl config database -d trt4

Here are some reference notes:

1. 1059856.1 ASM Diskgroups are not mounted with spfile on ASM on next startup with crsctl command in 11gR2. [ID 1059856.1]
2. 987904.1 Additional Diskgroups Does Not Get Mounted When SRVCTL Is Used To Start And Shutdown ASM Instance [ID 987904.1]
3. http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams010.htm#I1010247
   

Recover Copy of Database

You need to move a datafile from/to different filesystems or ASM.
and the backup/restore time is so long that you cannot stop production systems so long.

RMAN will have a good feature that is :
1-backup as copy level 0 ; then directly apply created redo,archived logs
2-Sometimes applying all changes redos may take longer then applying level 1 incremental backup. recovery with level 1 incremental backup + apply redo/archived logs.

Here is a just commands , I used in a customer evn which generated 200gb archived logs per day and level 0 backup takes 12 hours.

----- level 0 copy backup
run{
allocate channel dev1 type disk;
allocate channel dev2 type disk;
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '/oradata01/npcdb/npcdata/%U9' TAG 'ORA_ASM_MIGRATION';
}

---- level 1 incremental backup
run {
backup incremental level 1 for recover of copy with tag 'ORA_ASM_MIGRATION' database ;
}

---- recover copy of database
run {
recover copy of database with tag 'ORA_ASM_MIGRATION';
}

Gap is not resolved in dataguard. WHY ?

One of my customers had an environment like that:
2 node rac primary site and one active dataguard in standby site. 11gr1 (11.1.0.7 )version. The problem was gaps in standby site. When everthing is working fine log writer process send change vectors to standby and active dataguard updates tables immediately. But when network failes or manually a gap is created by shutting down standby site gap is never resolved. I looked around and finally realized that the problem was because of insufficient archiver processes.
in that case the value was 2.

Now read this information:
LOG_ARCHIVE_MAX_PROCESSES init parameter has a range 1 to 30.
Gaps are resolved using archiver processes in primary site. When a gap is detected ARCn process sents archived log to standby.But one process is always dedicated to local arhiving and never talks to standby site.

A maximum of thirty ARCn processes can be enabled if there are a large number of archive logs that need to be transferred. Twenty-nine of these ARCn processes may ship to remote locations; one ARCn process is always dedicated to local archival.



As we have 2 threads in rac then 2 archiver processes are dediceated for local archiving. This makes the sense : no archiver process is ready to send standby site.
all are dediceted.
increase the value greater then 2 and problem is solved. I set it to 8 and gap is resolved.

HOW TO MOVE DATAFILES TO ASM

Let's say you have datafiles on filesystem ; created ASM; and want to move these files to ASM storage , or you had a RAC and added datafiles on node1 local disks, but node2,node3..nodeX cannot access these newly created datafiles just like my customer has just did. Here is a solution using RMAN and copy datafile methods.

NOTE : some sqlstatement using double pipe " " are not visible in this blog(step 1, step2 ,step 4 step 6 ) ..!! I dont know why but pay attention ,the sql statement will fail if you run directly without adding double pipes.!!

UPDATE: Please see my other post "Recover Copy of Database" if the downtime or offline period is not in acceptable limits.

PLAN
1-determine the tablespaces which have datafiles on filesystem,take them offline,
2-determine datafiles which will be copied to ASM ; and backup using RMAN
3-Verifiy that all copy datafiles are avaliable.
4-Switch datafiles to copies.
5-Verifiy that switch operation is succesfull,
6- Online tablespace, which were taken offline in step1
7-Check datafiles , open other instances if using RAC and backup database.


1--
determine which tablespaces should be offlined ; containing datafiles from "/oradata" mount point.and offline tablespaces.
select TS# from v$datafile where NAME like '/oradata%' group by ts# order by ts#;
select 'alter tablespace ' name ' offline;' from v$tablespace where ts# in (7,8,9,12,13,14,15,16,17,18,24,25,26,27,28,29);


2--
determine which datafiles will be rman copied to ASM
select 'copy datafile ' file# ' to +DATA;' from v$datafile where NAME like '/oradata%';
EX: RMAN > copy datafile 38 to '+DATA';

3--
see copy is finished succesfully , you can check using rman
rman target /
list copy;

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
1 39 A 20-AUG-09 234419163 20-AUG-09 +DATA/abim/datafile/isveren.339.695416859
2 39 A 20-AUG-09 234419163 20-AUG-09 +DATA/abim/datafile/isveren.340.695417627
3 40 A 20-AUG-09 234419128 20-AUG-09 +DATA/abim/datafile/isci.341.695417761
4 41 A 20-AUG-09 234419286 20-AUG-09 +DATA/abim/datafile/uye.342.695417837
5 42 A 20-AUG-09 234419302 20-AUG-09 +DATA/abim/datafile/uyeana.343.695417913

4--
after rman copy datafile is finished; swith datafiles to copy,
!If multiple copies exist see SWITH reference
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta061.htm
select 'SWITCH datafile ' file# ' to COPY;' from v$datafile where NAME like '/oradata%';
EX: RMAN> SWITCH DATAFILE 38 TO COPY ;

5--
see copy and current datafiles are excahnged. Use rman to verify
RMAN> list copy;
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
10 38 A 20-AUG-09 234419163 20-AUG-09 /oradata/ABIM/ISVEREN7.dbf
58 39 A 20-AUG-09 234419163 20-AUG-09 /oradata/ABIM/ISVEREN8.dbf
31 40 A 20-AUG-09 234419128 20-AUG-09 /oradata/ABIM/ISCIDATA08.dbf
32 41 A 20-AUG-09 234419286 20-AUG-09 /oradata/ABIM/UYE5.dbf
33 42 A 20-AUG-09 234419302 20-AUG-09 /oradata/ABIM/UYEANA6.dbf

6--
online tablespaces which you have offlined in step 1
EX: select 'alter tablespace ' name ' online;' from v$tablespace where ts# in (7,8,9,12,13,14,15,16,17,18,24,25,26,27,28,29);

7--
SQL>select file#,name from v$datafile;
RMAN>backup database;

REFERENCE :
Metalink Doc ID: 390274.1 How to move a datafile from a file system to ASM

Restore - Recover Some Practices ( part 2 )

===========SOLUTION 3
SUTIATION SUMMARY:
old scn controlfiles + different incarnation in controlfile&datafile
SCENARIO CREATION
1-db is open;
2-rman is used for online backup, ## rman backup
3-shutdown database ;
4-after rman hot backup a file system cold backup is done. ## file system backup --> control files are used as catalog. so rman backup will be avaliable in following steps.See registering backups to catalog.!
5-start database ;
6-switch log file 5 times; ## different SCN
7-alter database backup controlfile to trace;
8-shutdown db ;
9-startup nomount;
10-re-create controlfile from trace;
11-alter database open resetlogs; ## different incarnation creation,
12-switch log file 5 times;
13-shutdown db ;
14-copy controlfiles from file system backup; ## control files differ by SCN and incarnation
15-copy some datafiles from cold backup; ## some datafiles differ by SCN and incarnation
SCENARIO READY



status of database after step 7 ;

RMAN backup created in step 2

RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 PARENT 1 19-DEC-06
2 2 SID2 384861248 PARENT 564488 20-MAR-09
3 3 SID2 384861248 CURRENT 1903057 17-APR-09



RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
3 B F A DISK 22-APR-09 1 1 NO TAG20090422T093549 ## hot backup
4 B F A DISK 22-APR-09 1 1 NO TAG20090422T093655 ## controlfile spfile autobackup

RMAN> list backupset 3 ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.80G DISK 00:00:55 22-APR-09
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20090422T093549
Piece Name: /oracle/oradata/FRA/SID2/backupset/2009_04_22/o1_mf_nnndf_TAG20090422T093549_4yxgp6b7_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2041786 22-APR-09 /oracle/oradata/SID2/system01.dbf
2 Full 2041786 22-APR-09 /oracle/oradata/SID2/undotbs01.dbf
3 Full 2041786 22-APR-09 /oracle/oradata/SID2/sysaux01.dbf
4 Full 2041786 22-APR-09 /oracle/oradata/SID2/users01.dbf
5 Full 2041786 22-APR-09 /oracle/oradata/SID2/example01.dbf
6 Full 2041786 22-APR-09 /oracle/oradata/SID2/drop01.dbf
7 Full 2041786 22-APR-09 /oracle/oradata/SID2/drop02.dbf
8 Full 2041786 22-APR-09 /oracle/oradata/SID2/drop03.dbf
9 Full 2041786 22-APR-09 /oracle/oradata/SID2/adtest01.dbf
10 Full 2041786 22-APR-09 /oracle/oradata/SID2/UKS001.dbf

RMAN> list backupset 4 ;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 6.83M DISK 00:00:00 22-APR-09
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20090422T093655
Piece Name: /oracle/oradata/FRA/SID2/autobackup/2009_04_22/o1_mf_s_684841015_4yxgr7mq_.bkp
Control File Included: Ckp SCN: 2041812 Ckp time: 22-APR-09
SPFILE Included: Modification time: 22-APR-09

File system backup created in step 4
drwxr-xr-x 5 oracle dba 4096 Mar 20 13:19 ..
drwxr-x--- 3 oracle dba 4096 Mar 20 13:40 FRA
drwxr-x--- 2 oracle dba 4096 Apr 17 10:24 SID2
drwxr-xr-x 5 oracle dba 4096 Apr 22 09:46 .
drwxr-x--- 2 oracle dba 4096 Apr 22 09:47 SID2_backup

SQL> shutdown immediate;
SQL> startup nomount;
CREATE CONTROLFILE REUSE DATABASE "SID2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/SID2/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/SID2/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/SID2/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/SID2/system01.dbf',
'/oracle/oradata/SID2/undotbs01.dbf',
'/oracle/oradata/SID2/sysaux01.dbf',
'/oracle/oradata/SID2/users01.dbf',
'/oracle/oradata/SID2/example01.dbf',
'/oracle/oradata/SID2/drop01.dbf',
'/oracle/oradata/SID2/drop02.dbf',
'/oracle/oradata/SID2/drop03.dbf',
'/oracle/oradata/SID2/adtest01.dbf',
'/oracle/oradata/SID2/UKS001.dbf'
CHARACTER SET WE8ISO8859P9
;
Control file created.
SQL>

## HINT when create controlfile is issued db is mounted; you do not explicity mount it.

RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 CURRENT 1903057 17-APR-09

SQL> alter database open resetlogs; ## new incarnation created..!!
Database altered.


RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 PARENT 1903057 17-APR-09
2 2 SID2 384861248 CURRENT 2043251 22-APR-09
RMAN>

## Now we have a different incarnation number

SQL> alter system switch logfile; -- execute this a few more times ..
System altered.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.
SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
------------------ ------------- ------------ ----------
22-APR-09 09:57:26 2042410 2042413 8
22-APR-09 09:57:31 2042413 2042415 9
22-APR-09 09:57:32 2042415 2043251 10
22-APR-09 10:27:48 2043251 2043388 1
22-APR-09 10:29:17 2043388 2043390 2
22-APR-09 10:29:18 2043390 2043393 3
22-APR-09 10:29:21 2043393 2043395 4
22-APR-09 10:29:21 2043395 2043397 5

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

[oracle@gulcan oradata]$ cp SID2_backup/control0* SID2/ ## all controlfiles have old snc and incarnation
[oracle@gulcan oradata]$ cp SID2_backup/users01.dbf SID2/ ## users01.dbf datafile has old scn and incarnation.

Scenario now ready .
MAYDAY MAYDAY .. :)



SQL> startup;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2071256 bytes
Variable Size 117441832 bytes
Database Buffers 41943040 bytes
Redo Buffers 6316032 bytes
Database mounted.
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'


RMAN> recover database;
Starting recover at 22-APR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=56 devtype=DISK
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/22/2009 10:53:36
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'
RMAN>

as you see it seems we are stuck
there may be some possible ways; I will try
restore from last rman backup + register archived logs to catalog + apply archived logs

SOLUTION 1
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.
SQL> SELECT FIRST_TIME, FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# FROM V$LOG_HISTORY;
FIRST_TIME FIRST_CHANGE# NEXT_CHANGE# SEQUENCE#
------------------ ------------- ------------ ----------
20-MAR-09 13:40:19 564488 595962 1
.......
14-APR-09 21:02:40 1749055 1791086 59
15-APR-09 20:00:19 1791086 1824816 60
16-APR-09 12:42:26 1824816 1824818 61
16-APR-09 12:42:29 1824818 1824820 62
16-APR-09 12:42:32 1824820 1824822 63
16-APR-09 12:42:34 1824822 1862796 64
17-APR-09 08:48:27 1862796 1862798 65
17-APR-09 08:48:28 1862798 1862801 66
17-APR-09 08:48:30 1862801 1862803 67
17-APR-09 08:48:30 1862803 1882805 68
17-APR-09 08:58:27 1882805 1882948 69
17-APR-09 09:00:10 1882948 1882950 70
17-APR-09 09:00:11 1882950 1882953 71
17-APR-09 09:00:13 1882953 1882955 72
17-APR-09 09:00:13 1882955 1902957 73
17-APR-09 09:03:47 1902957 1903057 74
17-APR-09 10:33:01 1903057 1940815 1
18-APR-09 07:00:09 1940815 1998639 2
19-APR-09 21:00:25 1998639 2041019 3
77 rows selected.

controlfile is lack of archied log information normally. we will try to register newly created archived logs.

alter database register logfile '/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_10_4yxkqnpr_.arc';
....
alter database register logfile '/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_9_4yxkqnl6_.arc';
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level


RMAN> restore database ;
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/oradata/SID2/system01.dbf
restoring datafile 00002 to /oracle/oradata/SID2/undotbs01.dbf
restoring datafile 00003 to /oracle/oradata/SID2/sysaux01.dbf
restoring datafile 00004 to /oracle/oradata/SID2/users01.dbf
restoring datafile 00005 to /oracle/oradata/SID2/example01.dbf
restoring datafile 00006 to /oracle/oradata/SID2/drop01.dbf
restoring datafile 00007 to /oracle/oradata/SID2/drop02.dbf
restoring datafile 00008 to /oracle/oradata/SID2/drop03.dbf
restoring datafile 00009 to /oracle/oradata/SID2/adtest01.dbf
restoring datafile 00010 to /oracle/oradata/SID2/UKS001.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 22-APR-09

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2041786 generated at 04/22/2009 09:35:50 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_4_%u_.arc
ORA-00280: change 2041786 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
RETURN PRESSED

ORA-00283: recovery session canceled due to errors
ORA-19906: recovery target incarnation changed during recovery
ORA-01112: media recovery not started


RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 SID2 384861248 PARENT 1 19-DEC-06
2 2 SID2 384861248 PARENT 564488 20-MAR-09
3 3 SID2 384861248 PARENT 1903057 17-APR-09
4 4 SID2 384861248 CURRENT 2043251 22-APR-09

RMAN> RESET DATABASE TO INCARNATION 4 ;
database reset to incarnation 4

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2041786 generated at 04/22/2009 09:35:50 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_22/o1_mf_1_4_%u_.arc
ORA-00280: change 2041786 for thread 1 is in sequence #4
Specify log: {=suggested | filename | AUTO | CANCEL}
... return pressed
Log applied.
Media recovery complete.

SQL> alter database open RESETLOGS ;
Database altered.

DONE..!!
That was really cool..!

Restore - Recover Some Practices ( part 1 )

Backup is one of the primary tasks of a dba.But how about restore and recover ?I know many sites that take regular backups and never test if they could restore,recover.In the post I created scenario some of which I have encountered.Because of many examples I will have a few posts about this subject.

===========SOLUTION 1
SUTIATION SUMMARY:
rman or sqlplus to recover ; one of controlfiles and some of datafile has old scn
SCENARIO CREATION
SQL> shutdown immediate;
#cp -R /oracle/oradata/SID2 /oracle/oradata/SID2_backup
SQL> startup;
SQL> alter system switch log file ; -- X5 times
SQL> shutdown abort;
#cp /oracle/oradata/SID2_backup/control01.ctl /oracle/oradata/SID2/control01.ctl ## controlfile01 has old scn
#cp /oracle/oradata/SID2_backup/system01.dbf /oracle/oradata/SID2/system01.dbf ## datafile01 has old scn
SQL> shutdown abort;
SCENARIO READY

SQL> startup mount;
ORACLE instance started.
ORA-00214: control file '/oracle/oradata/SID2/control02.ctl' version 1352
inconsistent with file '/oracle/oradata/SID2/control01.ctl' version 1327

cp /oracle/oradata/SID2/control02.ctl /oracle/oradata/SID2/control01.ctl
SQL> alter database mount;
Database altered.

SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/SID2/system01.dbf'

HINT ..!
if you try recovering from sqlplus it does not know about archived logs..!!
Although both candidates can recover database rman is strongly recommended to use here ..!!

### ************************* SQLPLUS DOES NOT KNOW ABOUT ARCHIVED LOGS
SQL> recover datafile 1 ;
ORA-00279: change 1862553 generated at 04/17/2009 08:45:42 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_%u_.arc
ORA-00280: change 1862553 for thread 1 is in sequence #64
Specify log: {=suggested | filename | AUTO | CANCEL}
--RETURN PRESSED TO ACCEPT suggested archived logs..!
Log applied.
Media recovery complete.

SQL> alter database open ;
Database altered.


### ************************* RMAN KNOWS ABOUT ARCHIVED LOGS
RMAN> recover datafile 1 ;
Starting recover at 17-APR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=53 devtype=DISK
starting media recovery
archive log thread 1 sequence 64 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_4yj61c6n_.arc
archive log thread 1 sequence 65 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_65_4yj61d9g_.arc
archive log thread 1 sequence 66 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_66_4yj61g6m_.arc
archive log thread 1 sequence 67 is already on disk as file /oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_67_4yj61gx8_.arc
archive log filename=/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_4yj61c6n_.arc thread=1 sequence=64
archive log filename=/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_65_4yj61d9g_.arc thread=1 sequence=65
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-APR-09


DB IS NOW READY TO OPEN
SQL> alter database open ;
Database altered.


===========SOLUTION 2
SUTIATION SUMMARY:
rman to recover ; old controlfile is used from cold backup..!
SCENARIO CREATION
SQL>shutdown immediate;
# copy all datafiles as filesystem cold backup

[oracle@gulcan oradata]# cp SID2_backup/control0* SID2/
cp: overwrite `SID2/control01.ctl'? y
cp: overwrite `SID2/control02.ctl'? y
cp: overwrite `SID2/control03.ctl'? y
SCENARIO READY

SQL> startup mount ;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2071256 bytes
Variable Size 117441832 bytes
Database Buffers 41943040 bytes
Redo Buffers 6316032 bytes
Database mounted.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/oracle/oradata/SID2/undotbs01.dbf'
ORA-01207: file is more recent than control file - old control file

### ****************************** RMAN is not using backup controlfile statement..
RMAN> recover database using backup controlfile until cancel;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "using": expecting one of: "archivelog, auxiliary, allow, check, delete, from, high, noredo, noparallel, parallel, ;, skip, tablespace, test, until, undo"
RMAN-01007: at line 1 column 18 file: standard input
RMAN>

### ****************************** SQLPLUS is using backupcontrolfile ..
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1862553 generated at 04/17/2009 08:45:42 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata/FRA/SID2/archivelog/2009_04_17/o1_mf_1_64_%u_.arc
ORA-00280: change 1862553 for thread 1 is in sequence #64
Specify log: {=suggested | filename | AUTO | CANCEL}

all suggested recovery was done ..!!

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/oracle/oradata/SID2/undotbs01.dbf'

..... HINT ..!! when warning declared "RECOVER succeeded but OPEN RESETLOGS would get error below.."
it means we need more recovery but we have no archived log remaining unapplied.
so the redo data should be in redo log files which are the source of archived log files .!!
re-apply restore operation using redolog files as input files.
see member,groups..etc for redo log files.
SQL> select * from v$logfile;
/oracle/oradata/SID2/redo01.log
/oracle/oradata/SID2/redo02.log
/oracle/oradata/SID2/redo03.log

SQL> recover database using backup controlfile until cancel;
Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle/oradata/SID2/redo01.log
Log applied.
Media recovery complete.

SQL> alter database open RESETLOGS;
Database altered.
SQL>