Login to ASMCMD as grid user
+DATA is are target for storing the files :
ASMCMD> pwd
+DATA/IRT
ASMCMD> cd IRT
ASMCMD> ls -lt
ASMCMD> mkdir TEMPFILE PARAMETERFILE ONLINELOG DATAFILE CONROLFILE BACKUPSET
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
N TEMPFILE/
N PARAMETERFILE/
N ONLINELOG/
N DATAFILE/
N CONROLFILE/
N BACKUPSET/
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Change the Database to SPFILE instead of Pfile and set parameters:
bash-4.1$ pwd
/scratch/app/oracle/product/11.2.0/db_1/dbs
sqlplus '/as sysdba'
SQL> create spfile from pfile='/scratch/app/oracle/product/11.2.0/db_1/dbs/initIRT.ora';
File created.
SQL> !ls -tlr
total 40532
-rw-r--r-- 1 orausr oinstall 452 Nov 16 12:05 initIRT.ora
-rw-r----- 1 orausr asmadmin 1536 Nov 16 12:52 spfileIRT.ora
SQL> shutdown immediate
SQL> startup;
SQL> alter database disable block change tracking ; --Applies only if BCT is on
SQL> --Configuring the FRA
SQL> alter system set db_recovery_file_dest_size = 1024M;
SQL> alter system set db_recovery_file_dest = '+FRA';
SQL> alter system set control_files = '+DATA/IRT/controlfile/control_irt.dbf' scope=spfile;
SQL> shutdown immediate;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Moving the files to ASM storage.
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 620758200 bytes
Database Buffers 440401920 bytes
Redo Buffers 5541888 bytes
rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Nov 16 12:55:26 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: IRT (not mounted)
RMAN> restore controlfile from '/scratch/app/oracle/oradata/IRT/controlfiles/control1.ctl';
RMAN> alter database mount;
RMAN> backup as copy database format '+DATA';
Starting backup at 16-NOV-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/scratch/app/oracle/oradata/IRT/datafiles/system01.dbf
output file name=+DATA/irt/datafile/system.278.799505801 tag=TAG20121116T125640 RECID=1 STAMP=799505802
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/scratch/app/oracle/oradata/IRT/datafiles/sysaux01.dbf
output file name=+DATA/irt/datafile/sysaux.279.799505803 tag=TAG20121116T125640 RECID=2 STAMP=799505804
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/scratch/app/oracle/oradata/IRT/datafiles/users01.dbf
output file name=+DATA/irt/datafile/users.280.799505805 tag=TAG20121116T125640 RECID=3 STAMP=799505805
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/scratch/app/oracle/oradata/IRT/datafiles/undotbs01.dbf
output file name=+DATA/irt/datafile/undotbs1.281.799505805 tag=TAG20121116T125640 RECID=4 STAMP=799505806
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/irt/controlfile/backup.282.799505807 tag=TAG20121116T125640 RECID=5 STAMP=799505807
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 16-NOV-12
channel ORA_DISK_1: finished piece 1 at 16-NOV-12
piece handle=+DATA/irt/backupset/2012_11_16/nnsnf0_tag20121116t125640_0.283.799505809 tag=TAG20121116T125640 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-NOV-12
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/irt/datafile/system.278.799505801"
datafile 2 switched to datafile copy "+DATA/irt/datafile/sysaux.279.799505803"
datafile 3 switched to datafile copy "+DATA/irt/datafile/undotbs1.281.799505805"
datafile 4 switched to datafile copy "+DATA/irt/datafile/users.280.799505805"
RMAN> exit
sqlplus '/as sysdba'
SQL> alter database open;
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/irt/datafile/system.278.799505801
+DATA/irt/datafile/sysaux.279.799505803
+DATA/irt/datafile/undotbs1.281.799505805
+DATA/irt/datafile/users.280.799505805
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/control_irt.dbf
SQL> select member,group# from v$logfile;
MEMBER GROUP#
---------------------------------------------------------------------- ----------
/scratch/app/oracle/oradata/IRT/onlinelogfiles/redo01.log 1
/scratch/app/oracle/oradata/IRT/onlinelogfiles/redo02.log 2
/scratch/app/oracle/oradata/IRT/onlinelogfiles/redo03.log 3
SQL> select file_name from dba_temp_files;
FILE_NAME
------------------------------------------------------
/scratch/app/oracle/oradata/IRT/tempfiles/temp01.dbf
SQL> select tablespace_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
TEMPTS1
SQL> alter tablespace TEMPTS1 add tempfile size 100m;
Tablespace altered.
SQL> alter database tempfile '/scratch/app/oracle/oradata/IRT/tempfiles/temp01.dbf' drop;
Database altered.
SQL> alter database add logfile group 5 size 100M;
Database altered.
SQL> alter database add logfile group 6 size 100M;
Database altered.
SQL> alter database add logfile group 7 size 100M;
Database altered.
SQL> select member,group# from v$logfile;
MEMBER GROUP#
---------------------------------------------------------------------- ----------
/scratch/app/oracle/oradata/IRT/onlinelogfiles/redo01.log 1
/scratch/app/oracle/oradata/IRT/onlinelogfiles/redo02.log 2
/scratch/app/oracle/oradata/IRT/onlinelogfiles/redo03.log 3
+DATA/irt/onlinelog/group_5.285.799508049 5
+DATA/irt/onlinelog/group_6.286.799508059 6
+DATA/irt/onlinelog/group_7.287.799508065 7
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select member,group# from v$logfile;
MEMBER GROUP#
---------------------------------------------------------------------- ----------
+DATA/irt/onlinelog/group_5.285.799508049 5
+DATA/irt/onlinelog/group_6.286.799508059 6
+DATA/irt/onlinelog/group_7.287.799508065 7
SQL> create pfile='/scratch/app/oracle/product/11.2.0/db_1/dbs/initIRT.ora' from spfile;
File created.
SQL> shutdown immediate;
@@@@@@@@@@@@@@@@@@@@@@@@
Login to asmcmd as grid
ASMCMD> pwd
+DATA
ASMCMD> cp control_irt.dbf control_irt01.ctl
copying +DATA/control_irt.dbf -> +DATA/control_irt01.ctl
ASMCMD> cp control_irt01.ctl control_irt02.ctl
copying +DATA/control_irt01.ctl -> +DATA/control_irt02.ctl
ASMCMD> cp control_irt01.ctl control_irt03.ctl
copying +DATA/control_irt01.ctl -> +DATA/control_irt03.ctl
ASMCMD> exit
@@@@@@@@@@@@@@@@@@@@@@@@
Edit the pfile /scratch/app/oracle/product/11.2.0/db_1/dbs/initIRT.ora for control_files value:
-bash-4.1$ cat /tmp/init.ora
IRT.__db_cache_size=436207616
IRT.__java_pool_size=4194304
IRT.__large_pool_size=12582912
IRT.__oracle_base='/scratch/app/oracle'#ORACLE_BASE set from environment
IRT.__pga_aggregate_target=432013312
IRT.__sga_target=641728512
IRT.__shared_io_pool_size=0
IRT.__shared_pool_size=176160768
IRT.__streams_pool_size=0
*.control_files='+DATA/control_irt01.ctl','+DATA/control_irt02.ctl','+DATA/control_irt03.ctl' #This is the value we are interested in
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='IRT'
*.fast_start_mttr_target=500
*.job_queue_processes=10
*.memory_max_target=1024M
*.memory_target=1024M
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Now start the database using this pfile.
SQL> sqlplus '/as sysdba'
SQL> startup nomount pfile='/scratch/app/oracle/product/11.2.0/db_1/dbs/initIRT.ora';
SQL> alter database mount;
SQL> create spfile '+DATA/irt/spfile' from pfile;
SQL> shutdown immediate;
@@@@@@@@@@@@@@@@@@@@@@@@
Now that we have created SPFILE from pfile , edit the pfile in the dbs dir and create a link to the spfile.
-bash-4.1$ pwd
/scratch/app/oracle/product/11.2.0/db_1/dbs
-bash-4.1$ cat initIRT.ora
spfile='+DATA/irt/spfile'
@@@@@@@@@@@@@@@@@@@@@@@@
startup the databae
sqlplus '/as sysdba'
SQL> startup;
@@@@@@@@@@@@@@@@@@@@@@@@
After these control file , online redo logfile and datafiles folder can be removed.
