Guenadi N Jilevski's Oracle BLOG

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

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>

December 21, 2010 - Posted by | oracle

4 Comments »

  1. 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

  2. […] 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

  3. Many thanks dear, It made life quite easier… thanks again.

    Comment by ikram | October 13, 2011 | Reply

  4. 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: