Total Pageviews

Tuesday 30 December 2014

Migrating the Database from Filesystem to ASM


Check the existing location of all datafiles , redo and control files:

SQL> select name from v$database;

NAME
---------
DBORA

SQL> Archive log list;
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /home/oracle/arch/
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence        3
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbora/users01.dbf
/u01/app/oracle/oradata/dbora/undotbs01.dbf
/u01/app/oracle/oradata/dbora/sysaux01.dbf
/u01/app/oracle/oradata/dbora/system01.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbora/temp01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbora/redo03.log
/u01/app/oracle/oradata/dbora/redo02.log
/u01/app/oracle/oradata/dbora/redo01.log

SQL>  show parameter spfile ;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
spfile         string  /u01/app/oracle/product/11.2/d
       b_1/dbs/spfiledbora.ora
SQL> show parameter control_file ;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time      integer  7
control_files        string  /u01/app/oracle/oradata/dbora/
       control01.ctl, /u01/app/oracle
       /fast_recovery_area/dbora/cont
       rol02.ctl


 Backup pfile and spfile:

SQL> create pfile='/home/oracle/pfile.ora' from spfile ;

File created.


[oracle@oracle arch]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls

hc_dbora.dat  initdbora.ora  init.ora  lkDBORA  orapwdbora  spfiledbora.ora
[oracle@oracle dbs]$ cp spfiledbora.ora /home/oracle/spfiledbora.ora_bkp

Back up the datafiles to the ASM disk group.

The format clause specifies +DATA2, which is the name of the ASM disk group to be used for storing the database.

