Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for the ‘Database11g’ Category

CREATING AN ORACLE11GR2 DATABASE WITH FEW COMMANDS IN WINDOWS

Posted by Hendry chinnapparaj on February 14, 2012

G:\Hendry Scripts>oradim -new -sid HENDRY
Instance created.

G:\Hendry Scripts>edit initHENDRY.ora

db_name=HENDRY

G:\HENDRY~1>set ORACLE_SID=HENDRY

G:\HENDRY~1>sqlplus “/ as sysdba”

SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 14 14:45:52 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=initHENDRY.ora
ORACLE instance started.

Total System Global Area 146472960 bytes
Fixed Size 1373152 bytes
Variable Size 92277792 bytes
Database Buffers 50331648 bytes
Redo Buffers 2490368 bytes
SQL> create database;

Database created.

SQL> select ts#, name from v$tablespace;

TS# NAME
———- ——————————
0 SYSTEM
1 SYSAUX
2 SYS_UNDOTS

SQL> column name format a60
SQL> select ts#, name from v$datafile;

TS# NAME
———- ————————————————————
0 G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\DBS1HENDRY.ORA

1 G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SYX1HENDRY.ORA

2 G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\UND1HENDRY.ORA
SQL> column member format a60
SQL> select group#, member from v$logfile;

GROUP# MEMBER
———- ————————————————————
1 G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG1HENDRY.ORA

2 G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\LOG2HENDRY.ORA
SQL> column name format a60
SQL> select name from v$controlfile;

NAME
————————————————————
G:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\CTL1HENDRY.ORA
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive restrict pfile=initHENDRY.ora
ORACLE instance started.

Total System Global Area 146472960 bytes
Fixed Size 1373152 bytes
Variable Size 92277792 bytes
Database Buffers 50331648 bytes
Redo Buffers 2490368 bytes
Database mounted.
SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Release 11.2.0.1.0 – Production
SQL> exit

G:\HENDRY~1>oradim -delete -sid HENDRY
Instance deleted.

Shalom

H

Posted in Database11g | Leave a Comment »

Migrate 11.2.0.2 Non-ASM Database files to 11.2.0.2 ASM Storage

Posted by Hendry chinnapparaj on September 1, 2011

Thursday, 1 September 2011

Hendry Dasan

Purpose of Oracle ASM Data Migration

Alternatives to Oracle ASM storage include file systems, raw disks, and SAN configurations. Oracle ASM includes numerous benefits over these storage alternatives, including performance optimization, redundancy protection, and load balancing. You do not need a third-party Logical Volume Manager because Oracle ASM manages disks for you. Oracle Real Application Clusters (Oracle RAC) databases benefit from Oracle ASM because it provides ready-made shared storage.

If a database currently uses a storage system other than Oracle ASM, then you can migrate all or part of the database into Oracle ASM, thereby simplifying database administration. You can also migrate a fast recovery area to Oracle ASM.

Native operating system commands such as Linux cp or Windows COPY cannot write or read files in Oracle ASM storage. Because RMAN can read and write Oracle ASM files, you can use RMAN to copy data files into and out of Oracle ASM storage or between Oracle ASM disk groups. This technique is useful if you must store backups on user-managed disks.

 

Basic Concepts of Oracle ASM Data Migration

You can migrate data to Oracle ASM with RMAN even if you are not using RMAN as your primary backup tool. The migration requires one RMAN database backup.

If you have sufficient disk space to hold the entire database both in Oracle ASM and alternative storage systems, then you can move a database directly into Oracle ASM. If you do not have sufficient storage, then you can back the database up to tape, create an Oracle ASM disk group that uses old disk space, and restore the database from tape to Oracle ASM.

After you set the location of the new recovery area, existing backups remain in the old recovery area and count against the total disk quota of the recovery area. The backups are deleted from the old recovery area when space is needed. These backups are usable by RMAN. It is not necessary to move legacy backups to the new Oracle ASM recovery area unless you need disk space. To free space consumed by files in the old recovery area, you can back them up to tape or migrate them to the Oracle ASM recovery area.

 

Preparing to Migrate the Database to Oracle ASM Using RMAN

This section explains how to prepare the database for migration. This section makes the following assumptions:

You want to migrate the database to two Oracle ASM disk groups: +DATA for the database. The database to be migrated to Oracle ASM storage is named asmtest

 

 

Determine how much disk space will be required for your database Migration

Connect to the oracle11g database

 

select DF.TOTAL/1048576 “DataFile Size Mb”, LOG.TOTAL/1048576 “Redo Log Size Mb”, CONTROL.TOTAL/1048576 “Control File Size Mb”, (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 “Total Size Mb” from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

 

 

DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb

—————- —————- ——————– ————-

38695.9688             1500           104.015625    40299.9844

 

 

Ensuring you have enough space within your ASM instance.

Connect to ASM Instance

+ASM:/u01/ofsad2> sqlplus / as sysasm

 

SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 31 10:33:40 2011

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Automatic Storage Management option

 

SQL> show user

USER is “SYS”

 

SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

 

NAME                           STATE         TOTAL_MB    FREE_MB

—————————— ———– ———- ———-

DATA                           MOUNTED         514687     465497

 

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 “FREE %” FROM v$asm_diskgroup;

 

NAME                              FREE_MB   TOTAL_MB     FREE %

—————————— ———- ———- ———-

DATA                               465497     514687 90.4427351

 

 

ENSURE COMPATIBEL IS 11.2.0.2.0 FOR THE ASM MIGRATION

If the COMPATIBLE initialization parameter setting for the database is less than 11.0.0, then make any read-only transportable tablespaces read/write. (JUST FOR TRANSPORTABLE TBLS ONLY). Read-only transportable tablespaces cannot be migrated because RMAN cannot back them up.

 

 

 

SQL> show parameter compatible

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

compatible                           string      11.2.0.2.0

SQL>

 

Database RMAN Configuration

 

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

 

old RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters are successfully stored

 

just do a backup test

 

RMAN> backup current controlfile;

 

Starting backup at 31-08-2011 14:37:03

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=203 device type=DISK

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 31-08-2011 14:37:04

channel ORA_DISK_1: finished piece 1 at 31-08-2011 14:37:11

piece handle=+DATA/asmtest/backupset/2011_08_31/ncnnf0_tag20110831t143703_0.833.760631829 tag=TAG20110831T143703 comment=NONE

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

Finished backup at 31-08-2011 14:37:11

 

RMAN> show all;

 

RMAN configuration parameters for database with db_unique_name ASMTEST are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE ‘SBT_TAPE’ TO ‘%F’;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’;

CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE ‘SBT_TAPE’ TO 1;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE ‘SBT_TAPE’ TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE CHANNEL 1 DEVICE TYPE ‘SBT_TAPE’ PARMS  “ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo_asmtest.opt)”;

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/oracle/product/11.2.0.2/dbs/snapcf_asmtest.f’; # default

 

RMAN>

 

Do a Backup of the Spfile

 

asmtest:/u01/oracle/product/11.2.0.2/dbs> cp -p spfileasmtest.ora spfileasmtest.ora.ori

 

Back up the data files to the Oracle ASM disk group.

The following example uses a RUN command to make a level 0 incremental backup and allocates a  single channel (allocate more channels to increase the backup speed). A level 0 incremental backup is an RMAN incremental backup that backs up all data blocks in the data files being backed up.

 

asmtest:/u01/oracle/product/11.2.0.2/dbs> rman target /

 

Recovery Manager: Release 11.2.0.2.0 – Production on Wed Aug 31 14:45:49 2011

 

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

 

connected to target database: ASMTEST (DBID=258344671)

 

RMAN> run

