2010 December 21 « Guenadi N Jilevski's Oracle BLOG

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.

December 21, 2010 Posted by gjilevski | 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

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

About

Guenadi Jilevski is a Senior Oracle Professional (OCP 7, 8, 8i, 9i, 10g, 11g, OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10 Certified Implementation Specialist and OCM 9i) 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, OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10 Certified Implementation Specialist and Oracle OCM, 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
Blog Stats
- 518,981 hits
Top Rated
Blogroll
- WordPress.com 0
- WordPress.org 0
Calendar

December 2010

M T W T F S S

« Oct Jan »

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
Search

Search for:
Email Subscription

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

Join 394 other followers
OraNA

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

Moving a database from file system to ASM and vice versa in Oracle 11gR2

About

Archives

Blog Stats

Top Rated

Blogroll

Calendar

Search

Email Subscription

OraNA

Archives

Categories

RSS

Site info

Follow “Guenadi N Jilevski's Oracle BLOG”

M	T	W	T	F	S	S
« Oct				Jan »
		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