CONVERTING NON-ASM INSTANCE TO ASM INSTANCE  

Tags

Database Name: DEVDBA

Asm disk group: +TEST_DG’

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP NOMOUNT

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            2214595736 bytes

Database Buffers         1.4865E+10 bytes

Redo Buffers               21708800 bytes

RESTORE THE CONTROLFILE TO DISK GROUP:

[oracle@VM1  admin]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Thu Oct 3 09:20:31 2013

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

connected to target database: DEVDB (not mounted)

RMAN>  restore controlfile to ‘+TEST_DG’ from ‘/u02/oradata/11.2.0.3/DEVDB/control01.ctl';

Starting restore at 03-OCT-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=193 device type=DISK

channel ORA_DISK_1: copied control file copy

Finished restore at 03-OCT-13

RMAN> exit

Recovery Manager complete.

[oracle@VM1  admin]$ s

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 09:25:18 2013

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

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            2214595736 bytes

Database Buffers         1.4865E+10 bytes

Redo Buffers               21708800 bytes

SWITCH CONTROLFILE TO DISKGROUP LOCATION:

SQL>  alter system set control_files=’+TEST_DG/DEVDB/CONTROLFILE/current.270.827832049′ scope=spfile;

System altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            2214595736 bytes

Database Buffers         1.4865E+10 bytes

Redo Buffers               21708800 bytes

Database mounted.

SQL> select name from v$controlfile;

NAME

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

+TEST_DG/devdb/controlfile/current.270.827832049

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

MOVE DATAFILE TO ASM:

[oracle@VM1  admin]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Thu Oct 3 09:28:11 2013

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

connected to target database: DEVDB (DBID=732555631, not open)

RMAN> configure device type disk parallelism 4;

using target database control file instead of recovery catalog

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

RMAN> backup as copy database format ‘+TEST_DG';

Starting backup at 03-OCT-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=233 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=241 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=249 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u02/oradata/DEVDB/system.dbf

channel ORA_DISK_2: starting datafile copy

input datafile file number=00002 name=/u02/oradata/DEVDB/sysaux.dbf

channel ORA_DISK_3: starting datafile copy

input datafile file number=00003 name=/u02/oradata/DEVDB/undotbs1.dbf

channel ORA_DISK_4: starting datafile copy

copying current control file

output file name=+TEST_DG/devdb/controlfile/backup.274.827832515 tag=TAG20131003T092833 RECID=1 STAMP=827832514

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

channel ORA_DISK_4: starting full datafile backup set

channel ORA_DISK_4: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ORA_DISK_4: starting piece 1 at 03-OCT-13

channel ORA_DISK_4: finished piece 1 at 03-OCT-13

piece handle=+TEST_DG/devdb/backupset/2013_10_03/nnsnf0_tag20131003t092833_0.275.827832515 tag=TAG20131003T092833 comment=NONE

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

output file name=+TEST_DG/devdb/datafile/sysaux.272.827832515 tag=TAG20131003T092833 RECID=2 STAMP=827832517

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:04

output file name=+TEST_DG/devdb/datafile/undotbs1.273.827832515 tag=TAG20131003T092833 RECID=3 STAMP=827832518

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:04

output file name=+TEST_DG/devdb/datafile/system.271.827832513 tag=TAG20131003T092833 RECID=4 STAMP=827832530

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

Finished backup at 03-OCT-13

RMAN> switch database to copy ;

datafile 1 switched to datafile copy “+TEST_DG/devdb/datafile/system.271.827832513″

datafile 2 switched to datafile copy “+TEST_DG/devdb/datafile/sysaux.272.827832515″

datafile 3 switched to datafile copy “+TEST_DG/devdb/datafile/undotbs1.273.827832515″

RMAN> ALTER DATABASE OPEN;

database opened

RMAN> exit

Recovery Manager complete.

[oracle@VM1  admin]$ s

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 09:29:26 2013

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

Connected to:

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

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> select file_name from dba_data_files;

FILE_NAME

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

+TEST_DG/devdb/datafile/system.271.827832513

+TEST_DG/devdb/datafile/sysaux.272.827832515

+TEST_DG/devdb/datafile/undotbs1.273.827832515