2> {

3> allocate channel dev1 device type disk;

4> backup as copy

5> incremental level 0

6> database

7> format ‘+DATA’

8> tag ‘ORA_ASM_MIGRATION’;

9> }

 

channel dev1: datafile copy complete, elapsed time: 00:00:01

channel dev1: starting datafile copy

input datafile file number=00216 name=/u02/oradata/asmtest/CSHIDX015_01.DBF

output file name=+DATA/asmtest/datafile/cshidx015.845.760633307 tag=ORA_ASM_MIGRATION RECID=265 STAMP=760633306

channel dev1: datafile copy complete, elapsed time: 00:00:01

channel dev1: starting datafile copy

input datafile file number=00259 name=/u02/oradata/asmtest/XMLDATA001.DBF

output file name=+DATA/asmtest/datafile/xml_data.846.760633307 tag=ORA_ASM_MIGRATION RECID=266 STAMP=760633307

channel dev1: datafile copy complete, elapsed time: 00:00:01

channel dev1: starting incremental level 0 datafile backup set

channel dev1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel dev1: starting piece 1 at 31-08-2011 15:01:48

channel dev1: finished piece 1 at 31-08-2011 15:01:49

piece handle=+DATA/asmtest/backupset/2011_08_31/nnsnn0_ora_asm_migration_0.847.760633309 tag=ORA_ASM_MIGRATION comment=NONE

channel dev1: backup set complete, elapsed time: 00:00:01

Finished backup at 31-08-2011 15:01:49

released channel: dev1

 

 

 

can do an incremental backup just before the final migration if data is modified after full backup

 

The following example makes an incremental level 1 copy of the level 0 backup created in the previous step:

RUN

{

ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 1

FOR RECOVER OF COPY WITH TAG ‘ORA_ASM_MIGRATION’

DATABASE;

}

 

 

 

 

 

asmtest:/u01/oracle/product/11.2.0.2/dbs> rman target /

 

Recovery Manager: Release 11.2.0.2.0 – Production on Wed Aug 31 15:05:00 2011

 

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

 

connected to target database: ASMTEST (DBID=258344671)

 

RMAN> run

2> {

3> allocate channel dev1 device type disk;

4> backup incremental level 1

5> for recover of copy with tag ‘ORA_ASM_MIGRATION’

6> database;

7> }

 

using target database control file instead of recovery catalog

allocated channel: dev1

channel dev1: SID=204 device type=DISK

 

Starting backup at 31-08-2011 15:05:55

channel dev1: starting incremental level 1 datafile backup set

channel dev1: specifying datafile(s) in backup set

input datafile file number=00025 name=/u02/oradata/asmtest/CB002_02.DBF

skipping datafile 00025 because it has not changed

input datafile file number=00003 name=/u02/oradata/asmtest/sysaux01.dbf

input datafile file number=00251 name=/u02/oradata/asmtest/MIS_DATA_64M_01.DBF

input datafile file number=00148 name=/u02/oradata/asmtest/CPS_LM_DATA_1M_01.DBF

input datafile file number=00253 name=/u02/oradata/asmtest/MIS_INDEX_1M_01.DBF

input datafile file number=00021 name=/u02/oradata/asmtest/BWA_LM_INDEX_64M_01.DBF

input datafile file number=00236 name=/u02/oradata/asmtest/DOCIX001_01.DBF

input datafile file number=00013 name=/u02/oradata/asmtest/AUT_LM_INDEX_8M_01.DBF

input datafile file number=00030 name=/u02/oradata/asmtest/CB004_01.DBF

input datafile file number=00035 name=/u02/oradata/asmtest/CB007_02.DBF

input datafile file number=00040 name=/u02/oradata/asmtest/CB009_02.DBF

input datafile file number=00045 name=/u02/oradata/asmtest/CB012_01.DBF

input datafile file number=00050 name=/u02/oradata/asmtest/CB014_02.DBF

input datafile file number=00055 name=/u02/oradata/asmtest/CB017_01.DBF

