Switching into asm


Now ASM is in and running i now need to switch all my various file types into it so i can them remove all my filesystems and sail on into the future....

First up lets connect to my basic test database (created using my minimal database post) and see where it's files currently are:


Setting Enviroment for TEST
[ oracle@server]:TEST :[ ~ ]# s

SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 28 09:24:32 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/12.0.0.1/dbs/dbs1TEST.dbf
/oracle/12.0.0.1/dbs/dbx1TEST.dbf
/oracle/12.0.0.1/dbs/dbu1TEST.dbf

SQL> select name from v$tempfile;

no rows selected

Hmm no tempfile created yet - lets add one

SQL> create temporary tablespace temp;
create temporary tablespace temp
                               *
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause


SQL> sho parameter create

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size          integer     8388608
create_stored_outlines             string
db_create_file_dest             string
db_create_online_log_dest_1         string
db_create_online_log_dest_2         string
db_create_online_log_dest_3         string
db_create_online_log_dest_4         string
db_create_online_log_dest_5         string
SQL> alter system set db_create_file_dest='/oracle/12.0.0.1/dbs/';

System altered.

SQL> create temporary tablespace temp;

Tablespace created.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/oracle/12.0.0.1/dbs/TEST/datafile/o1_mf_temp_9k0ok0xy_.tmp

OK that's done lets check logfile and controlfile


SQL>
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oracle/12.0.0.1/dbs/log1TEST.dbf
/oracle/12.0.0.1/dbs/log2TEST.dbf

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracle/12.0.0.1/dbs/cntrlTEST.dbf

SQL>

Lets get ourselves into archive log mode

SQL>
SQL> archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /oracle/12.0.0.1/dbs/arch
Oldest online log sequence     105
Current log sequence           106
SQL> startup force mount;
ORACLE instance started.

Total System Global Area 1169227776 bytes
Fixed Size            2680792 bytes
Variable Size         1107298344 bytes
Database Buffers       50331648 bytes
Redo Buffers            8916992 bytes
Database mounted.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> alter database open;

Database altered.

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1169227776 bytes
Fixed Size            2680792 bytes
Variable Size         1107298344 bytes
Database Buffers       50331648 bytes
Redo Buffers            8916992 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

Right back to ASM - lets assume i have no diskgroups created and do them from scratch


Setting Enviroment for +ASM
[ oracle@server]:+ASM :[ ~ ]# sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 28 09:29:39 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL>  create diskgroup data external redundancy disk '/dev/raw/raw1','/dev/raw/raw2','/dev/raw/raw3','/dev/raw/raw4','/dev/raw/raw5';

Diskgroup created.

SQL> create diskgroup fra external redundancy disk '/dev/raw/raw6','/dev/raw/raw7';

Diskgroup created.

SQL>

OK - now we have some space in ASM - how do we get thos pesky files in there? There is more than one way to do this but the simplest (to me at least) would seem to be rman.

First up we have to take an 'image' copy of the database and put those files in ASM. It has to be an image copy rather than a normal rman backup - si it's  a byte for byte copy of the file to enable us to switch to it.

To create thie image copy we use the keywords 'as copy'.

[ oracle@server]:TEST :[ ~ ]# rman target=/

Recovery Manager: Release 12.1.0.1.0 - Production on Fri Feb 28 09:32:11 2014

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

connected to target database: TEST (DBID=2136645407)

RMAN> backup as copy database format '+DATA';

Starting backup at 28-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=291 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/12.0.0.1/dbs/dbx1TEST.dbf
output file name=+DATA/TEST/DATAFILE/sysaux.256.840706351 tag=TAG20140228T093227 RECID=1 STAMP=840706351
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/oracle/12.0.0.1/dbs/dbu1TEST.dbf
output file name=+DATA/TEST/DATAFILE/sys_undots.257.840706355 tag=TAG20140228T093227 RECID=2 STAMP=840706355
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/12.0.0.1/dbs/dbs1TEST.dbf
output file name=+DATA/TEST/DATAFILE/system.258.840706355 tag=TAG20140228T093227 RECID=3 STAMP=840706356
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/TEST/CONTROLFILE/backup.259.840706357 tag=TAG20140228T093227 RECID=4 STAMP=840706356
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-FEB-14

RMAN>


RMAN>

So now we have full copies of the datafiles in ASM - but they lag behind the current version of the files (if only by a few minutes) - so how can we make use of these files and flip to them rather than the files in the filesystem.

Again rman helps us here by using the 'switch' command. However as the files are not completely up to date we have to take the database down, do the switch command and then recover to apply the redo to bring it up to current time - we can then open the database as normal

RMAN> switch database to copy;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 02/28/2014 09:34:54
RMAN-06572: database is open and datafile 1 is not offline

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    1169227776 bytes

Fixed Size                     2680792 bytes
Variable Size               1107298344 bytes
Database Buffers              50331648 bytes
Redo Buffers                   8916992 bytes

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/TEST/DATAFILE/system.258.840706355"
datafile 2 switched to datafile copy "+DATA/TEST/DATAFILE/sysaux.256.840706351"
datafile 3 switched to datafile copy "+DATA/TEST/DATAFILE/sys_undots.257.840706355"