MOVE REDOLOGS TO ASM:

<Note: Make sure the log groups which you want to drop shouldn’t be in CURRENT/ACTIVE state, In case they are switch logfile and check the status>

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

GROUP#    MEMBERS STATUS

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

1          1 CURRENT

2          1 INACTIVE

3          1 INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 (‘+TEST_DG’) size 300M;

Database altered.

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

GROUP#    MEMBERS STATUS

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

1          1 CURRENT

2          1 INACTIVE

3          1 UNUSED

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 (‘+TEST_DG’) size 300M;

Database altered.

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

GROUP#    MEMBERS STATUS

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

1          1 CURRENT

2          1 UNUSED

3          1 UNUSED

SQL> alter system switch logfile;

System altered.

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

GROUP#    MEMBERS STATUS

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

1          1 ACTIVE

2          1 CURRENT

3          1 UNUSED

SQL>  alter system switch logfile;

System altered.

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

GROUP#    MEMBERS STATUS

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

1          1 ACTIVE

2          1 ACTIVE

3          1 CURRENT

SQL> alter system switch logfile;

System altered.

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

GROUP#    MEMBERS STATUS

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

1          1 CURRENT

2          1 INACTIVE

3          1 INACTIVE

SQL>  alter system switch logfile;

System altered.

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

GROUP#    MEMBERS STATUS

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

1          1 INACTIVE

2          1 CURRENT

3          1 INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 (‘+TEST_DG’) size 300M;

Database altered.

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

GROUP#    MEMBERS STATUS

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

1          1 UNUSED

2          1 CURRENT

3          1 INACTIVE

SQL> create pfile from spfile;

File created.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

MODIFY THE PFILE POINTING TO SPFILE:

[oracle@VM1  dbs] vi initDEVDB.ora

Add one parameter  .db_create_file_dest=+TEST_DG

SQL> startup pfile=’/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora';

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            2214595736 bytes

Database Buffers         1.4865E+10 bytes

Redo Buffers               21708800 bytes

Database mounted.

Database opened.

SQL> create spfile=’+TEST_DG’ from pfile=’/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora';

File created.

SQL> exit

ASMCMD> find –type parameterfile +TEST_DG *

+TEST_DG/DEVDB/PARAMETERFILE/spfile.279.827833001

##remove all entries and add the spfile parameter
$cd $ORACLE_HOME/dbs

[oracle@VM1  dbs] vi initDEVDB.ora

Spfile=’ +TEST_DG/DEVDB/PARAMETERFILE/spfile.279.827833001’

SQL> startup pfile=’/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora';

ORACLE instance started.

Total System Global Area 1.7103E+10 bytes

Fixed Size                  2245480 bytes

Variable Size            2214595736 bytes

Database Buffers         1.4865E+10 bytes

Redo Buffers               21708800 bytes

Database mounted.

Database opened.

MOVING THE TEMP FILE:

SQL> select name, bytes from v$tempfile;

NAME

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

BYTES

———-

/u02/oradata/DEVDB/temp01.dbf

104857600

SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> create temporary tablespace temp tempfile SIZE 10120M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select name from v$tempfile;

NAME

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

+TEST_DG/devdb/tempfile/temp.281.827833353

+TEST_DG/devdb/tempfile/temp1.280.827833319

SQL> drop tablespace temp1 including contents;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME

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

+TEST_DG/devdb/tempfile/temp.281.827833353

Ref: http://orasteps.wordpress.com/2013/04/23/migration-of-non-asm-11g-database-to-asm/

Deadlock

Tags

,

Arup Nandra:

http://arup.blogspot.com/2013/04/application-design-is-only-reason-for.html

Jonathan Lewis

http://jonathanlewis.wordpress.com/2011/08/29/deadlock-2/

http://jonathanlewis.wordpress.com/2011/04/08/deadlock/#more-6169

Khurram Siddiqui

http://oraware.blogspot.com/2009/03/oracle-locks.html

https://dl.dropboxusercontent.com/u/2326391/pachot.net/ora-demo/lock_dml_behaviour.html#2.10.4

Follow

Get every new post delivered to your Inbox.