[oracle@oracle dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 29 17:38:21 2014

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

connected to target database: DBORA (DBID=3262226103)

RMAN> RUN
{
  ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
 BACKUP AS COPY
 INCREMENTAL LEVEL 0
 DATABASE
 FORMAT '+DATA2'
 TAG 'ORA_ASM_MIGRATION';
 }

using target database control file instead of recovery catalog
allocated channel: dev1
channel dev1: SID=48 device type=DISK

allocated channel: dev2
channel dev2: SID=53 device type=DISK

Starting backup at 29-DEC-14
channel dev1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/dbora/system01.dbf
channel dev2: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/dbora/sysaux01.dbf
output file name=+DATA2/dbora/datafile/sysaux.257.867606039 tag=ORA_ASM_MIGRATION RECID=1 STAMP=867606314
channel dev2: datafile copy complete, elapsed time: 00:05:38
channel dev2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/dbora/undotbs01.dbf
output file name=+DATA2/dbora/datafile/undotbs1.258.867606335 tag=ORA_ASM_MIGRATION RECID=2 STAMP=867606350
channel dev2: datafile copy complete, elapsed time: 00:00:25
channel dev2: starting datafile copy
copying current control file
output file name=+DATA2/dbora/datafile/system.256.867606009 tag=ORA_ASM_MIGRATION RECID=3 STAMP=867606376
channel dev1: datafile copy complete, elapsed time: 00:06:32
channel dev1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/dbora/users01.dbf
output file name=+DATA2/dbora/controlfile/backup.259.867606353 tag=ORA_ASM_MIGRATION RECID=4 STAMP=867606379
channel dev2: datafile copy complete, elapsed time: 00:00:29
channel dev2: starting incremental level 0 datafile backup set
channel dev2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel dev2: starting piece 1 at 29-DEC-14
output file name=+DATA2/dbora/datafile/users.260.867606381 tag=ORA_ASM_MIGRATION RECID=5 STAMP=867606382
channel dev1: datafile copy complete, elapsed time: 00:00:05
channel dev2: finished piece 1 at 29-DEC-14
piece handle=+DATA2/dbora/backupset/2014_12_29/nnsnn0_ora_asm_migration_0.261.867606385 tag=ORA_ASM_MIGRATION comment=NONE
channel dev2: backup set complete, elapsed time: 00:00:07
Finished backup at 29-DEC-14
released channel: dev1
released channel: dev2

If block change tracking is enabled for the database, then optionally make a level 1 incremental backup that you can use later to recover the database copy.

SQL> select  status from   v$block_change_tracking ;
STATUS  
----------
DISABLED
In our case it was disabled . If enable take level1 DB inc backup as below:

RUN
{
  ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
  BACKUP INCREMENTAL LEVEL 1 
    FOR RECOVER OF COPY WITH TAG 'ORA_ASM_MIGRATION' 
    DATABASE;
}

If the database is in ARCHIVELOG mode, and if the database is open, then archive the online logs.

The following example uses the SQL command to archive the current redo logs:

RMAN> SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";

SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";

If the database instance is currently using a server parameter file, then back it up.

The following example backs up the server parameter file:

RMAN> BACKUP AS BACKUPSET SPFILE;

Starting backup at 29-DEC-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
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 29-DEC-14
channel ORA_DISK_1: finished piece 1 at 29-DEC-14
piece handle=/u01/app/oracle/fast_recovery_area/DBORA/backupset/2014_12_29/o1_mf_nnsnf_TAG20141229T175459_bb2l0x3h_.bkp tag=TAG20141229T175459 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 29-DEC-14


If block change tracking is enabled, then disable it.

The following command disables block change tracking:

RMAN> SQL "ALTER DATABASE DISABLE BLOCK CHANGE TRACKING";

RMAN> SQL "ALTER DATABASE FLASHBACK OFF";

sql statement: ALTER DATABASE FLASHBACK OFF

RMAN> SHUTDOWN IMMEDIATE;

database closed
database dismounted
Oracle instance shut down

RMAN> STARTUP MOUNT;

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

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                536874328 bytes
Database Buffers             297795584 bytes
Redo Buffers                   2379776 bytes

RMAN> RESTORE SPFILE TO '+DATA2/spfiledbora.ora';

Starting restore at 29-DEC-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA2/spfiledbora.ora
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/DBORA/backupset/2014_12_29/o1_mf_nnsnf_TAG20141229T175459_bb2l0x3h_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/DBORA/backupset/2014_12_29/o1_mf_nnsnf_TAG20141229T175459_bb2l0x3h_.bkp tag=TAG20141229T175459
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 29-DEC-14

If the database is not using a server parameter file, then create one in ASM. Execute the CREATE SPFILE command in SQL*Plus as follows, where sid is the SID of the database (the command spans two lines):

SQL> CREATE SPFILE='+DATA/spfiledbora.ora' FROM PFILE='?/dbs/initsid.ora';

Afterward, delete spfiledbora.ora and initdbora.ora from the ?/dbs directory and 

Create a new initdbora.ora with the following line of content:

SPFILE='+DATA2/spfiledbora.ora'


RMAN> STARTUP MOUNT;

RMAN> SHUTDOWN IMMEDIATE;

database dismounted
Oracle instance shut down

 SQL> STARTUP FORCE NOMOUNT;

Oracle instance started

Total System Global Area     839282688 bytes

Fixed Size                     2233000 bytes
Variable Size                536874328 bytes
Database Buffers             297795584 bytes
Redo Buffers                   2379776 bytes


SQL> alter system set control_files='+DATA','+DATA2' scope=spfile;

System altered.

SQL>  alter system set db_create_file_dest='+DATA2' scope=spfile;

System altered.


SQL> startup nomount ;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size      2233000 bytes
Variable Size    536874328 bytes
Database Buffers   297795584 bytes
Redo Buffers      2379776 bytes
SQL> show parameter control_file;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time      integer  7
control_files        string  +DATA, +DATA2
SQL> 
RMAN> restore controlfile from '/u01/app/oracle/oradata/dbora/control01.ctl' ;

Starting restore at 29-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/dbora/controlfile/current.256.867608667
output file name=+DATA2/dbora/controlfile/current.263.867608679
Finished restore at 29-DEC-14

RMAN> ALTER DATABASE MOUNT;

database mounted
released channel: ORA_DISK_1

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA2/dbora/datafile/system.256.867606009"
datafile 2 switched to datafile copy "+DATA2/dbora/datafile/sysaux.257.867606039"
datafile 3 switched to datafile copy "+DATA2/dbora/datafile/undotbs1.258.867606335"
datafile 4 switched to datafile copy "+DATA2/dbora/datafile/users.260.867606381"

RMAN> RUN
{
  ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
  RECOVER DATABASE;
}

allocated channel: dev1
channel dev1: SID=24 device type=DISK

allocated channel: dev2
channel dev2: SID=26 device type=DISK

allocated channel: dev3
channel dev3: SID=27 device type=DISK

allocated channel: dev4
channel dev4: SID=28 device type=DISK

Starting recover at 29-DEC-14
starting media recovery
media recovery complete, elapsed time: 00:01:05

Finished recover at 29-DEC-14
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4

RMAN> ALTER DATABASE OPEN;

database opened

QL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA2/dbora/datafile/users.260.867606381
+DATA2/dbora/datafile/undotbs1.258.867606335
+DATA2/dbora/datafile/sysaux.257.867606039
+DATA2/dbora/datafile/system.256.867606009

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbora/temp01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbora/redo03.log
/u01/app/oracle/oradata/dbora/redo02.log
/u01/app/oracle/oradata/dbora/redo01.log

SQL> show parameter spfile ;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
spfile         string  /u01/app/oracle/product/11.2/d
       b_1/dbs/spfiledbora.ora
SQL> show parameter control_file ;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time      integer  7
control_files        string  +DATA/dbora/controlfile/curren
       t.256.867608667, +DATA2/dbora/
       controlfile/current.263.867608
       679

 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA2/dbora/tempfile/temp.264.867609077


SQL> SET SERVEROUTPUT ON;
DECLARE
   CURSOR rlc IS
      SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL
      FROM   V$LOG
      UNION
      SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL
      FROM   V$STANDBY_LOG
      ORDER BY 1;
   stmt     VARCHAR2(2048);
BEGIN
   FOR rlcRec IN rlc LOOP
      IF (rlcRec.srl = 'YES') THEN
         stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||
                 rlcRec.thr || ' SIZE ' || rlcRec.bytes;
         EXECUTE IMMEDIATE stmt;
         stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;
         EXECUTE IMMEDIATE stmt;
      ELSE
         stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||
                 rlcRec.thr || ' SIZE ' ||  rlcRec.bytes;
         EXECUTE IMMEDIATE stmt;
         BEGIN
            stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;
            DBMS_OUTPUT.PUT_LINE(stmt);
            EXECUTE IMMEDIATE stmt;
         EXCEPTION
            WHEN OTHERS THEN
               EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
               EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
               EXECUTE IMMEDIATE stmt;
         END;
      END IF;
   END LOOP;
END;
/SQL>     

ALTER DATABASE DROP LOGFILE GROUP 1
ALTER DATABASE DROP LOGFILE GROUP 2
ALTER DATABASE DROP LOGFILE GROUP 3

PL/SQL procedure successfully completed.

SQL> SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA2/dbora/onlinelog/group_2.269.867609239
+DATA2/dbora/onlinelog/group_1.267.867609167
+DATA2/dbora/onlinelog/group_4.265.867609139
+DATA2/dbora/onlinelog/group_4.266.867609151
+DATA2/dbora/onlinelog/group_1.268.867609179
+DATA2/dbora/onlinelog/group_2.270.867609251

Back up archived redo log files, backup sets, and data file copies to Oracle ASM. For example, run the following command at the RMAN prompt:

RMAN> RUN
{
  ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
  ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;

  BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;
  BACKUP BACKUPSET ALL DELETE INPUT;
  BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;
}

using target database control file instead of recovery catalog
allocated channel: dev1
channel dev1: SID=58 device type=DISK

allocated channel: dev2
channel dev2: SID=59 device type=DISK

allocated channel: dev3
channel dev3: SID=60 device type=DISK

allocated channel: dev4
channel dev4: SID=61 device type=DISK

Starting backup at 29-DEC-14
current log archived
channel dev1: starting archived log copy
input archived log thread=1 sequence=3 RECID=1 STAMP=867606655
channel dev2: starting archived log copy
input archived log thread=1 sequence=4 RECID=2 STAMP=867606867
channel dev3: starting archived log copy
input archived log thread=1 sequence=5 RECID=3 STAMP=867609204
channel dev4: starting archived log copy
input archived log thread=1 sequence=6 RECID=4 STAMP=867609456
output file name=+DATA2/dbora/archivelog/2014_12_29/thread_1_seq_4.272.867609463 RECID=5 STAMP=867609472
channel dev2: archived log copy complete, elapsed time: 00:00:21
channel dev2: deleting archived log(s)
archived log file name=/home/oracle/arch/arch_1_4_867594243.arc RECID=2 STAMP=867606867
output file name=+DATA2/dbora/archivelog/2014_12_29/thread_1_seq_5.273.867609467 RECID=6 STAMP=867609476
channel dev3: archived log copy complete, elapsed time: 00:00:20
channel dev3: deleting archived log(s)
archived log file name=/home/oracle/arch/arch_1_5_867594243.arc RECID=3 STAMP=867609204
output file name=+DATA2/dbora/archivelog/2014_12_29/thread_1_seq_6.274.867609471 RECID=7 STAMP=867609479
channel dev4: archived log copy complete, elapsed time: 00:00:18
channel dev4: deleting archived log(s)
archived log file name=/home/oracle/arch/arch_1_6_867594243.arc RECID=4 STAMP=867609456
output file name=+DATA2/dbora/archivelog/2014_12_29/thread_1_seq_3.271.867609459 RECID=8 STAMP=867609488
channel dev1: archived log copy complete, elapsed time: 00:00:37
channel dev1: deleting archived log(s)
archived log file name=/home/oracle/arch/arch_1_3_867594243.arc RECID=1 STAMP=867606655
Finished backup at 29-DEC-14

Starting backup at 29-DEC-14
channel dev1: input backup set: count=6, stamp=867606384, piece=1
channel dev1: starting piece 1 at 29-DEC-14
channel dev1: backup piece +DATA2/dbora/backupset/2014_12_29/nnsnn0_ora_asm_migration_0.261.867606385
channel dev2: input backup set: count=7, stamp=867606900, piece=1
channel dev2: starting piece 1 at 29-DEC-14
channel dev2: backup piece /u01/app/oracle/fast_recovery_area/DBORA/backupset/2014_12_29/o1_mf_nnsnf_TAG20141229T175459_bb2l0x3h_.bkp
piece handle=+DATA2/dbora/backupset/2014_12_29/nnnnf0_0.276.867609507 comment=NONE
channel dev1: finished piece 1 at 29-DEC-14
channel dev1: backup piece complete, elapsed time: 00:00:07
deleted backup piece
backup piece handle=+DATA2/dbora/backupset/2014_12_29/nnsnn0_ora_asm_migration_0.261.867606385 RECID=1 STAMP=867606384
piece handle=+DATA2/dbora/backupset/2014_12_29/nnnnf0_0.275.867609507 comment=NONE
channel dev2: finished piece 1 at 29-DEC-14
channel dev2: backup piece complete, elapsed time: 00:00:09
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/DBORA/backupset/2014_12_29/o1_mf_nnsnf_TAG20141229T175459_bb2l0x3h_.bkp RECID=2 STAMP=867606900
Finished backup at 29-DEC-14

Starting backup at 29-DEC-14
channel dev1: starting datafile copy
input is copy of datafile 00001: /u01/app/oracle/oradata/dbora/system01.dbf
channel dev2: starting datafile copy
input is copy of datafile 00002: /u01/app/oracle/oradata/dbora/sysaux01.dbf
channel dev3: starting datafile copy
input is copy of datafile 00003: /u01/app/oracle/oradata/dbora/undotbs01.dbf
channel dev4: starting datafile copy
input is copy of datafile 00004: /u01/app/oracle/oradata/dbora/users01.dbf
output file name=+DATA2/dbora/datafile/users.279.867609541 RECID=10 STAMP=867609562
channel dev4: datafile copy complete, elapsed time: 00:00:40
deleted datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbora/users01.dbf RECID=9 STAMP=867608773
output file name=+DATA2/dbora/datafile/undotbs1.278.867609533 RECID=11 STAMP=867609567
channel dev3: datafile copy complete, elapsed time: 00:00:54
deleted datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbora/undotbs01.dbf RECID=8 STAMP=867608771
output file name=+DATA2/dbora/datafile/sysaux.277.867609525 RECID=12 STAMP=867609831
channel dev2: datafile copy complete, elapsed time: 00:05:13
deleted datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbora/sysaux01.dbf RECID=7 STAMP=867608769
output file name=+DATA2/dbora/datafile/system.261.867609525 RECID=13 STAMP=867609859
channel dev1: datafile copy complete, elapsed time: 00:05:37
deleted datafile copy
datafile copy file name=/u01/app/oracle/oradata/dbora/system01.dbf RECID=6 STAMP=867608768
Finished backup at 29-DEC-14
released channel: dev1
released channel: dev2
released channel: dev3
released channel: dev4


RMAN> report schema ;


using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DBORA

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     +DATA2/dbora/datafile/system.256.867606009
2    550      SYSAUX               ***     +DATA2/dbora/datafile/sysaux.257.867606039
3    35       UNDOTBS1             ***     +DATA2/dbora/datafile/undotbs1.258.867606335
4    5        USERS                ***     +DATA2/dbora/datafile/users.260.867606381

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    100      TEMP                 32767       +DATA2/dbora/tempfile/temp.264.867609077

1 comment:

  1. Jadwal Pertandingan Ayam SV388 7 Maret 2019 - Minggu, Ujung Pandang 10 Maret 2019 – Pada Hari Tersebut Akan Di Laksanakan Berbagai Pertandingan Sabung Ayam Secara Live di Arena Sabung Ayam Thailand.

    Situs Judi Sabung Ayam Online SV388 Merupakan Situs Judi Asal Thailand Yang Sangat Terkenal Dengan Permainan Sabung Ayam Yang Fair dan Menghibur Para Penonton Judi Sabung Ayam.

    Untuk Info Lebih Lanjut Bisa Hub kami Di :
    wechat : bolavita
    line : cs_bolavita
    whatsapp : +628122222995
    BBM: BOLAVITA

    ReplyDelete