RMAN> recover database;

Starting recover at 28-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=288 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 28-FEB-14

RMAN> alter database open;

Statement processed

RMAN>

So all seemed to go well let's log on and see where the files are located now.

[ oracle@server]:TEST :[ ~ ]# s

SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 28 09:41:32 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/TEST/DATAFILE/system.258.840706355
+DATA/TEST/DATAFILE/sysaux.256.840706351
+DATA/TEST/DATAFILE/sys_undots.257.840706355

So datafiles are done - lets look at the other file types

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracle/12.0.0.1/dbs/cntrlTEST.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oracle/12.0.0.1/dbs/log1TEST.dbf
/oracle/12.0.0.1/dbs/log2TEST.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/oracle/12.0.0.1/dbs/TEST/datafile/o1_mf_temp_9k0ok0xy_.tmp

SQL>

But before we do that we missed an even more fundamental file - the spfile

Let's try and create one from the current pfile

SQL>
SQL>
SQL> create spfile='+DATA' from pfile;

File created.

Lets check if that updated the parameter

SQL> sho parameter spfile;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string


And it didnt - lets bounce and see if it picks it up

SQL>
SQL> startup force
ORACLE instance started.

Total System Global Area 1169227776 bytes
Fixed Size            2680792 bytes
Variable Size         1107298344 bytes
Database Buffers       50331648 bytes
Redo Buffers            8916992 bytes
Database mounted.
Database opened.
SQL> sho parameter spfile;


NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string

At this point i realised that the spfile does exist inside ASM but the database doesn't know where to find it (it can't use the trick ASM does to locate it). To make use of it we have to have a pfile with a single entry pointing to the spfile location inside ASM.

As this is single instance i couldn't really see the point of having the spfile inside ASM - so i just decided to create it locally as 'normal'

SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 28 09:48:25 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create spfile from pfile;

File created.

SQL> sho parameter spfile

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string
SQL> startup force
ORACLE instance started.

Total System Global Area 1169227776 bytes
Fixed Size            2680792 bytes
Variable Size         1107298344 bytes
Database Buffers       50331648 bytes
Redo Buffers            8916992 bytes
Database mounted.
Database opened.
SQL> sho parameter spfile

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /oracle/12.0.0.1/dbs/spfileTES
                         T.ora
SQL>

Ok - now we have the (non ASM) spfile - lets work through the other file types - lets first check we can create new files for the datafiles OK inside ASM. First we switch the create dest to ASM.

SQL> alter system set db_create_file_dest='+DATA';

System altered.

SQL> create tablespace is_this_asm;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/TEST/DATAFILE/system.258.840706355
+DATA/TEST/DATAFILE/sysaux.256.840706351
+DATA/TEST/DATAFILE/sys_undots.257.840706355
+DATA/TEST/DATAFILE/is_this_asm.261.840707399

SQL>

Yep - all looks good - now lets switch the FRA to ASM (+FRA)

SQL> sho parameter recov

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string
db_recovery_file_dest_size         big integer 0
db_unrecoverable_scn_tracking         boolean     TRUE
recovery_parallelism             integer     0
SQL> alter system set db_recovery_file_dest='+FRA';
alter system set db_recovery_file_dest='+FRA'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE


SQL> alter system set db_recovery_file_dest_size=2G;

System altered.

SQL> alter system set db_recovery_file_dest='+FRA';

System altered.

SQL>


SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

SQL>


SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     108
Next log sequence to archive   109
Current log sequence           109


Now lets create a new archivelog and check it went to ASM

SQL> alter system switch logfile;

System altered.

SQL>


SQL> select name from v$archived_log
  2 
SQL> /

NAME
--------------------------------------------------------------------------------
/oracle/12.0.0.1/dbs/arch1_107_838498975.dbf
/oracle/12.0.0.1/dbs/arch1_108_838498975.dbf
+FRA/TEST/ARCHIVELOG/2014_02_28/thread_1_seq_109.256.840707681

SQL>

Done - now lets do redo logs - the easiest way it just to add new groups and drop the old ones.

  1* select member,group# from v$logfile
SQL> /

MEMBER                       GROUP#
---------------------------------- ----------
/oracle/12.0.0.1/dbs/log1TEST.dbf        1
/oracle/12.0.0.1/dbs/log2TEST.dbf        2

SQL>
SQL> alter database add logfile group 3 size 64M;

Database altered.

SQL> alter database add logfile group 4 size 64M;

Database altered.

SQL>                                 
SQL> select member,group# from v$logfile
  2  /

MEMBER                       GROUP#
---------------------------------- ----------
/oracle/12.0.0.1/dbs/log1TEST.dbf        1
/oracle/12.0.0.1/dbs/log2TEST.dbf        2
+DATA/TEST/ONLINELOG/group_3.262.8        3
40707855

+FRA/TEST/ONLINELOG/group_3.257.84        3
0707855

+DATA/TEST/ONLINELOG/group_4.263.8        4
40707869


MEMBER                       GROUP#
---------------------------------- ----------
+FRA/TEST/ONLINELOG/group_4.258.84        4
0707869


6 rows selected.

SQL>


SQL>
SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance TEST (thread 1)
ORA-00312: online log 1 thread 1: '/oracle/12.0.0.1/dbs/log1TEST.dbf'


SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance TEST (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/oracle/12.0.0.1/dbs/log2TEST.dbf'


SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> select member,group# from v$logfile
  2  /

MEMBER                       GROUP#
---------------------------------- ----------
+DATA/TEST/ONLINELOG/group_3.262.8        3
40707855

+FRA/TEST/ONLINELOG/group_3.257.84        3
0707855

+DATA/TEST/ONLINELOG/group_4.263.8        4
40707869

+FRA/TEST/ONLINELOG/group_4.258.84        4
0707869

MEMBER                       GROUP#
---------------------------------- ----------


SQL>

OK - all logfiles switched - now lets do tempfiles - again this is easy as we don't lose anything by dropping and recreating temp.

SQL> drop tablespace temp;

Tablespace dropped.

SQL> create temporary tablespace temp;

Tablespace created.

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_         TS#     RFILE# STATUS  ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
    CON_ID
----------
     1        1891892 28-FEB-14           4      1 ONLINE  READ WRITE
 104857600    12800     104857600     8192
+DATA/TEST/TEMPFILE/temp.264.840708461
     0


SQL>

For completeness lets do the BCT file too

SQL>
SQL> alter database enable block change tracking;

Database altered.

Lets see if that created in ASM

Setting Enviroment for +ASM
[ oracle@server]:+ASM :[ ~ ]# asmcmd -p
ASMCMD [+] > ls
DATA/
FRA/
ASMCMD [+] > cd DATA
ASMCMD [+DATA] > ls
TEST/
ASMCMD [+DATA] > cd TEST
ASMCMD [+DATA/TEST] > ls
CHANGETRACKING/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD [+DATA/TEST] > cd CHANG*
ASMCMD [+DATA/TEST/CHANGETRACKING] > ls
ctf.265.840708491
ASMCMD [+DATA/TEST/CHANGETRACKING] >

OK - now controlfile - i thought this might be a little trickier involving the use of backup controlfile to trace etc - but actually its very simple

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracle/12.0.0.1/dbs/cntrlTEST.dbf

oracle@server]:TEST :[ ~ ]# rman target=/

Recovery Manager: Release 12.1.0.1.0 - Production on Fri Feb 28 10:26:14 2014

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area    1169227776 bytes

Fixed Size                     2680792 bytes
Variable Size               1107298344 bytes
Database Buffers              50331648 bytes
Redo Buffers                   8916992 bytes

RMAN> shutdown;                                                    

using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area    1169227776 bytes

Fixed Size                     2680792 bytes
Variable Size               1107298344 bytes
Database Buffers              50331648 bytes
Redo Buffers                   8916992 bytes

RMAN> restore controlfile from '/oracle/12.0.0.1/dbs/cntrlTEST.dbf';

Starting restore at 28-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=499 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/TEST/CONTROLFILE/current.266.840709661
output file name=+FRA/TEST/CONTROLFILE/current.260.840709661
Finished restore at 28-FEB-14

RMAN>    

[ oracle@server ]:TEST :[ ~ ]# s

SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 28 10:28:17 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> sho parameter cont

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time         integer     7
control_files                 string     +DATA/TEST/CONTROLFILE/current
                         .266.840709661, +FRA/TEST/CONT
                         ROLFILE/current.260.840709661
control_management_pack_access         string     DIAGNOSTIC+TUNING
global_context_pool_size         string



[ oracle@server]:TEST :[ ~ ]# s

SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 28 10:28:17 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> sho parameter cont

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time         integer     7
control_files                 string     +DATA/TEST/CONTROLFILE/current
                         .266.840709661, +FRA/TEST/CONT
                         ROLFILE/current.260.840709661
control_management_pack_access         string     DIAGNOSTIC+TUNING
global_context_pool_size         string
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL>

So actually very simple - thanks again to rman

While we are on the subject of rman - lets do a quick report to show all the main file locations

rman target=/

Recovery Manager: Release 12.1.0.1.0 - Production on Fri Feb 28 10:30:03 2014

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

connected to target database: TEST (DBID=2136645407)

RMAN> report schema
2> ;

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

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    311      SYSTEM               ***     +DATA/TEST/DATAFILE/system.258.840706355
2    659      SYSAUX               ***     +DATA/TEST/DATAFILE/sysaux.256.840706351
3    349      SYS_UNDOTS           ***     +DATA/TEST/DATAFILE/sys_undots.257.840706355
4    100      IS_THIS_ASM          ***     +DATA/TEST/DATAFILE/is_this_asm.261.840707399

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    100      TEMP                 32767       +DATA/TEST/TEMPFILE/temp.264.840708461

RMAN>

And there we have it - we moved everything into ASM with the minimum of fuss - we can even make this quicker still (for the datafiles by using more rman commands).

Comments