Monday, February 10, 2014

Creating an ASM database from NON-ASM database backup in 11g

In this post I'll create an ASM database over a host from the backup of a NON-ASM database located on a different host using RMAN utility.

Here, I'll create new ASM managed database on host 'stby' with the backup of database 'db1' which is OMF.

Database_Name     HOST     File_Management
db1                         prim        OMF
db1(new database)  stby        ASM



Step 1.

Take the Datafile, Archivelog and Controlfile backup of the NON-ASM database in '/u01/bkp/'. 

RMAN> backup database format '/u01/bkp/db_%U.bkp';

Starting backup at 10-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0kp092u5_1_1.bkp tag=TAG20140210T113845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0lp09307_1_1.bkp tag=TAG20140210T113845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 10-FEB-14

RMAN> backup archivelog all format '/u01/bkp/arc_%U.bkp';

Starting backup at 10-FEB-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=7 STAMP=838829575
input archived log thread=1 sequence=10 RECID=8 STAMP=838884652
input archived log thread=1 sequence=11 RECID=9 STAMP=838901252
input archived log thread=1 sequence=12 RECID=10 STAMP=838901331
input archived log thread=1 sequence=13 RECID=11 STAMP=838905416
input archived log thread=1 sequence=14 RECID=12 STAMP=838939737
input archived log thread=1 sequence=15 RECID=13 STAMP=838978232
input archived log thread=1 sequence=16 RECID=14 STAMP=839014248
input archived log thread=1 sequence=17 RECID=15 STAMP=839052021
input archived log thread=1 sequence=18 RECID=16 STAMP=839095566
input archived log thread=1 sequence=19 RECID=17 STAMP=839140243
input archived log thread=1 sequence=20 RECID=18 STAMP=839161908
input archived log thread=1 sequence=21 RECID=19 STAMP=839162150
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/arc_0mp09696_1_1.bkp tag=TAG20140210T123550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-FEB-14

RMAN> backup current controlfile format '/u01/bkp/control_%U.ctl';

Starting backup at 10-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/control_0np096bf_1_1.ctl tag=TAG20140210T123703 comment=NO
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14

Step 2.

Copy the backup to the hosts where needs to create the ASM database.

[oracle@prim bkp]$ scp -r * oracle@stby:/u01/bkp/
oracle@stby's password:
arc_0mp09696_1_1.bkp                                                                                                       100%  263MB  23.9MB/s   00:11
control_0np096bf_1_1.ctl                                                                                                   100% 9568KB   9.3MB/s   00:00
db_0kp092u5_1_1.bkp                                                                                                        100% 1070MB  14.3MB/s   01:15
db_0lp09307_1_1.bkp                                                                                                        100% 9600KB   3.1MB/s   00:03

Step3.

Copy the parameter file of the OMF database to the new database server and modify the parameter for the ASM.

[oracle@stby dbs]$ more initdb1.ora
db1.__db_cache_size=687865856
db1.__java_pool_size=16777216
db1.__large_pool_size=16777216
db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
db1.__pga_aggregate_target=687865856
db1.__sga_target=1006632960
db1.__shared_io_pool_size=0
db1.__shared_pool_size=268435456
db1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/db1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA','+DATA'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='oracle.com'
*.db_name='db1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Step 4.

Start the database in nomount mode using the newly modified parameter.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdb1.ora';
ORACLE instance started.

Total System Global Area  171581440 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                2461696 bytes

Step5. 
Restore the control file from the the controlfile backup.

RMAN> restore controlfile from '/u01/bkp/control_0np096bf_1_1.ctl';

Starting restore at 10-FEB-14
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: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output file name=+DATA/db1/controlfile/current.283.839163177
output file name=+DATA/db1/controlfile/current.281.839163181
Finished restore at 10-FEB-14

Step 5.
Open the database in mount mode.
RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

Step 6. 
Bounce the database and update the pfile for the control file location and then open the database in mount mode. Otherwise in future after bounce the database Oracle will throw the error as below.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


excerpts of the updated pfile with the controlfile location.

*.audit_file_dest='/u01/app/oracle/admin/db1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/DB1/CONTROLFILE/current.281.839163181','+DATA/DB1/CONTROLFILE/current.283.839163177'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='oracle.com'
*.db_name='db1'
*.diagnostic_dest='/u01/app/oracle'

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdb1.ora';
ORACLE instance started.

Total System Global Area  171581440 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                2461696 bytes
Database mounted.

Step7.
Restore the datafile.

Check in the v$datafile and v$tempfile for the file id.

