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

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

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

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

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

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

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>

December 21, 2010 - Posted by gjilevski | oracle

4 Comments »

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

Comment by capsiplex | December 22, 2010 | Reply
[…] 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. […]

Pingback by Transferring files between ASM and OS file system in Oracle 10g – 11gR2 (11.2.0.2) « Guenadi N Jilevski's Oracle BLOG | May 12, 2011 | Reply
Many thanks dear, It made life quite easier… thanks again.

Comment by ikram | October 13, 2011 | Reply
Very rapidly this web page will be famous among
all blogging and site-building viewers, due to it’s
good articles

Comment by Teena | June 8, 2015 | Reply

About

Guenadi Jilevski is a Senior Oracle Professional (OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and OCM 9i, OCM 11g) with more than 20 years of industrial experience in high end Oracle technology starting with Oracle RDBMS version 6 up to 11g. He has been involved with multi platform High Availability Solutions starting with OPS and Veritas and Sun cluster as well as complex multiple node RAC 9i, 10g and 11g implementations on AIX, Linux, Solaris and HP. He has experience in deploying disaster recovery solutions with standby databases compliant to Oracle MAA and third party solutions such as Shareplex and Goldengate. As a former employee of Oracle Corporation Advanced Technology Solutions Consulting Practice in Americas, Guenadi has been involved in multiple visible high end projects for RAC enabling EBS, Peoplesoft, Siebel ERP applications, end to end performance tuning, architectured High Availability and Disaster Recovery solutions to name a few. Presently he is working as A Senior Oracle Consultant in EMEA.

Guenadi Jilevski is Oracle OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and Oracle OCM9i , OCM 11g, Teradata Certified DBA, Informix Certified DBA.

The views expressed on this blog are my own and do not necessarily reflect the views of Current/Past/Future Employers. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect mine.

Archives
Archives
Blog Stats
- 1,172,092 hits
Top Rated
Blogroll
- WordPress.com 0
- WordPress.org 0
Calendar

December 2010

M T W T F S S

1 2 3 4 5

6 7 8 9 10 11 12

13 14 15 16 17 18 19

20 21 22 23 24 25 26

27 28 29 30 31

« Oct Jan »
Search

Search for:
Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,112 other followers

Email Address:
OraNA

Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG