Let’s have fun with instance today. Being an Oracle DBA we are well known for Instance. DBA’s life starts from Instance. In early days of DBA training, we introduced with Instance. When we enter to the new part of Oracle. we notice new teams. Now I am going to focus on ASM instance. Some starting lines remind the definition of Instance and ASM Instance.
What is an instance?
A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database.
Every running Oracle database is associated with at least one Oracle database instance. Because an instance exists in memory and a database exists on disk, an instance can exist without a database and a database can exist without an instance.
What is ASM instance?
An ASM instance is built on the same technology as an Oracle Database instance. An ASM instance has a System Global Area (SGA) and background processes that are similar to those of Oracle Database. However, because ASM performs fewer tasks than a database, an ASM SGA is much smaller than a database SGA. In addition, ASM has a minimal performance effect on a server. ASM instances mount disk groups to make ASM files available to database instances; ASM instances do not mount databases.
Prerequisites:
1. Oracle cluster need to be installed on that server( for ASM)
2. Create required asm disk groups.
EXAMPLE:
Currently the datafiles are in /u02 mount point. We will move them to ASM DISK GROUP +TEST_DG’.
Database Name: DEVDBA
Asm disk group: +TEST_DG’
First, move the controlfile to ASM disk
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
[oracle@TEST 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@TEST 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
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@TEST 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 evice 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
Once files are copied to ASM , switch database
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 completed
[oracle@TEST 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:
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@TEST 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@TEST 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.
[oracle@TEST 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@TEST 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
