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
Post a Comment