SQL> select FILE#,NAME from v$datafile;
SQL> select FILE#,NAME from v$tempfile;


RMAN> run
2> {
3> set newname for datafile 1 to '+DATA';
4> set newname for datafile 2 to '+DATA';
5>
6> set newname for datafile 3 to '+DATA';
7> set newname for datafile 4 to '+DATA';
8> set newname for datafile 5 to '+DATA';
9> set newname for tempfile 1 to '+DATA';
10> restore database;
11> switch datafile all;
12> switch tempfile all;
13> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 10-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA
channel ORA_DISK_1: restoring datafile 00002 to +DATA
channel ORA_DISK_1: restoring datafile 00003 to +DATA
channel ORA_DISK_1: restoring datafile 00004 to +DATA
channel ORA_DISK_1: restoring datafile 00005 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/bkp/db_0kp092u5_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/bkp/db_0kp092u5_1_1.bkp tag=TAG20140210T113845
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 10-FEB-14

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=839163435 file name=+DATA/db1/datafile/system.279.839163339
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=839163435 file name=+DATA/db1/datafile/sysaux.285.839163339
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=839163435 file name=+DATA/db1/datafile/undotbs1.280.839163343
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=839163436 file name=+DATA/db1/datafile/users.282.839163343
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=839163436 file name=+DATA/db1/datafile/test.284.839163341

renamed tempfile 1 to +DATA in control file

Step8.

Recover the database.


RMAN> list backup of archivelog all;
List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1       137.97M    DISK        00:00:07     06-FEB-14
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20140206T161117
        Piece Name: /u01/bkp/01ovv1d5_1_1.bkp

  List of Archived Logs in backup set 1
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    3       791516     04-FEB-14 816435     05-FEB-14
  1    4       816435     05-FEB-14 858512     05-FEB-14
  1    5       858512     05-FEB-14 859840     05-FEB-14
  1    6       859840     05-FEB-14 859843     05-FEB-14
  1    7       859843     05-FEB-14 869252     05-FEB-14
  1    8       869252     05-FEB-14 898900     06-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4       6.50K      DISK        00:00:00     06-FEB-14
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20140206T161256
        Piece Name: /u01/bkp/04ovv1g8_1_1.bkp

  List of Archived Logs in backup set 4
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    9       898900     06-FEB-14 898952     06-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       177.34M    DISK        00:00:07     07-FEB-14
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20140207T120732
        Piece Name: /u01/bkp/07p017g5_1_1.bkp

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    3       791516     04-FEB-14 816435     05-FEB-14
  1    4       816435     05-FEB-14 858512     05-FEB-14
  1    5       858512     05-FEB-14 859840     05-FEB-14
  1    6       859840     05-FEB-14 859843     05-FEB-14
  1    7       859843     05-FEB-14 869252     05-FEB-14
  1    8       869252     05-FEB-14 898900     06-FEB-14
  1    9       898900     06-FEB-14 898952     06-FEB-14
  1    10      898952     06-FEB-14 926119     07-FEB-14
  1    11      926119     07-FEB-14 933143     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10      3.00K      DISK        00:00:00     07-FEB-14
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20140207T120851
        Piece Name: /u01/bkp/0ap017ij_1_1.bkp

  List of Archived Logs in backup set 10
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    12      933143     07-FEB-14 933178     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
14      178.69M    DISK        00:00:09     07-FEB-14
        BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20140207T131656
        Piece Name: /u01/bkp/archivelog_0fp01bi8_1_1.bkp

  List of Archived Logs in backup set 14
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    3       791516     04-FEB-14 816435     05-FEB-14
  1    4       816435     05-FEB-14 858512     05-FEB-14
  1    5       858512     05-FEB-14 859840     05-FEB-14
  1    6       859840     05-FEB-14 859843     05-FEB-14
  1    7       859843     05-FEB-14 869252     05-FEB-14
  1    8       869252     05-FEB-14 898900     06-FEB-14
  1    9       898900     06-FEB-14 898952     06-FEB-14
  1    10      898952     06-FEB-14 926119     07-FEB-14
  1    11      926119     07-FEB-14 933143     07-FEB-14
  1    12      933143     07-FEB-14 933178     07-FEB-14
  1    13      933178     07-FEB-14 934988     07-FEB-14

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
21      263.13M    DISK        00:00:17     10-FEB-14
        BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20140210T123550
        Piece Name: /u01/bkp/arc_0mp09696_1_1.bkp

  List of Archived Logs in backup set 21
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    9       898900     06-FEB-14 898952     06-FEB-14
  1    10      898952     06-FEB-14 926119     07-FEB-14
  1    11      926119     07-FEB-14 933143     07-FEB-14
  1    12      933143     07-FEB-14 933178     07-FEB-14
  1    13      933178     07-FEB-14 934988     07-FEB-14
  1    14      934988     07-FEB-14 953914     07-FEB-14
  1    15      953914     07-FEB-14 974151     08-FEB-14
  1    16      974151     08-FEB-14 993762     08-FEB-14
  1    17      993762     08-FEB-14 1016061    09-FEB-14
  1    18      1016061    09-FEB-14 1039552    09-FEB-14
  1    19      1039552    09-FEB-14 1062286    10-FEB-14
  1    20      1062286    10-FEB-14 1090918    10-FEB-14
  1    21      1090918    10-FEB-14 1091253    10-FEB-14


RMAN>list backup of database;
RMAN>report schema;

RMAN> run
2> {
3> set until sequence 21 thread 1 ;
4> recover database;
5> }

executing command: SET until clause

Starting recover at 10-FEB-14
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_DISK_1: reading from backup piece /u01/bkp/arc_0mp09696_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/bkp/arc_0mp09696_1_1.bkp tag=TAG20140210T123550
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_20_838668830.dbf thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 10-FEB-14


Step 9.
Open the database in resetlog mode.

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 02/10/2014 13:03:07
RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlogs
ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/db1/redo01.log'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Additional information: 1

SQL> select GROUP#,MEMBERS,STATUS from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 CLEARING_CURRENT
         3          1 CLEARING
         2          1 CLEARING


SQL> alter database add logfile
  2  group 4 '+DATA' size 50M;

Database altered.

SQL> alter database add logfile
  2  group 5 '+DATA' size 50M;

Database altered.

SQL> alter database add logfile
  2  group 6 '+DATA' size 50M;

Database altered.

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


SQL> select GROUP#,STATUS,MEMBER from v$logfile;
    GROUP# STATUS  MEMBER
---------- ------- --------------------------------------------------
         3         /u01/app/oracle/oradata/db1/redo03.log
         2         /u01/app/oracle/oradata/db1/redo02.log
         1         /u01/app/oracle/oradata/db1/redo01.log
         4         +DATA/db1/onlinelog/group_4.286.839164409
         5         +DATA/db1/onlinelog/group_5.287.839164465
         6         +DATA/db1/onlinelog/group_6.288.839164475

6 rows selected.

SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;

    GROUP#    MEMBERS STATUS           ARC
---------- ---------- ---------------- ---
         1          1 CLEARING_CURRENT NO
         2          1 CLEARING         YES
         6          1 UNUSED           YES
         4          1 UNUSED           YES
         5          1 UNUSED           YES
         3          1 CLEARING         YES

6 rows selected.

I tried to recover again the database using the controlfile but it didn't work.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1090918 generated at 02/10/2014 12:31:44 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_21_838668830.dbf
ORA-00280: change 1090918 for thread 1 is in sequence #21


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/db1/redo01.log'



Since the group 1 log file was not able to archived so it prevent database to open.
We need to clear that unarchived log.

SQL> alter database clear unarchived logfile group 1;

Database altered.


SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;

    GROUP#    MEMBERS STATUS           ARC
---------- ---------- ---------------- ---
         1          1 CURRENT          NO
         2          1 UNUSED           YES
         3          1 UNUSED           YES
         4          1 UNUSED           YES
         5          1 UNUSED           YES
         6          1 UNUSED           YES

6 rows selected.

SQL> alter database open resetlogs;

Database altered.

Now the database is OPEN.

SQL> select NAME,STATUS from v$datafile;
NAME                                               STATUS
-------------------------------------------------- -------
+DATA/db1/datafile/system.279.839163339            SYSTEM
+DATA/db1/datafile/sysaux.285.839163339            ONLINE
+DATA/db1/datafile/undotbs1.280.839163343          ONLINE
+DATA/db1/datafile/users.282.839163343             ONLINE
+DATA/db1/datafile/test.284.839163341              ONLINE

SQL> select  NAME,STATUS from v$tempfile;

NAME                                               STATUS
-------------------------------------------------- -------
+DATA/db1/tempfile/temp.289.839165941              ONLINE


SQL> select GROUP#,MEMBERS,STATUS from v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 UNUSED
         3          1 UNUSED
         4          1 UNUSED
         5          1 UNUSED
         6          1 UNUSED

6 rows selected.

No comments:

Post a Comment