input datafile file number=00060 name=/u02/oradata/asmtest/CB019_02.DBF

input datafile file number=00107 name=/u02/oradata/asmtest/CBIDX007_01.DBF

input datafile file number=00112 name=/u02/oradata/asmtest/CBIDX012_01.DBF

input datafile file number=00117 name=/u02/oradata/asmtest/CBIDX017_01.DBF

input datafile file number=00157 name=/u02/oradata/asmtest/CPS_LM_INDEX_8M_01.DBF

input datafile file number=00165 name=/u02/oradata/asmtest/CSE005_01.DBF

input datafile file number=00170 name=/u02/oradata/asmtest/CSE010_01.DBF

input datafile file number=00175 name=/u02/oradata/asmtest/CSEIDX005_01.DBF

input datafile file number=00180 name=/u02/oradata/asmtest/CSEIDX010_01.DBF

input datafile file number=00186 name=/u02/oradata/asmtest/CSH005_01.DBF

input datafile file number=00191 name=/u02/oradata/asmtest/CSH010_01.DBF

input datafile file number=00204 name=/u02/oradata/asmtest/CSHIX003_01.DBF

input datafile file number=00209 name=/u02/oradata/asmtest/CSHIDX008_01.DBF

input datafile file number=00214 name=/u02/oradata/asmtest/CSHIDX013_01.DBF

input datafile file number=00227 name=/u02/oradata/asmtest/DOC005_01.DBF

input datafile file number=00232 name=/u02/oradata/asmtest/DOC010_01.DBF

input datafile file number=00238 name=/u02/oradata/asmtest/DOCIX003_01.DBF

input datafile file number=00243 name=/u02/oradata/asmtest/DOCIDX008_01.DBF

input datafile file number=00248 name=/u02/oradata/asmtest/DOCIDX013_01.DBF

input datafile file number=00118 name=/u02/oradata/asmtest/CBIDX018_01.DBF

input datafile file number=00011 name=/u02/oradata/asmtest/AUT_LM_INDEX_512K_01.DBF

input datafile file number=00220 name=/u02/oradata/asmtest/CSHIDX019_01.DBF

input datafile file number=00064 name=/u02/oradata/asmtest/CB021_02.DBF

input datafile file number=00069 name=/u02/oradata/asmtest/CB024_01.DBF

input datafile file number=00074 name=/u02/oradata/asmtest/CB026_02.DBF

input datafile file number=00079 name=/u02/oradata/asmtest/CB029_01.DBF

input datafile file number=00084 name=/u02/oradata/asmtest/CB031_02.DBF

input datafile file number=00089 name=/u02/oradata/asmtest/CB034_01.DBF

input datafile file number=00094 name=/u02/oradata/asmtest/CB036_02.DBF

input datafile file number=00099 name=/u02/oradata/asmtest/CB039_01.DBF

input datafile file number=00123 name=/u02/oradata/asmtest/CBIDX023_01.DBF

input datafile file number=00128 name=/u02/oradata/asmtest/CBIDX028_01.DBF

input datafile file number=00133 name=/u02/oradata/asmtest/CBIDX033_01.DBF

input datafile file number=00138 name=/u02/oradata/asmtest/CBIDX038_01.DBF

input datafile file number=00147 name=/u02/oradata/asmtest/CPSDATA_01.DBF

input datafile file number=00198 name=/u02/oradata/asmtest/CSH017_01.DBF

input datafile file number=00216 name=/u02/oradata/asmtest/CSHIDX015_01.DBF

channel dev1: starting piece 1 at 31-08-2011 15:05:57

channel dev1: finished piece 1 at 31-08-2011 15:06:12

piece handle=+DATA/asmtest/backupset/2011_08_31/nnndn1_ora_asm_migration_0.848.760633557 tag=ORA_ASM_MIGRATION comment=NONE

channel dev1: backup set complete, elapsed time: 00:00:15

channel dev1: starting incremental level 1 datafile backup set

