Moving a database from file system to ASM and vice versa in Oracle 11gR2
Moving a database from file system to ASM and vice versa in Oracle 11gR2
Moving a database from/to ASM is based on RMAN’s ability to backup files as a copies and perform a switch database in the copied location. Database file management is further simplified if OMF and FRA are used, that is if db_create_file_dest and db_recovery_* parameters are specified.
1.Moving a database from file system to ASM
Prepare a pfile for the duration of the move.
This is the original pfile.
[oracle@raclinux1 dbs]$ cat inittst.ora.org
tst.__db_cache_size=171966464
tst.__java_pool_size=4194304
tst.__large_pool_size=4194304
tst.__pga_aggregate_target=180355072
tst.__sga_target=343932928
tst.__shared_io_pool_size=0
tst.__shared_pool_size=150994944
tst.__streams_pool_size=4194304
*.audit_file_dest=’/u01/app/oracle/admin/tst/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/app/oracle/TST/controlfile/o1_mf_6jy10hy0_.ctl’,’/u01/app/oracle/fast_recovery_area/TST/controlfile/o1_mf_6jy10jfk_.ctl’
*.db_block_size=8192
*.db_create_file_dest=’/u01/app/oracle’
*.db_domain=”
*.db_name=’tst’
*.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
*.db_recovery_file_dest_size=4227858432
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tstXDB)’
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
[oracle@raclinux1 dbs]$
This is a pfile for move to ASM. Note that control_files parameter is removed and db_create_file_dest and db_recovery_file_dest point to the disk goup.
[oracle@raclinux1 dbs]$ cat inittst.ora.new
tst.__db_cache_size=171966464
tst.__java_pool_size=4194304
tst.__large_pool_size=4194304
tst.__pga_aggregate_target=180355072
tst.__sga_target=343932928
tst.__shared_io_pool_size=0
tst.__shared_pool_size=150994944
tst.__streams_pool_size=4194304
*.audit_file_dest=’/u01/app/oracle/admin/tst/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.db_block_size=8192
*.db_create_file_dest=’+DGDUP’
*.db_domain=”
*.db_name=’tst’
*.db_recovery_file_dest=’+DGDUP’
*.db_recovery_file_dest_size=4227858432
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tstXDB)’
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
[oracle@raclinux1 dbs]$
This is a pfile for move from ASM to file system. Note that control_files parameter is removed and db_create_file_dest and db_recovery_file_dest point to the file system.
[oracle@raclinux1 dbs]$ cat inittst.ora.org1
tst.__db_cache_size=171966464
tst.__java_pool_size=4194304
tst.__large_pool_size=4194304
tst.__pga_aggregate_target=180355072
tst.__sga_target=343932928
tst.__shared_io_pool_size=0
tst.__shared_pool_size=150994944
tst.__streams_pool_size=4194304
*.audit_file_dest=’/u01/app/oracle/admin/tst/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.db_block_size=8192
*.db_create_file_dest=’/u01/app/oracle’
*.db_domain=”
*.db_name=’tst’
*.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
*.db_recovery_file_dest_size=4227858432
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tstXDB)’
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
[oracle@raclinux1 dbs]$
Take a note of the database file location prior to the move to ASM
[oracle@raclinux1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 20 03:23:48 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select name from v$controlfile;
NAME
——————————————————————————–
/u01/app/oracle/TST/controlfile/o1_mf_6jy10hy0_.ctl
/u01/app/oracle/fast_recovery_area/TST/controlfile/o1_mf_6jy10jfk_.ctl
SQL> select name from v$datafile;
NAME
——————————————————————————–
/u01/app/oracle/TST/datafile/o1_mf_system_6jy0osyn_.dbf
/u01/app/oracle/TST/datafile/o1_mf_sysaux_6jy0otdj_.dbf
/u01/app/oracle/TST/datafile/o1_mf_undotbs1_6jy0otxm_.dbf
/u01/app/oracle/TST/datafile/o1_mf_users_6jy0ovlz_.dbf
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
/u01/app/oracle/TST/onlinelog/o1_mf_3_6jy12241_.log
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_3_6jy129og_.log
/u01/app/oracle/TST/onlinelog/o1_mf_2_6jy11bh5_.log
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_2_6jy11rbj_.log
/u01/app/oracle/TST/onlinelog/o1_mf_1_6jy10nhn_.log
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_1_6jy10qcz_.log
6 rows selected.
SQL> select name from v$tempfile;
NAME
——————————————————————————–
/u01/app/oracle/TST/datafile/o1_mf_temp_6jy13z9k_.tmp
SQL>
Backup the database as a copy to the disk group.
[oracle@raclinux1 dbs]$ rman target /
Recovery Manager: Release 11.2.0.2.0 – Production on Mon Dec 20 04:13:10 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TST (DBID=1879374527)
RMAN> backup as copy database format ‘+DGDUP’;
Starting backup at 20-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/TST/datafile/o1_mf_system_6jy0osyn_.dbf
output file name=+DGDUP/tst/datafile/system.271.738217627 tag=TAG20101220T042705 RECID=2 STAMP=738217808
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/TST/datafile/o1_mf_sysaux_6jy0otdj_.dbf
output file name=+DGDUP/tst/datafile/sysaux.272.738217813 tag=TAG20101220T042705 RECID=3 STAMP=738217967
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:46
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/TST/datafile/o1_mf_undotbs1_6jy0otxm_.dbf
output file name=+DGDUP/tst/datafile/undotbs1.273.738217977 tag=TAG20101220T042705 RECID=4 STAMP=738217999
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DGDUP/tst/controlfile/backup.274.738218003 tag=TAG20101220T042705 RECID=5 STAMP=738218003
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=/u01/app/oracle/TST/datafile/o1_mf_users_6jy0ovlz_.dbf
output file name=+DGDUP/tst/datafile/users.275.738218007 tag=TAG20101220T042705 RECID=6 STAMP=738218007
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 20-DEC-10
channel ORA_DISK_1: finished piece 1 at 20-DEC-10
piece handle=+DGDUP/tst/backupset/2010_12_20/nnsnf0_tag20101220t042705_0.276.738218007 tag=TAG20101220T042705 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-DEC-10
RMAN>
Start the instance with the temporarily created pfile and leave the database in nomount state. Recover the control file. Mount the database switch to copy and recover. Open the database.
[oracle@raclinux1 dbs]$ rman target /
Recovery Manager: Release 11.2.0.2.0 – Production on Mon Dec 20 04:57:34 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount pfile=$ORACLE_HOME/dbs/inittst.ora.new
Oracle instance started
Total System Global Area 521936896 bytes
Fixed Size 2228072 bytes
Variable Size 343933080 bytes
Database Buffers 171966464 bytes
Redo Buffers 3809280 bytes
RMAN> restore controlfile from ‘/u01/app/oracle/TST/controlfile/o1_mf_6jy10hy0_.ctl’;
Starting restore at 20-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: copied control file copy
output file name=+DGDUP/tst/controlfile/current.277.738219655
output file name=+DGDUP/tst/controlfile/current.278.738219657
Finished restore at 20-DEC-10
RMAN>
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> switch database to copy;
datafile 1 switched to datafile copy “+DGDUP/tst/datafile/system.271.738217627”
datafile 2 switched to datafile copy “+DGDUP/tst/datafile/sysaux.272.738217813”
datafile 3 switched to datafile copy “+DGDUP/tst/datafile/undotbs1.273.738217977”
datafile 4 switched to datafile copy “+DGDUP/tst/datafile/users.275.738218007”
RMAN> recover database;
Starting recover at 20-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:05
Finished recover at 20-DEC-10
RMAN> alter database open;
database opened
RMAN>
Add new log file and a new temporary tablespace and drop the old redo logs and temporary tablespace. Make sure that the spfile is created to take care of the control files.
[oracle@raclinux1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 20 05:09:42 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
tst
SQL> select name from v$database;
NAME
———
TST
SQL> select name from v$datafile;
NAME
——————————————————————————–
+DGDUP/tst/datafile/system.271.738217627
+DGDUP/tst/datafile/sysaux.272.738217813
+DGDUP/tst/datafile/undotbs1.273.738217977
+DGDUP/tst/datafile/users.275.738218007
SQL> select name from v$controlfile;
NAME
——————————————————————————–
+DGDUP/tst/controlfile/current.277.738219655
+DGDUP/tst/controlfile/current.278.738219657
SQL>
SQL> alter database add logfile;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>
SQL> alter database add logfile ;
Database altered.
SQL>
SQL> column member format a90
SQL> select lf.member,l.group#,l.status from v$logfile lf, v$log l where lf.group#=l.group#;
MEMBER GROUP# STATUS
—————————————————————————————— ———- —————-
/u01/app/oracle/TST/onlinelog/o1_mf_3_6jy12241_.log 3 INACTIVE
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_3_6jy129og_.log 3 INACTIVE
/u01/app/oracle/TST/onlinelog/o1_mf_2_6jy11bh5_.log 2 INACTIVE
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_2_6jy11rbj_.log 2 INACTIVE
/u01/app/oracle/TST/onlinelog/o1_mf_1_6jy10nhn_.log 1 INACTIVE
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_1_6jy10qcz_.log 1 INACTIVE
+DGDUP/tst/onlinelog/group_4.279.738220289 4 INACTIVE
+DGDUP/tst/onlinelog/group_4.280.738220295 4 INACTIVE
+DGDUP/tst/onlinelog/group_5.281.738220315 5 CURRENT
+DGDUP/tst/onlinelog/group_5.282.738220321 5 CURRENT
+DGDUP/tst/onlinelog/group_6.285.738220875 6 UNUSED
MEMBER GROUP# STATUS
—————————————————————————————— ———- —————-
+DGDUP/tst/onlinelog/group_6.286.738220883 6 UNUSED
12 rows selected.
SQL>
SQL> alter database drop logfile group 1;
Database altered.
SQL> SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile 3;
alter database drop logfile 3
*
ERROR at line 1:
ORA-02236: invalid file name
SQL> alter database drop logfile group 3;
Database altered.
SQL>
SQL> select lf.member,l.group#,l.status from v$logfile lf, v$log l where lf.group#=l.group#;
MEMBER GROUP# STATUS
—————————————————————————————— ———- —————-
+DGDUP/tst/onlinelog/group_4.279.738220289 4 INACTIVE
+DGDUP/tst/onlinelog/group_4.280.738220295 4 INACTIVE
+DGDUP/tst/onlinelog/group_5.281.738220315 5 CURRENT
+DGDUP/tst/onlinelog/group_5.282.738220321 5 CURRENT
+DGDUP/tst/onlinelog/group_6.285.738220875 6 UNUSED
+DGDUP/tst/onlinelog/group_6.286.738220883 6 UNUSED
6 rows selected.
SQL>
SQL> create temporary tablespace temp1;
Tablespace created.
SQL> alter database default temporary tablespace temp1;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
SQL>
SQL> alter tablespace temp1 rename to temp;
Tablespace altered.
SQL>
SQL> select name from v$tempfile;
NAME
——————————————————————————————————————————————————————————————————–
+DGDUP/tst/tempfile/temp1.287.738221951
SQL> select name from v$datafile;
NAME
——————————————————————————————————————————————————————————————————–
+DGDUP/tst/datafile/system.271.738217627
+DGDUP/tst/datafile/sysaux.272.738217813
+DGDUP/tst/datafile/undotbs1.273.738217977
+DGDUP/tst/datafile/users.275.738218007
SQL> select name from v$controlfile;
NAME
——————————————————————————————————————————————————————————————————–
+DGDUP/tst/controlfile/current.277.738219655
+DGDUP/tst/controlfile/current.278.738219657
SQL> select member from v$logfile;
MEMBER
——————————————————————————————
+DGDUP/tst/onlinelog/group_4.279.738220289
+DGDUP/tst/onlinelog/group_4.280.738220295
+DGDUP/tst/onlinelog/group_5.281.738220315
+DGDUP/tst/onlinelog/group_5.282.738220321
+DGDUP/tst/onlinelog/group_6.285.738220875
+DGDUP/tst/onlinelog/group_6.286.738220883
6 rows selected.
SQL>
2. Moving from ASM to file system
Create a backup as copy of the database to a directory in the file system. The datafiles’ location is shown at the end of the previous task.
RMAN> backup as copy database format ‘/u01/app/oracle/TST/datafile/%U’;
Starting backup at 20-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DGDUP/tst/datafile/system.271.738217627
output file name=/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-SYSTEM_FNO-1_0om00prb tag=TAG20101220T061259 RECID=13 STAMP=738224186
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DGDUP/tst/datafile/sysaux.272.738217813
output file name=/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-SYSAUX_FNO-2_0pm00q22 tag=TAG20101220T061259 RECID=14 STAMP=738224354
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DGDUP/tst/datafile/undotbs1.273.738217977
output file name=/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-UNDOTBS1_FNO-3_0qm00q78 tag=TAG20101220T061259 RECID=15 STAMP=738224370
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/TST/datafile/cf_D-TST_id-1879374527_0rm00q7o tag=TAG20101220T061259 RECID=16 STAMP=738224382
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DGDUP/tst/datafile/users.275.738218007
output file name=/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-USERS_FNO-4_0sm00q7v tag=TAG20101220T061259 RECID=17 STAMP=738224386
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 20-DEC-10
RMAN>
Start the instance with the temporarily created pfile for the move from ASM to file system. Restore the control file. Switch the database and recover. Open the database.
[oracle@raclinux1 dbs]$ rman target /
Recovery Manager: Release 11.2.0.2.0 – Production on Mon Dec 20 06:30:27 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> shutdown immediate;
RMAN> startup nomount pfile=inittst.ora.org1;
Oracle instance started
Total System Global Area 521936896 bytes
Fixed Size 2228072 bytes
Variable Size 343933080 bytes
Database Buffers 171966464 bytes
Redo Buffers 3809280 bytes
RMAN> restore controlfile from ‘+DGDUP/tst/controlfile/current.277.738219655’;
Starting restore at 20-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/TST/controlfile/o1_mf_6jyhotol_.ctl
output file name=/u01/app/oracle/fast_recovery_area/TST/controlfile/o1_mf_6jyhovxm_.ctl
Finished restore at 20-DEC-10
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> switch database to copy;
datafile 1 switched to datafile copy “/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-SYSTEM_FNO-1_0om00prb”
datafile 2 switched to datafile copy “/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-SYSAUX_FNO-2_0pm00q22”
datafile 3 switched to datafile copy “/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-UNDOTBS1_FNO-3_0qm00q78”
datafile 4 switched to datafile copy “/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-USERS_FNO-4_0sm00q7v”
RMAN>
RMAN> recover database;
Starting recover at 20-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 20-DEC-10
RMAN> alter database open;
database opened
RMAN>
So far the data files and the control files are moved from ASM to file system. Create new log file and temporary tablespace and drop the old temporary tablespace and old redo logs. Make sure that the spfile is created to take care of the control files.
[oracle@raclinux1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 20 06:36:09 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select name from v$controlfile;
NAME
——————————————————————————–
/u01/app/oracle/TST/controlfile/o1_mf_6jyhotol_.ctl
/u01/app/oracle/fast_recovery_area/TST/controlfile/o1_mf_6jyhovxm_.ctl
SQL> select name from v$datafile;
NAME
——————————————————————————–
/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-SYSTEM_FNO-1_0om00prb
/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-SYSAUX_FNO-2_0pm00q22
/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-UNDOTBS1_FNO-3_0qm00q78
/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-USERS_FNO-4_0sm00q7v
SQL> select member from v$logfile;
MEMBER
——————————————————————————–
+DGDUP/tst/onlinelog/group_4.279.738220289
+DGDUP/tst/onlinelog/group_4.280.738220295
+DGDUP/tst/onlinelog/group_5.281.738220315
+DGDUP/tst/onlinelog/group_5.282.738220321
+DGDUP/tst/onlinelog/group_6.285.738220875
+DGDUP/tst/onlinelog/group_6.286.738220883
6 rows selected.
SQL>
SQL> alter database add logfile;
Database altered.
SQL> alter database add logfile;
Database altered.
SQL> alter database add logfile;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
alter database drop logfile group 5
*
ERROR at line 1:
ORA-01624: log 5 needed for crash recovery of instance tst (thread 1)
ORA-00312: online log 5 thread 1: ‘+DGDUP/tst/onlinelog/group_5.281.738220315’
ORA-00312: online log 5 thread 1: ‘+DGDUP/tst/onlinelog/group_5.282.738220321’
SQL>
SQL> select lf.member,l.group#,l.status from v$logfile lf, v$log l where lf.group#=l.group#;
MEMBER GROUP# STATUS
—————————————————————————————— ———- —————-
/u01/app/oracle/TST/onlinelog/o1_mf_1_6jyj3hk7_.log 1 ACTIVE
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_1_6jyj3ht7_.log 1 ACTIVE
/u01/app/oracle/TST/onlinelog/o1_mf_2_6jyj54df_.log 2 ACTIVE
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_2_6jyj56yg_.log 2 ACTIVE
/u01/app/oracle/TST/onlinelog/o1_mf_3_6jyj6kph_.log 3 CURRENT
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_3_6jyj6kxn_.log 3 CURRENT
+DGDUP/tst/onlinelog/group_5.281.738220315 5 INACTIVE
+DGDUP/tst/onlinelog/group_5.282.738220321 5 INACTIVE
8 rows selected.
SQL> create temporary tablespace temp1;
Tablespace created.
SQL> alter database default temporary tablespace temp1;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
SQL>
SQL> select name from v$controlfile;
NAME
————————————————————————————————————————————————————————————————————————————————————————————————————
/u01/app/oracle/TST/controlfile/o1_mf_6jyhotol_.ctl
/u01/app/oracle/fast_recovery_area/TST/controlfile/o1_mf_6jyhovxm_.ctl
SQL> select name from v$datafile;
NAME
————————————————————————————————————————————————————————————————————————————————————————————————————
/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-SYSTEM_FNO-1_0om00prb
/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-SYSAUX_FNO-2_0pm00q22
/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-UNDOTBS1_FNO-3_0qm00q78
/u01/app/oracle/TST/datafile/data_D-TST_I-1879374527_TS-USERS_FNO-4_0sm00q7v
SQL> select name from v$tempfile;
NAME
————————————————————————————————————————————————————————————————————————————————————————————————————
/u01/app/oracle/TST/datafile/o1_mf_temp1_6jyjmflz_.tmp
SQL> select member from v$logfile;
MEMBER
——————————————————————————————
/u01/app/oracle/TST/onlinelog/o1_mf_1_6jyj3hk7_.log
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_1_6jyj3ht7_.log
/u01/app/oracle/TST/onlinelog/o1_mf_2_6jyj54df_.log
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_2_6jyj56yg_.log
/u01/app/oracle/TST/onlinelog/o1_mf_3_6jyj6kph_.log
/u01/app/oracle/fast_recovery_area/TST/onlinelog/o1_mf_3_6jyj6kxn_.log
6 rows selected.
SQL>
SQL> create spfile from pfile=’inittst.ora.org1′;
File created.
SQL>
4 Comments »
Leave a Reply
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Hello there. I’m so glad I found your blog, I actually discovered this by accident, when I’d been browsing Google for something else entirely, Just the same I’m here now and would certainly wish to express gratitude for a excellent blog posting and a over-all intriguing blog (I furthermore love the theme/design). I’ve saved it and in addition subscribed to your
[…] ASM, introduced in Oracle 10G, is Oracle solution for storing database files. In Oracle 11gR2 ASM was further enhanced with a general purpose ASM Cluster File system (ACFS) and ASM Dynamic Volume Manager (ADVM). Since ASM was introduced RMAN remains the only supported way to migrate databases from file systems to ASM and from ASM to file systems. For information and examples about how to use RMAN for migration to ASM click here. […]
Many thanks dear, It made life quite easier… thanks again.
Very rapidly this web page will be famous among
all blogging and site-building viewers, due to it’s
good articles