Guenadi N Jilevski's Oracle BLOG

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

RAC enabling single instance database using RCONFIG with Oracle 11gR2

RAC enabling single instance database using RCONFIG with Oracle 11gR2

Conversion from a single instance database to RAC database can be done with the following tools.

  • DBCA – for more info click here.
  • Enterprise Manager – for more info click here.
  • Rconfig – scope of the present paper.

The paper will look at the steps to convert a single instance database to RAC database on ASM using rconfig utility from Oracle 11gR2 (11.2.0.2). In $ORACLE_HOME/assistants/rconfig/sampleXMLs directory there are two templates ConvertToRAC_AdminManaged.xml and ConvertToRAC_PolicyManaged.xml that are used to convert a single instance database to RAC admin or policy managed database respectively. In the paper a conversion to an admin managed RAC database will be covered although the same approach is applicable to a policy managed database. Rconfig utility takes as an input a customized template and produces a RAC enable database upon successful completion. Base templates provided in $ORACLE_HOME/assistants/rconfig/sampleXMLs are customized by providing the information in the list below.

  • Source $OH – OracleHome of non-rac database for SourceDBHome
  • Destination $OH – racleHome where the rac database should be configured.
  • SourceDBInfo SID
  • list of nodes that should have rac instances running for the Admin Managed Cluster Database. LocalNode should be the first node
  • Instance Prefix tag is optional starting with 11.2.
  • Specify the type of storage to be used by RAC database. ASM or Cluster File system
  • Specify Database Area Location to be configured for rac database.
  • Specify Fast Recovery Area to be configured for rac database.

The content of the In $ORACLE_HOME/assistants/rconfig/sampleXMLs templates are shown in the appendix. I created two customized templates /tmp/conv_ver.xml and /tmp/conv_imp.xml reflecting my set up. The important field in the template is . Allowable values are: YES|NO|ONLY. If ONLY is specified rconfig only checks that the prerequisites are met and do not perform the conversion. This is good to test. If YES is specified the prerequisites are checked and if they met rconfig performs the actual conversion. Both /tmp/conv_ver.xml and /tmp/conv_imp.xml are customized to reflect the environment and differ only in the value of ‘Convert Verify’. Look for the customized content of /tmp/conv_ver.xml and /tmp/conv_imp.xml in the appendix. Rconfig utility performs the conversion for us using RMAN behind the stage. The logs are in $ORACLE_BASE/cfgtoollogs/rconfig directory.

Continue reading

December 21, 2010 Posted by | oracle | 1 Comment

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 | 3 Comments

   

Follow

Get every new post delivered to your Inbox.

Join 782 other followers