channel dev1: specifying datafile(s) in backup set

input datafile file number=00026 name=/u02/oradata/asmtest/CB002_01.DBF

skipping datafile 00026 because it has not changed

input datafile file number=00002 name=/u02/oradata/asmtest/undotbs01.dbf

input datafile file number=00159 name=/u02/oradata/asmtest/CPS_XML_DOC_01.DBF

input datafile file number=00250 name=/u02/oradata/asmtest/MIS_DATA_1M_01.DBF

input datafile file number=00008 name=/u02/oradata/asmtest/AUT_LM_DATA_64M_01.DBF

input datafile file number=00182 name=/u02/oradata/asmtest/CSH001_01.DBF

input datafile file number=00252 name=/u02/oradata/asmtest/MIS_DATA_8M_01.DBF

input datafile file number=00033 name=/u02/oradata/asmtest/CB006_02.DBF

input datafile file number=00038 name=/u02/oradata/asmtest/CB008_01.DBF

input datafile file number=00043 name=/u02/oradata/asmtest/CB011_01.DBF

input datafile file number=00048 name=/u02/oradata/asmtest/CB013_02.DBF

input datafile file number=00053 name=/u02/oradata/asmtest/CB016_01.DBF

input datafile file number=00058 name=/u02/oradata/asmtest/CB018_02.DBF

input datafile file number=00105 name=/u02/oradata/asmtest/CBIX005_01.DBF

input datafile file number=00110 name=/u02/oradata/asmtest/CBIDX010_01.DBF

input datafile file number=00115 name=/u02/oradata/asmtest/CBIDX015_01.DBF

input datafile file number=00142 name=/u02/oradata/asmtest/CBTRAP_02.DBF

input datafile file number=00163 name=/u02/oradata/asmtest/CSE003_01.DBF

input datafile file number=00168 name=/u02/oradata/asmtest/CSE008_01.DBF

input datafile file number=00173 name=/u02/oradata/asmtest/CSEIX003_01.DBF

input datafile file number=00178 name=/u02/oradata/asmtest/CSEIDX008_01.DBF

input datafile file number=00184 name=/u02/oradata/asmtest/CSH003_01.DBF

input datafile file number=00189 name=/u02/oradata/asmtest/CSH008_01.DBF

input datafile file number=00194 name=/u02/oradata/asmtest/CSH013_01.DBF

input datafile file number=00207 name=/u02/oradata/asmtest/CSHIDX006_01.DBF

input datafile file number=00212 name=/u02/oradata/asmtest/CSHIDX011_01.DBF

input datafile file number=00225 name=/u02/oradata/asmtest/DOC003_01.DBF

input datafile file number=00230 name=/u02/oradata/asmtest/DOC008_01.DBF

input datafile file number=00235 name=/u02/oradata/asmtest/DOC013_01.DBF

input datafile file number=00241 name=/u02/oradata/asmtest/DOCIDX006_01.DBF

input datafile file number=00246 name=/u02/oradata/asmtest/DOCIDX011_01.DBF

input datafile file number=00258 name=/u02/oradata/asmtest/UINDEX01.DBF

input datafile file number=00010 name=/u02/oradata/asmtest/AUT_LM_INDEX_1M_01.DBF

input datafile file number=00020 name=/u02/oradata/asmtest/BWA_LM_INDEX_512K_01.DBF

input datafile file number=00221 name=/u02/oradata/asmtest/CSHIDX020_01.DBF

input datafile file number=00065 name=/u02/oradata/asmtest/CB022_01.DBF

input datafile file number=00070 name=/u02/oradata/asmtest/CB024_02.DBF

input datafile file number=00075 name=/u02/oradata/asmtest/CB027_01.DBF

input datafile file number=00080 name=/u02/oradata/asmtest/CB029_02.DBF

input datafile file number=00085 name=/u02/oradata/asmtest/CB032_01.DBF

input datafile file number=00090 name=/u02/oradata/asmtest/CB034_02.DBF

input datafile file number=00095 name=/u02/oradata/asmtest/CB037_01.DBF

input datafile file number=00100 name=/u02/oradata/asmtest/CB039_02.DBF

input datafile file number=00124 name=/u02/oradata/asmtest/CBIDX024_01.DBF

input datafile file number=00129 name=/u02/oradata/asmtest/CBIDX029_01.DBF

input datafile file number=00134 name=/u02/oradata/asmtest/CBIDX034_01.DBF

input datafile file number=00139 name=/u02/oradata/asmtest/CBIDX039_01.DBF

input datafile file number=00158 name=/u02/oradata/asmtest/CPS_LOB_LM_64_01.DBF

input datafile file number=00199 name=/u02/oradata/asmtest/CSH018_01.DBF

input datafile file number=00259 name=/u02/oradata/asmtest/XMLDATA001.DBF

channel dev1: starting piece 1 at 31-08-2011 15:06:13

 

 

nput datafile file number=00195 name=/u02/oradata/asmtest/CSH014_01.DBF

input datafile file number=00200 name=/u02/oradata/asmtest/CSH019_01.DBF

channel dev1: starting piece 1 at 31-08-2011 15:07:42

channel dev1: finished piece 1 at 31-08-2011 15:08:07

piece handle=+DATA/asmtest/backupset/2011_08_31/nnndn1_ora_asm_migration_0.852.760633663 tag=ORA_ASM_MIGRATION comment=NONE

channel dev1: backup set complete, elapsed time: 00:00:25

channel dev1: starting incremental level 1 datafile backup set

channel dev1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel dev1: starting piece 1 at 31-08-2011 15:08:08

channel dev1: finished piece 1 at 31-08-2011 15:08:09

piece handle=+DATA/asmtest/backupset/2011_08_31/ncsnn1_ora_asm_migration_0.853.760633689 tag=ORA_ASM_MIGRATION comment=NONE

channel dev1: backup set complete, elapsed time: 00:00:01

Finished backup at 31-08-2011 15:08:09

released channel: dev1

 

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 statement: 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 31-08-2011 15:09:48

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=204 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 31-08-2011 15:09:49

channel ORA_DISK_1: finished piece 1 at 31-08-2011 15:09:50

piece handle=+DATA/asmtest/backupset/2011_08_31/nnsnf0_tag20110831t150949_0.855.760633789 tag=TAG20110831T150949 comment=NONE

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

Finished backup at 31-08-2011 15:09:50

 

 

If block change tracking is enabled, then disable it.

The following command disables block change tracking:

 

RMAN> SQL “ALTER DATABASE DISABLE BLOCK CHANGE TRACKING”;

 

sql statement: ALTER DATABASE DISABLE BLOCK CHANGE TRACKING

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

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

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

RMAN-03009: failure of sql command on default channel at 08/31/2011 15:10:38

RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE DISABLE BLOCK CHANGE TRACKING

ORA-19759: block change tracking is not enabled

 

Shut down the database consistently.

The following command shuts down the database:

RMAN> SHUTDOWN IMMEDIATE;

database closed

database dismounted

Oracle instance shut down

 

 

ASM DISK USAGE AT THIS POINT AFTER DOING THE BACKUP

 

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 “FREE %” FROM v$asm_diskgroup;

 

NAME                              FREE_MB   TOTAL_MB     FREE %

—————————— ———- ———- ———-

DATA                               426002     514687 82.7691393

 

 

 

Migrating the Database to Oracle ASM Using RMAN

The following procedure is intended to minimize database downtime.

 

If the database is using a server parameter file, then restore it to the Oracle ASM disk group with the following commands, where sid is the SID of the instance:

 

RMAN> STARTUP MOUNT

 

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area     680607744 bytes

 

Fixed Size                     2222736 bytes

Variable Size                226493808 bytes

Database Buffers             444596224 bytes

Redo Buffers                   7294976 bytes

 

