ASM Instance Creation
Creating a dummy disks
First Create Folder where you want to store the asmdiks on window
and Then Create Three Disks of 2GB Each with the asmtool command
asmtool -create <Full Path with File Name> <Size in MB>
and Then Create Three Disks of 2GB Each with the asmtool command
asmtool -create <Full Path with File Name> <Size in MB>
D:\>cd D:\Oracle\app\oracle
D:\Oracle\app\oracle>mkdir asmdisks
D:\Oracle\app\oracle>cd asmdisks
D:\Oracle\app\oracle\asmdisks>asmtool -create D:\Oracle\app\oracle\asmdisks\disk1 2046
D:\Oracle\app\oracle\asmdisks>asmtool -create D:\Oracle\app\oracle\asmdisks\disk2 2046
D:\Oracle\app\oracle\asmdisks>asmtool -create D:\Oracle\app\oracle\asmdisks\disk3 2046
D:\Oracle\app\oracle\asmdisks>asmtool -create D:\Oracle\app\oracle\asmdisks\disk4 2046
D:\Oracle\app\oracle\asmdisks>asmtool -create D:\Oracle\app\oracle\asmdisks\disk5 2046
Now you have 3 disks (dummy) of 2GB each which can be used to create a ASM disk group.
D:\Oracle\app\oracle\asmdisks>dir
Volume in drive D is DATA
Volume Serial Number is B8B4-5E00
Directory of D:\Oracle\app\oracle\asmdisks
10/07/2010 03:03 PM <DIR> .
10/07/2010 03:03 PM <DIR> ..
10/07/2010 02:54 PM 2,145,386,496 disk1
10/07/2010 03:03 PM 2,145,386,496 disk2
10/07/2010 03:10 PM 2,145,386,496 disk3
3 File(s) 6,436,159,488 bytes
2 Dir(s) 82,173,317,120 bytes free
Create ASM instance
Configure Cluster Synchronization Services
D:\Oracle\app\oracle\product\11.1.0\db_1\BIN>localconfig add
Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'accounts\iqbal.aulakh', privgrp ''..
Operation successful.
Step 2: creating new CSS service
successfully created local CSS service
successfully added CSS to home
D:\Oracle\app\oracle\product\11.1.0\db_1\BIN>
Create Admin Directories
mkdir %ORACLE_BASE%\admin\+ASM\bdump
mkdir %ORACLE_BASE%\admin\+ASM\cdump
mkdir %ORACLE_BASE%\admin\+ASM\hdump
mkdir %ORACLE_BASE%\admin\+ASM\pfile
mkdir %ORACLE_BASE%\admin\+ASM\udump
Create Parameter File for ASM
Open notepad edit the following parameters and save file as
D:\Oracle\app\oracle\admin\+ASM\pfile\init+ASM.ora
###########################################
# Automatic Storage Management
###########################################
_ASM_ALLOW_ONLY_RAW_DISKS=false
# asm_diskgroups='TESTDB_DATA1'
ASM_DISKSTRING='D:\Oracle\app\oracle\asmdisks\*'
###########################################
# Miscellaneous
###########################################
INSTANCE_TYPE=ASM
DB_UNIQUE_NAME=+ASM
#compatible=10.1.0.4.0
###########################################
# Pools
###########################################
large_pool_size=12M
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=exclusive
remote_login_passwordfile=exclusive
Now create a pfile in the database directory in the ORACLE_HOME
"D:\Oracle\app\oracle\product\11.1.0\db_1\database\init+ASM.ora"
Add
IFILE=D:\Oracle\app\oracle\admin\+ASM\pfile\init+ASM.ora
And save the file
Create service and password file
oradim will create an ASM instance and start it automatically.cd D:\Oracle\app\oracle\product\11.1.0\db_1\database
D:\Oracle\app\oracle\product\11.1.0\db_1\database> oradim -new -asmsid +ASM -syspwd welcome123
-pfile D:\Oracle\app\oracle\product\11.1.0\db_1\database\init+ASM.ora -spfile
-startmode manual -shutmode immediate
Instance created.
C:\> oradim -edit -asmsid +ASM -startmode a
Starting the ASM Instance
D:\Oracle\app\oracle\product\11.1.0\db_1\BIN>set ORACLE_SID=+ASM
D:\Oracle\app\oracle\product\11.1.0\db_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Oct 8 10:29:23 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 509162388 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted
SQL> create spfile from pfile;
File created.
SQL> shutdown
ORA-15100: invalid or missing diskgroup name
ASM instance shutdown
SQL> startup
ASM instance started
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 509162388 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted
You will notice when starting the ASM instance, we received the error:
ORA-15110: no diskgroups mounted
This error can be safely ignored.
Notice also that we created a server parameter file (SPFILE) for the ASM instance. This allows Oracle to automatically record new disk group names in the asm_diskgroups instance parameter, so that those disk groups can be automatically mounted whenever the ASM instance is started.
Now that the ASM instance is started, all other Oracle database instances running on the same node will be able to find it.
Check the available candidate disks
SQL> COL PATH FORMAT A80
SQL> SET LINE 200
SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH
------------ ----------- ------- ------------ -------- ----------------------------------------------------
0 0 CLOSED CANDIDATE NORMAL D:\ORACLE\APP\ORACLE\ASMDISKS\DISK1
0 2 CLOSED CANDIDATE NORMAL D:\ORACLE\APP\ORACLE\ASMDISKS\DISK3
0 1 CLOSED CANDIDATE NORMAL D:\ORACLE\APP\ORACLE\ASMDISKS\DISK2
SQL>
Connect to the Database and Create Disk group
create diskgroup
DG_DATA1
external redundancy disk
'D:\Oracle\app\oracle\asmdisks\disk1',
'D:\Oracle\app\oracle\asmdisks\disk2',
'D:\Oracle\app\oracle\asmdisks\disk3';
SQL> create diskgroup
2 DG_DATA1
3 external redundancy disk
4 'D:\Oracle\app\oracle\asmdisks\disk1',
5 'D:\Oracle\app\oracle\asmdisks\disk2',
6 'D:\Oracle\app\oracle\asmdisks\disk3';
Diskgroup created.
SQL>
Create FRA Diskgroup
create diskgroup
DG_FRA1
external redundancy disk
'D:\Oracle\app\oracle\asmdisks\disk4',
'D:\Oracle\app\oracle\asmdisks\disk5';
SQL> COL PATH FORMAT A80
SQL> SET LINE 200
SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH
------------ ----------- ------- ------------ -------- --------------------------------------------------------------------------------
0 0 CLOSED CANDIDATE NORMAL D:\ORACLE\APP\ORACLE\ASMDISKS\DISK4
0 1 CLOSED CANDIDATE NORMAL D:\ORACLE\APP\ORACLE\ASMDISKS\DISK5
1 0 CACHED MEMBER NORMAL D:\ORACLE\APP\ORACLE\ASMDISKS\DISK1
1 1 CACHED MEMBER NORMAL D:\ORACLE\APP\ORACLE\ASMDISKS\DISK2
1 2 CACHED MEMBER NORMAL D:\ORACLE\APP\ORACLE\ASMDISKS\DISK3
SQL> create diskgroup
2 DG_FRA1
3 external redundancy disk
4 'D:\Oracle\app\oracle\asmdisks\disk4',
5 'D:\Oracle\app\oracle\asmdisks\disk5';
Diskgroup created.
Change PFILE to SPFILE, Add ASM Diskgroup parameter and your all set to go and use ASM.
SQL> CREATE SPFILE FROM PFILE;
File created.
SQL> STARTUP FORCE;
ASM instance started
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 509162388 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted
Add the asm_disgroups parameter and restart the database.
SQL> alter system set asm_diskgroups=DG_DATA1 scope=spfile;
System altered.
SQL> SHUTDOWN;
ORA-15100: invalid or missing diskgroup name
ASM instance shutdown
SQL> startup
ASM instance started
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 509162388 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL>
Verify/Check the Diskgroup in ASM
SQL> select name,state,type,total_MB,free_MB from v$ASM_DISKGROUP;
NAME STATE TYPE TOTAL_MB FREE_MB
------------------------------ ----------- ------ ---------- ----------
DG_DATA1 MOUNTED EXTERN 6138 6084
DG_FRA1 MOUNTED EXTERN 4092 4040
Migrating Databases from non-ASM to ASM
Check Archivelog Mode
We assumes the database is open and in ARCHIVELOG mode:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL>
List All the Files in the Database
the following query lists database files as they exist on the local file system for the ORCL database. All of the files listed in this query will be relocated from the local file system to ASM:
SQL> @D:\Oracle\app\oracle\scripts\dba_scripts\common\sql\dba_files_all.sql
Tablespace Name / File Class Filename File Size Auto Next Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
EXAMPLE D:\ORACLE\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF 104,857,600 YES 655,360 34,359,721,984
SYSAUX D:\ORACLE\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF 566,231,040 YES 10,485,760 34,359,721,984
SYSTEM D:\ORACLE\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF 723,517,440 YES 10,485,760 34,359,721,984
TEMP D:\ORACLE\APP\ORACLE\ORADATA\ORCL\TEMP01.DBF 20,971,520 YES 655,360 34,359,721,984
UNDOTBS1 D:\ORACLE\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF 47,185,920 YES 5,242,880 34,359,721,984
USERS D:\ORACLE\APP\ORACLE\ORADATA\ORCL\USERS01.DBF 5,242,880 YES 1,310,720 34,359,721,984
[ CONTROL FILE ] D:\ORACLE\APP\ORACLE\ORADATA\ORCL\CONTROL01.CTL
[ CONTROL FILE ] D:\ORACLE\APP\ORACLE\ORADATA\ORCL\CONTROL02.CTL
[ CONTROL FILE ] D:\ORACLE\APP\ORACLE\ORADATA\ORCL\CONTROL03.CTL
[ ONLINE REDO LOG ] D:\ORACLE\APP\ORACLE\ORADATA\ORCL\REDO01.LOG 52,428,800
[ ONLINE REDO LOG ] D:\ORACLE\APP\ORACLE\ORADATA\ORCL\REDO02.LOG 52,428,800
[ ONLINE REDO LOG ] D:\ORACLE\APP\ORACLE\ORADATA\ORCL\REDO03.LOG 52,428,800
---------------
sum 1,625,292,800
12 rows selected.
Target database using spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\ORACLE\APP\ORACLE\PRODUCT\1
1.1.0\DB_1\DATABASE\SPFILEORCL
.ORA
SQL>
Change control_file and db_create_file_dest and db_recovery_file_dest parameter
With the target database open, edit the initialization parameter control_files and db_create_file_dest to point to the ASM disk group +DG_DATA1. Also configure db_recovery_file_dest to point to the ASM disk group +DG_FRA1
ALTER SYSTEM SET control_files='+DG_DATA1' SCOPE=spfile;
ALTER SYSTEM SET db_create_file_dest='+DG_DATA1' SCOPE=spfile;
ALTER SYSTEM SET db_recovery_file_dest='+DG_FRA' SCOPE=spfile;
D:\Oracle\app\oracle\product\111~1.0\db_1\BIN>set ORACLE_SID=ORCL
D:\Oracle\app\oracle\product\111~1.0\db_1\BIN>sqlplus
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Oct 8 14:56:54 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string D:\ORACLE\APP\ORACLE\ORADATA\O
RCL\CONTROL01.CTL, D:\ORACLE\A
PP\ORACLE\ORADATA\ORCL\CONTROL
02.CTL, D:\ORACLE\APP\ORACLE\O
RADATA\ORCL\CONTROL03.CTL
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\Oracle\app\oracle\flash_rec
overy_area
db_recovery_file_dest_size big integer 2G
SQL>
SQL> ALTER SYSTEM SET control_files='+DG_DATA1' SCOPE=spfile;
System altered.
SQL> ALTER SYSTEM SET db_create_file_dest='+DG_DATA1' SCOPE=spfile;
System altered.
SQL> ALTER SYSTEM SET db_recovery_file_dest='+DG_FRA1' SCOPE=spfile;
System altered.
SQL>
Startup the target database in NOMOUNT mode
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 155190164 bytes
Database Buffers 373293056 bytes
Redo Buffers 5844992 bytes
SQL>
Copy Controlefile from local to ASM
From an RMAN session, copy one of your controlfiles from the local file system to its new location in ASM. The new controlfile will be copied to the value specified in the initialization parameter control_files
C:\Documents and Settings\Iqbal.Aulakh>set oracle_sid=orcl
C:\Documents and Settings\Iqbal.Aulakh>rman target / nocatalog
Recovery Manager: Release 11.1.0.6.0 - Production on Fri Oct 8 16:18:08 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (not mounted)
using target database control file instead of recovery catalog
RMAN> RESTORE CONTROLFILE FROM 'D:\ORACLE\APP\ORACLE\ORADATA\ORCL\CONTROL01.CTL';
Starting restore at 08-OCT-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DG_DATA1/orcl/controlfile/current.256.731866693
Finished restore at 08-OCT-10
RMAN>
Mount the Databases
From an RMAN or SQL*Plus session, mount the database. This will mount the database using the controlfile stored in ASM:
SQL> ALTER DATABASE MOUNT;
Database altered.
SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG_DATA1/orcl/controlfile/cur
rent.256.731866693
SQL>
Copy Database To ASM
From an RMAN session, copy the database files from the local file system to ASM
RMAN> BACKUP AS COPY DATABASE FORMAT '+DG_DATA1';
RMAN> BACKUP AS COPY DATABASE FORMAT '+DG_DATA1';
Starting backup at 08-OCT-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\ORACLE\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
output file name=+DG_DATA1/orcl/datafile/system.257.731866929 tag=TAG20101008T162206 RECID=2 STAMP=731867006
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\ORACLE\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
output file name=+DG_DATA1/orcl/datafile/sysaux.258.731867013 tag=TAG20101008T162206 RECID=3 STAMP=731867081
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=D:\ORACLE\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
output file name=+DG_DATA1/orcl/datafile/example.259.731867089 tag=TAG20101008T162206 RECID=4 STAMP=731867103
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\ORACLE\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
output file name=+DG_DATA1/orcl/datafile/undotbs1.260.731867105 tag=TAG20101008T162206 RECID=5 STAMP=731867110
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DG_DATA1/orcl/controlfile/backup.261.731867111 tag=TAG20101008T162206 RECID=6 STAMP=731867114
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\ORACLE\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
output file name=+DG_DATA1/orcl/datafile/users.262.731867115 tag=TAG20101008T162206 RECID=7 STAMP=731867115
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
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 08-OCT-10
channel ORA_DISK_1: finished piece 1 at 08-OCT-10
piece handle=+DG_DATA1/orcl/backupset/2010_10_08/nnsnf0_tag20101008t162206_0.263.731867117 tag=TAG20101008T162206 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-OCT-10
Switch Database to Copy
From an RMAN session, update the control file / data dictionary so that all database files point to the RMAN copy made in ASM:
RMAN> SWITCH DATABASE TO COPY;
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DG_DATA1/orcl/datafile/system.257.731866929"
datafile 2 switched to datafile copy "+DG_DATA1/orcl/datafile/sysaux.258.731867013"
datafile 3 switched to datafile copy "+DG_DATA1/orcl/datafile/undotbs1.260.731867105"
datafile 4 switched to datafile copy "+DG_DATA1/orcl/datafile/users.262.731867115"
datafile 5 switched to datafile copy "+DG_DATA1/orcl/datafile/example.259.731867089"
RMAN>
Recover the Database (Incomplete)
From a SQL*Plus session, perform incomplete recovery and open the database using the RESETLOGS option:
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 940275 generated at 10/08/2010 16:16:16 needed for thread 1
ORA-00289: suggestion : +DG_FRA1
ORA-15173: entry 'ORCL' does not exist in directory '/'
ORA-00280: change 940275 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Cancel
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL>
Drop and Re-Create Temp File
From a SQL*Plus session, re-create any tempfiles that are still currently on the local file system to ASM. This is done by simply dropping the tempfiles from the local file system and re-creating them in ASM. This example relies on the initialization parameter db_create_file_dest=+DG_DATA1:
select tablespace_name, file_name, bytes from dba_temp_files;
alter database tempfile
'D:\ORACLE\APP\ORACLE\ORADATA\ORCL\TEMP01.DBF'
drop including datafiles;
alter tablespace temp add tempfile size 50m
autoextend on next 25m maxsize unlimited;
select tablespace_name, file_name, bytes from dba_temp_files;
SQL> col file_name for a60
SQL> set line 200
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ ------------------------------------------------------------ ----------
TEMP D:\ORACLE\APP\ORACLE\ORADATA\ORCL\TEMP01.DBF 20971520
SQL>
SQL> alter database tempfile
2 'D:\ORACLE\APP\ORACLE\ORADATA\ORCL\TEMP01.DBF'
3 drop including datafiles;
Database altered.
SQL>
SQL> alter tablespace temp add tempfile size 50m
2 autoextend on next 25m maxsize unlimited;
Tablespace altered.
SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES
------------------------------ ------------------------------------------------------------ ----------
TEMP +DG_DATA1/orcl/tempfile/temp.264.731867703 52428800
SQL>
Recreate Online Redo Log Group
From a SQL*Plus session, re-create any online redo logfiles that are still currently on the local file system to ASM. This is done by simply dropping the logfiles from the local file system and re-creating them in ASM. This example relies on the initialization parameters db_create_file_dest=+DG_DATA1 and db_recovery_file_dest=+DG_FRA1:
Determine the current online redo logfiles to move to ASM by examining the file names (and sizes) from V$LOGFILE:
SQL> col member for a50
SQL> set line 200
SQL> select a.group#, a.member, b.bytes
2 from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES
---------- -------------------------------------------------- ----------
3 D:\ORACLE\APP\ORACLE\ORADATA\ORCL\REDO03.LOG 52428800
2 D:\ORACLE\APP\ORACLE\ORADATA\ORCL\REDO02.LOG 52428800
1 D:\ORACLE\APP\ORACLE\ORADATA\ORCL\REDO01.LOG 52428800
Force Log switch
Force a log switch until the last redo log is marked "CURRENT" by issuing the following command:
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
SQL>
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT
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 orcl (thread 1)
ORA-00312: online log 1 thread 1: 'D:\ORACLE\APP\ORACLE\ORADATA\ORCL\REDO01.LOG'
SQL> ALTER SYSTEM CHECKPOINT GLOBAL;
System altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
Re-create the dropped redo log group in ASM (and a different size if desired):
SQL> alter database add logfile group 1 size 50m;
Database altered.
After re-creating the online redo log group, force a log switch. The online redo log group just created should become the CURRENT one:
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 INACTIVE
3 CURRENT
SQL> alter system switch logfile;
System altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 ACTIVE
After re-creating the first online redo log group, loop back to drop / re-create the next online redo logfile until all logs are rebuilt in ASM.
SQL> ALTER SYSTEM CHECKPOINT GLOBAL;
System altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 50m;
Database altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 INACTIVE
SQL> alter system switch logfile;
System altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
SQL> ALTER SYSTEM CHECKPOINT GLOBAL;
System altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 50m;
Database altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 CURRENT
SQL>
Verify all online redo logfiles have been created in ASM:
SQL> select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES
---------- -------------------------------------------------- ----------
3 +DG_DATA1/orcl/onlinelog/group_3.267.731868375 52428800
2 +DG_DATA1/orcl/onlinelog/group_2.266.731868297 52428800
1 +DG_DATA1/orcl/onlinelog/group_1.265.731868109 52428800
1 +DG_FRA1/orcl/onlinelog/group_1.261.731868113 52428800
2 +DG_FRA1/orcl/onlinelog/group_2.263.731868301 52428800
3 +DG_FRA1/orcl/onlinelog/group_3.265.731868379 52428800
6 rows selected.
Copy SPFILE to ASM
Perform the following steps to relocate the SPFILE from the local file system to an ASM disk group.
Create a text-based initialization parameter file from the current binary SPFILE located on the local file system:
create pfile='D:\Oracle\app\oracle\admin\orcl\pfile\init_after_asm_mov.ora' from spfile;
SQL> create pfile='D:\Oracle\app\oracle\admin\orcl\pfile\init_after_asm_mov.ora' from spfile;
File created.
SQL>
CREATE SPFILE='+DG_DATA1/ORCL/spfileORCL.ora' FROM PFILE='D:\Oracle\app\oracle\admin\orcl\pfile\init_after_asm_mov.ora';
SQL> CREATE SPFILE='+DG_DATA1/ORCL/spfileORCL.ora' FROM PFILE='D:\Oracle\app\oracle\admin\orcl\pfile\init_after_asm_mov.ora';
File created.
SQL>
Shutdown the Oracle database:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Update the text-based init<SID>.ora file with the new location of the SPFILE in ASM:
SPFILE='+DG_DATA1/ORCL/spfileORCL.ora'
Remove (actually rename) the old SPFILE on the local file system so that the new text-based init<SID>.ora will be used:
Startup the database and verify all the files
SQL> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 167773076 bytes
Database Buffers 360710144 bytes
Redo Buffers 5844992 bytes
Database mounted.
Database opened.
SQL> @D:\Oracle\app\oracle\scripts\dba_scripts\common\sql\dba_files_all.sql
Tablespace Name / File Class Filename File Size Auto Next Max
----------------------------- ---------------------------------------------------------------- --------------- ---- --------------- ---------------
EXAMPLE +DG_DATA1/orcl/datafile/example.259.731867089 104,857,600 YES 655,360 34,359,721,984
SYSAUX +DG_DATA1/orcl/datafile/sysaux.258.731867013 593,625,088 YES 10,485,760 34,359,721,984
SYSTEM +DG_DATA1/orcl/datafile/system.257.731866929 723,517,440 YES 10,485,760 34,359,721,984
TEMP +DG_DATA1/orcl/tempfile/temp.264.731867703 52,428,800 YES 26,214,400 34,359,721,984
UNDOTBS1 +DG_DATA1/orcl/datafile/undotbs1.260.731867105 47,185,920 YES 5,242,880 34,359,721,984
USERS +DG_DATA1/orcl/datafile/users.262.731867115 5,242,880 YES 1,310,720 34,359,721,984
[ CONTROL FILE ] +DG_DATA1/orcl/controlfile/current.256.731866693
[ ONLINE REDO LOG ] +DG_DATA1/orcl/onlinelog/group_1.265.731868109 52,428,800
[ ONLINE REDO LOG ] +DG_DATA1/orcl/onlinelog/group_2.266.731868297 52,428,800
[ ONLINE REDO LOG ] +DG_DATA1/orcl/onlinelog/group_3.267.731868375 52,428,800
[ ONLINE REDO LOG ] +DG_FRA1/orcl/onlinelog/group_1.261.731868113 52,428,800
[ ONLINE REDO LOG ] +DG_FRA1/orcl/onlinelog/group_2.263.731868301 52,428,800
[ ONLINE REDO LOG ] +DG_FRA1/orcl/onlinelog/group_3.265.731868379 52,428,800
---------------
sum 1,841,430,528
13 rows selected.
Remove old database files from disks
At this point, the target database is open with all of its datafiles, controlfiles, online redo logfiles, tempfiles, and SPFILE stored in ASM. If we wanted to remove the database files that were stored on the local file system (which are actually now RMAN copies), this could be done from an RMAN session. You could also then remove the old version of the controfile(s) that were stored on the local file system:
RMAN> DELETE NOPROMPT FORCE COPY;
RMAN> DELETE NOPROMPT FORCE COPY;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=119 device type=DISK
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
8 1 A 08-OCT-10 940275 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
9 2 A 08-OCT-10 940275 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
10 3 A 08-OCT-10 940275 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
11 4 A 08-OCT-10 940275 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
12 5 A 08-OCT-10 940275 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
List of Control File Copies
===========================
Key S Completion Time Ckp SCN Ckp Time
------- - --------------- ---------- ---------------
6 A 08-OCT-10 940275 08-OCT-10
Name: +DG_DATA1/orcl/controlfile/backup.261.731867111
Tag: TAG20101008T162206
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
3 1 1 A 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00001_0731865465.001
4 1 1 A 08-OCT-10
Name: +DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_1.256.731867391
5 1 2 A 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00002_0731865465.001
6 1 2 A 08-OCT-10
Name: +DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_2.257.731867399
1 1 2 A 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00002_0731865465.001
2 1 2 A 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_08\O1_MF_1_2_6BZ8VTJT_.ARC
7 1 3 A 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00003_0731865465.001
8 1 3 A 08-OCT-10
Name: +DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_3.258.731867407
9 1 1 A 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00001_0731867390.001
10 1 1 A 08-OCT-10
Name: +DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_1.259.731867993
11 1 2 A 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00002_0731867390.001
12 1 2 A 08-OCT-10
Name: +DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_2.260.731867995
13 1 3 A 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00003_0731867390.001
14 1 3 A 08-OCT-10
Name: +DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_3.262.731868167
15 1 4 A 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00004_0731867390.001
16 1 4 A 08-OCT-10
Name: +DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_4.264.731868315
17 1 5 A 08-OCT-10
Name: D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00005_0731867390.001
18 1 5 A 08-OCT-10
Name: +DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_5.266.731868397
deleted datafile copy
datafile copy file name=D:\ORACLE\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF RECID=8 STAMP=731867263
deleted datafile copy
datafile copy file name=D:\ORACLE\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF RECID=9 STAMP=731867263
deleted datafile copy
datafile copy file name=D:\ORACLE\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF RECID=10 STAMP=731867263
deleted datafile copy
datafile copy file name=D:\ORACLE\APP\ORACLE\ORADATA\ORCL\USERS01.DBF RECID=11 STAMP=731867263
deleted datafile copy
datafile copy file name=D:\ORACLE\APP\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF RECID=12 STAMP=731867264
deleted control file copy
control file copy file name=+DG_DATA1/orcl/controlfile/backup.261.731867111 RECID=6 STAMP=731867114
deleted archived log
archived log file name=D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00001_0731865465.001 RECID=3 STAMP=731867398
deleted archived log
archived log file name=+DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_1.256.731867391 RECID=4 STAMP=731867398
deleted archived log
archived log file name=D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00002_0731865465.001 RECID=5 STAMP=731867406
deleted archived log
archived log file name=+DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_2.257.731867399 RECID=6 STAMP=731867406
deleted archived log
archived log file name=D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00002_0731865465.001 RECID=1 STAMP=731865875
deleted archived log
archived log file name=D:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_08\O1_MF_1_2_6BZ8VTJT_.ARC RECID=2 STAMP=731865875
deleted archived log
archived log file name=D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00003_0731865465.001 RECID=7 STAMP=731867410
deleted archived log
archived log file name=+DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_3.258.731867407 RECID=8 STAMP=731867410
deleted archived log
archived log file name=D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00001_0731867390.001 RECID=9 STAMP=731867995
deleted archived log
archived log file name=+DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_1.259.731867993 RECID=10 STAMP=731867995
deleted archived log
archived log file name=D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00002_0731867390.001 RECID=11 STAMP=731867995
deleted archived log
archived log file name=+DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_2.260.731867995 RECID=12 STAMP=731867995
deleted archived log
archived log file name=D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00003_0731867390.001 RECID=13 STAMP=731868166
deleted archived log
archived log file name=+DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_3.262.731868167 RECID=14 STAMP=731868166
deleted archived log
archived log file name=D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00004_0731867390.001 RECID=15 STAMP=731868317
deleted archived log
archived log file name=+DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_4.264.731868315 RECID=16 STAMP=731868317
deleted archived log
archived log file name=D:\ORACLE\APP\ORACLE\PRODUCT\11.1.0\DB_1\RDBMS\ARC00005_0731867390.001 RECID=17 STAMP=731868398
deleted archived log
archived log file name=+DG_FRA1/orcl/archivelog/2010_10_08/thread_1_seq_5.266.731868397 RECID=18 STAMP=731868398
Deleted 24 objects
RMAN>
No comments:
Post a Comment