RMAN> run

2> {

3> allocate channel dev1 device type disk;

4> RESTORE SPFILE TO ‘+DATA/spfileasmtest.ora’;

5> }

 

allocated channel: dev1

channel dev1: SID=580 device type=DISK

 

Starting restore at 31-08-2011 15:18:28

 

channel dev1: starting datafile backup set restore

channel dev1: restoring SPFILE

output file name=+DATA/spfileasmtest.ora

channel dev1: reading from backup piece +DATA/asmtest/backupset/2011_08_31/nnsnf0_tag20110831t150949_0.855.760633789

channel dev1: piece handle=+DATA/asmtest/backupset/2011_08_31/nnsnf0_tag20110831t150949_0.855.760633789 tag=TAG20110831T150949

channel dev1: restored backup piece 1

channel dev1: restore complete, elapsed time: 00:00:05

Finished restore at 31-08-2011 15:18:33

released channel: dev1

 

 

 

 

SQL> show parameter spfile

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

spfile                               string      /u01/oracle/product/11.2.0.2/d

bs/spfileasmtest.ora

 

 

 

 

Set Oracle Managed Files initialization parameters to Oracle ASM locations.

Note:

If you are not migrating the fast recovery area, then do not change the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameter settings. However, you must set DB_CREATE_ONLINE_LOG_DEST_n parameter to an Oracle ASM location for migration of the online redo logs.

Set the DB_CREATE_FILE_DEST and optional DB_CREATE_ONLINE_LOG_DEST_n initialization parameters to Oracle ASM disk groups. If the database uses a recovery area, then change the recovery area location to the Oracle ASM disk group. Also, change the recovery area size.

 

 

RMAN> shutdown immediate

 

database dismounted

Oracle instance shut down

 

RMAN> STARTUP FORCE NOMOUNT;

 

Oracle instance started

 

Total System Global Area     680607744 bytes

 

Fixed Size                     2222736 bytes

Variable Size                230688112 bytes

Database Buffers             440401920 bytes

Redo Buffers                   7294976 bytes

 

 

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST=’+DATA’ SID=’*’;

 

System altered.

 

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SID=’*’;

 

System altered.

 

 

Set the CONTROL_FILES initialization parameter to Oracle ASM locations

 

SQL> STARTUP FORCE NOMOUNT;

ORACLE instance started.

 

Total System Global Area  680607744 bytes

Fixed Size                  2222736 bytes

Variable Size             230688112 bytes

Database Buffers          440401920 bytes

Redo Buffers                7294976 bytes

SQL> ALTER SYSTEM SET CONTROL_FILES=’+DATA’ SCOPE=SPFILE SID=’*’;

 

System altered.

 

Migrate the control file to Oracle ASM and mount the control file.

 

 

RMAN> RESTORE CONTROLFILE FROM ‘/u02/oradata/asmtest/control01.ctl’;

 

Starting restore at 31-08-2011 15:32:27

using channel ORA_DISK_1

 

channel ORA_DISK_1: copied control file copy

output file name=+DATA/asmtest/controlfile/current.857.760635149

Finished restore at 31-08-2011 15:32:28

 

RMAN> ALTER DATABASE MOUNT;

 

database mounted

released channel: ORA_DISK_1

 

Migrate the data files to Oracle ASM.

Use RMAN to switch to the database copy that you created in step above “Back up the data files to the Oracle ASM disk group” in “Preparing to Migrate the Database to Oracle ASM Using RMAN”. The switch renames all the data files to files on Oracle ASM disk groups. Afterward, recover the database. If incremental backups were taken, then RMAN applies them during recovery. For example, enter the following commands at the RMAN prompt:

SWITCH DATABASE TO COPY;

RUN

{

ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;

RECOVER DATABASE;

}

 

RMAN> SWITCH DATABASE TO COPY;

 

datafile 246 switched to datafile copy “+DATA/asmtest/datafile/docidx011.665.760633199”

datafile 247 switched to datafile copy “+DATA/asmtest/datafile/docidx012.664.760633199”

datafile 248 switched to datafile copy “+DATA/asmtest/datafile/docidx013.663.760633201”

datafile 249 switched to datafile copy “+DATA/asmtest/datafile/doctrap.662.760633203”

datafile 250 switched to datafile copy “+DATA/asmtest/datafile/mis_lm_data_1m.806.760633037”

datafile 251 switched to datafile copy “+DATA/asmtest/datafile/mis_lm_data_64m.812.760633019”

datafile 252 switched to datafile copy “+DATA/asmtest/datafile/mis_lm_data_8m.791.760633057”

datafile 253 switched to datafile copy “+DATA/asmtest/datafile/mis_lm_index_1m.805.760633039”

datafile 254 switched to datafile copy “+DATA/asmtest/datafile/mis_lm_index_8m.816.760633007”

datafile 255 switched to datafile copy “+DATA/asmtest/datafile/ro_test.656.760633209”

datafile 256 switched to datafile copy “+DATA/asmtest/datafile/statspack.815.760633009”

datafile 257 switched to datafile copy “+DATA/asmtest/datafile/user_data.661.760633203”

datafile 258 switched to datafile copy “+DATA/asmtest/datafile/user_index.660.760633205”

datafile 259 switched to datafile copy “+DATA/asmtest/datafile/xml_data.846.760633307”

datafile 260 switched to datafile copy “+DATA/asmtest/datafile/bw_audit.818.760632997”

datafile 261 switched to datafile copy “+DATA/asmtest/datafile/soe.814.760633013”

datafile 262 switched to datafile copy “+DATA/asmtest/datafile/soeindex.819.760632989”

 

RMAN> run

2> {

3> allocate channel dev1 device type disk;

4> recover database;

5> }

 

destination for restore of datafile 00231: +DATA/asmtest/datafile/doc009.679.760633183

destination for restore of datafile 00237: +DATA/asmtest/datafile/docidx002.674.760633189

destination for restore of datafile 00242: +DATA/asmtest/datafile/docidx007.669.760633193

destination for restore of datafile 00247: +DATA/asmtest/datafile/docidx012.664.760633199

destination for restore of datafile 00260: +DATA/asmtest/datafile/bw_audit.818.760632997

destination for restore of datafile 00261: +DATA/asmtest/datafile/soe.814.760633013

channel dev1: reading from backup piece +DATA/asmtest/backupset/2011_08_31/nnndn1_ora_asm_migration_0.852.760633663

channel dev1: piece handle=+DATA/asmtest/backupset/2011_08_31/nnndn1_ora_asm_migration_0.852.760633663 tag=ORA_ASM_MIGRATION

channel dev1: restored backup piece 1

channel dev1: restore complete, elapsed time: 00:00:03

 

starting media recovery

media recovery complete, elapsed time: 00:00:03

 

Finished recover at 31-08-2011 15:36:35

released channel: dev1

 

RMAN>

 

 

If the database uses block change tracking or Flashback Database, then enable these features

 

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘+DATA’;

 

Database altered.

 

SQL> ALTER DATABASE FLASHBACK ON;

 

Database altered.

 

SQL> ALTER DATABASE OPEN;

 

Database altered.

 

Drop the tempfiles and re-create them in Oracle ASM

 

SQL> select name from v$tempfile;

 

NAME

——————————————————————————–

/u02/oradata/asmtest/temp01.dbf

 

SQL> ALTER DATABASE TEMPFILE ‘/u02/oradata/asmtest/temp01.dbf’ DROP;

 

Database altered.

 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE;

 

Tablespace altered.

 

 

 

 

More info here:-

http://download.oracle.com/docs/cd/E11882_01/server.112/e16102/asm_rman.htm#i1022780

 

Posted in Database11g, Oracle11gR2 GRID INFRA and ASM | Leave a Comment »