Virtual indexes in Oracle 10g.

Virtual indexes in Oracle 10g.

07:05:15 orcl>
07:05:20 orcl>
07:05:20 orcl>
07:05:21 orcl>
07:05:21 orcl>
07:05:25 orcl> Let?s describe the employees table and review the columns and datatypes.
07:05:25 orcl>
07:05:25 orcl> desc hr.employees;
?Name??????????????????????????????? ?????????????????????????????????????Null???? Type
?------------------------------------------------------------------------ -------- -------------------------------------------------
?EMPLOYEE_ID??????????????????????????????????????????????????????NUMBER(6)
?FIRST_NAME????????????????????????????????????????????????????????VARCHAR2(20)
?LAST_NAME??????????????????????????????????????????????????????????NOT NULL VARCHAR2(25)
?EMAIL???????????????????????????????????? ???????????????????????????????NOT NULL VARCHAR2(25)
?PHONE_NUMBER?????????????????????????????????????????????????VARCHAR2(20)
?HIRE_DATE???????????????????????????????????????????????????????????NOT NULL DATE
?JOB_ID??????????? ???????????????????????????????????????????????????????NOT NULL VARCHAR2(10)
?SALARY??????????????????????????????????????????????????????????????????NUMBER(8,2)
?COMMISSION_PCT????????????????????????????????????????????????NUMBER(2,2)
?MANAGER_ID?????????????????????????????????????????????????????????NUMBER(6)
?DEPARTMENT_ID??????????????????????????????????????????????????NUMBER(4)


Since I am using this table for other testing, let?s build a copy to use for our virtual index testing.


07:34:55 orcl> create table hr.employees2 as select * from hr.employees;

Table created.




We?ll duplicate a few rows in the table.

07:35:15 orcl> insert into hr.employees2 select * from hr.employees;
107 rows created.



Duplicating rows to pump up the volume in our test table.

07:36:17 orcl> r
? 1* insert into hr.employees2 select * from hr.employees2

178048 rows created.



Activating SQL*PLUS Autotrace to produce an Explain Plan.

07:50:38 orcl> set autotrace traceonly exp stat



Building a regular B-Tree index on the EMPLOYEEES2 table.? Notice that Autotrace does not produce Explain Plans for DDL statements.

07:52:19 orcl> create index hr.emp2_emp_id on hr.employees2 (employee_id);

Index created.



Running the statement using the Autotrace option to generate the access path.? Please note
that the optimizer has chosen the EMP2_EMP_ID index we just built.

07:54:52 orcl> ed
Wrote file afiedt.buf

? 1? select employee_id, a.department_id, b.department_name
? 2? from
? 3? hr.departments b,? hr.employees2 a
? 4? where
? 5? a.department_id = b.department_id
? 6* and employee_id = 203
07:55:14 ??7? /


Execution Plan
----------------------------------------------------------?????????????????????????????????????????????????????????????????????????
Plan hash value: 1466647341??????????????????????????????????????????????????????????????????????? ?????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
--------------------------------------------------------------------------------------------???????????????????????????????????????
| Id? | Operation??????????????????? | Name??????? | Rows? | Bytes | Cost (%CPU)| Time???? |???????????????????????????????????????
--------------------------------------------------------------------------------------------???????????????????????????????????????
|?? 0 | SELECT STATEMENT???????????? |???????????? |???? 1 |??? 25 |???? 5?? (0)| 00:00:01 |???????????????????????????????????????
|?? 1 |? NESTED LOOPS??????????????? |???????????? |???? 1 |??? 25 | ????5?? (0)| 00:00:01 |???????????????????????????????????????
|?? 2 |?? TABLE ACCESS BY INDEX ROWID| EMPLOYEES2? |???? 1 |???? 9 |???? 4?? (0)| 00:00:01 |???????????????????????????????????????
|*? 3 |??? INDEX RANGE SCAN????????? | EMP2_EMP_ID |???? 1 |?????? |???? 3?? (0)| 00:00:01 |???????????????????????????????????????
|?? 4 |?? TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |???? 1 |??? 16 |???? 1?? (0)| 00:00:01 |???????????????????????????????????????
|*? 5 |??? INDEX UNIQUE SCAN???????? | DEPT_ID_PK? |???? 1 |?????? |???? 0?? (0)| 00:00:01 |???????????????????????????????????????
--------------------------------------------------------------------------------------------???????????????????????????????????????
?????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????


Dropping the original index.

07:55:17 orcl> drop index hr.emp2_emp_id;

Index dropped.



Building our virtual index using the NOSEGMENT clause.

07:59:12 orcl> create index hr.emp2_emp_id_virtual on hr.employees2(employee_id) nosegment;

Index created.



Setting the hidden startup parameter "_use_nosegment_indexes" to TRUE so that our
session will recognize our new virtual index.

08:00:09 orcl> alter session set "_use_nosegment_indexes" = true;




Running our statement again to see if it will use our new virtual index.? Check outthe access path below.? The optimizer has chosen our virtual index.

? 1? select employee_id, a.department_id, b.department_name
? 2? from
? 3? hr.departments b,? hr.employees2 a
? 4? where
? 5? a.department_id = b.department_id
? 6* and employee_id = 203


Execution Plan
----------------------------------------------------------??????????????????? ??????????????????????????????????????????????????????
Plan hash value: 2516110069????????????????????????????????????????????????????????????????????????????????????????????????????????
??????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
| Id? | Operation??????????????????? | Name?????????????? ?| Rows? | Bytes | Cost (%CPU)| Time???? |???????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
|?? 0 | SELECT STATEMENT???????????? |????????? ???????????|???? 1 |??? 25 |???? 3?? (0)| 00:00:01 |???????????????????????????????
|?? 1 |? NESTED LOOPS??????????????? |???????????????????? |???? 1 |??? 25 |???? 3?? (0)| 00:00:01 |???????????????????????????????
|?? 2 |?? TABLE ACCESS BY INDEX ROWID| EMPLOYEES2????????? |???? 1 |???? 9 |???? 2?? (0)| 00:00:01 |???????????????????????????????
|*? 3 |??? INDEX RANGE SCAN????????? | EMP2_EMP_ID_VIRTUAL |???? 1 |?????? |???? 1?? (0)| 00:00:01 |???????????????????????????????
|?? 4 |?? TABLE ACCESS BY INDEX ROWID| DEPARTMENTS???????? |???? 1 |??? 16 |???? 1?? (0)| 00:00:01 |???????????????????????????????
|*? 5 |??? INDEX UNIQUE SCAN???????? | DEPT_ID_PK????????? |???? 1 |?????? |???? 0?? (0)| 00:00:01 |???????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
??????



Setting the "_use_nosegment_indexes? to FALSE.? Note that the optimizer did NOTchoose the virtual index.

08:01:09 orcl> alter session set "_use_nosegment_indexes" = false;

Session altered.

08:01:33 orcl> select employee_id, a.department_id, b.department_name
08:01:47?? 2? from
08:01:47?? 3? hr.departments b,? hr.employees2 a
08:01:47?? 4? where
08:01:47?? 5? a.department_id = b.department_id
08:01:47?? 6? and employee_id = 203;


Execution Plan
----------------------------------------------------------??????????????????????????????? ??????????????????????????????????????????
Plan hash value: 2641883601????????????????????????????????????????????????????????????????????????????????????????????????????????
??????????????????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????
--------------------------------------------------------------------------------------------???????????????????????????????????????
| Id? | Operation??????????????????? | Name??????? | Rows? | Bytes | Cost (%CPU)| Time???? |???????????????????????????????????????
--------------------------------------------------------------------------------------------???????????????????????????????????????
|?? 0 | SELECT STATEMENT???????????? |???????????? |???? 1 |??? 25 |?? 818?? (3)| 00:00:10 |???????????????????????????????????????
|?? 1 |? NESTED LOOPS??????????????? |???????????? |???? 1 |??? 25 |?? 818?? (3)| 00:00:10 |???????????????????????????????????????
|*? 2 |?? TABLE ACCESS FULL????????? | EMPLOYEES2 ?|???? 1 |???? 9 |?? 817?? (3)| 00:00:10 |???????????????????????????????????????
|?? 3 |?? TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |???? 1 |??? 16 |???? 1?? (0)| 00:00:01 |???????????????????????????????????????
|*? 4 |??? INDEX UNIQUE SCAN???????? | DEPT_ID_PK? |???? 1 |?????? |???? 0?? (0)| 00:00:01 |???????????????????????????????????????
--------------------------------------------------------------------------------------------?



Building another virtual index using the NOSEGMENT clause.? Note that Oracle returns an
error stating that the column is already indexed.

08:17:23 orcl>? create index hr.emp3_emp_id_virtual on hr.employees2(employee_id) nosegment;
create index hr.emp3_emp_id_virtual on hr.employees2(employee_id) nosegment
????????????????????????????????????????????????????? *
ERROR at line 1:
ORA-01408: such column list already indexed



Reactivating our session to begin recognizing virtual indexes.?

08:19:01 orcl> alter session set "_use_nosegment_indexes" = true;
Session altered.


Building a regular B-TREE index on the same column.? Although Oracle didn?t allow us to create twovirtual indexes on the same column, it did allow us to create one standard index and one virtual indexon the same column.08:19:12 orcl> create index hr.emp2_emp_id_non_virtual on hr.employees2(employee_id);Index created.


Running the statement again.? The optimizer chose the virtual index over the standard indexcontaining data!?? Interesting!
1? select employee_id, a.department_id, b.department_name
? 2? from
? 3? hr.departments b,? hr.employees2 a
? 4? where
? 5? a.department_id = b.department_id
? 6* and employee_id = 203

Execution Plan
----------------------------------------------------------?????????????????????????????????????????????????????????????????????????
Plan hash value: 2516110069????????????????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
| Id? | Operation??????????????????? | Name??????????????? | Rows? | Bytes | Cost (%CPU)| Time???? |???????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
|?? 0 | SELECT STATEMENT???????????? |???????????????????? |???? 1 |??? 25 |???? 3?? (0)| 00:00:01 |????????????????????? ??????????
|?? 1 |? NESTED LOOPS??????????????? |???????????????????? |???? 1 |??? 25 |???? 3?? (0)| 00:00:01 |???????????????????????????????
|?? 2 |?? TABLE ACCESS BY INDEX ROWID| EMPLOYEES2????????? |???? 1 |???? 9 |???? 2?? (0)| 00:00:01 |??????????? ????????????????????
|*? 3 |??? INDEX RANGE SCAN????????? | EMP2_EMP_ID_VIRTUAL |???? 1 |?????? |???? 1?? (0)| 00:00:01 |???????????????????????????????
|?? 4 |?? TABLE ACCESS BY INDEX ROWID| DEPARTMENTS???????? |???? 1 |??? 16 |???? 1?? (0)| 00:00:01 |? ??????????????????????????????
|*? 5 |??? INDEX UNIQUE SCAN???????? | DEPT_ID_PK????????? |???? 1 |?????? |???? 0?? (0)| 00:00:01 |???????????????????????????????
----------------------------------------------------------------------------------------------------???????????????????????????????
?????


Setting the _use_nosegment_indexes parameter to false to turn it off for our session.

08:20:00 orcl>? alter session set "_use_nosegment_indexes" =false;
Session altered.



Running the statement again.? The optimizer chose the standard index over the virtual index.It looks like Oracle will use the virtual index if you have the parameter set to TRUE.

08:20:31 orcl> select employee_id, a.department_id, b.department_name
08:20:41?? 2? from
08:20:41?? 3? hr.departments b,? hr.employees2 a
08:20:41?? 4? where
08:20:41?? 5? a.department_id = b.department_id
08:20:41?? 6? and employee_id = 203;
Execution Plan
----------------------------------------------------------?????????????????????????????????????????????????????????????????????????
Plan hash value: 4005350841??????????????????????????????????????????????????????????????????????????????????????????????????? ?????
???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
--------------------------------------------------------------------------------------------------------???????????? ???????????????
| Id? | Operation??????????????????? | Name??????????????????? | Rows? | Bytes | Cost (%CPU)| Time???? |???????????????????????????
--------------------------------------------------------------------------------------------------------?? ?????????????????????????
|?? 0 | SELECT STATEMENT???????????? |???????????????????????? |???? 1 |??? 25 |???? 5?? (0)| 00:00:01 |???????????????????????????
|?? 1 |? NESTED LOOPS??????????????? |???????????????????????? |???? 1 |??? 25 |???? 5?? (0)| 00:00:01 |???????????????????????????
|?? 2 |?? TABLE ACCESS BY INDEX ROWID| EMPLOYEES2????????????? |???? 1 |???? 9 |???? 4?? (0)| 00:00:01 |???????????????????????????
|*? 3 |??? INDEX RANGE SCAN????????? | EMP2_EMP_ID_NON_VIRTUAL |???? 1 |?????? |???? 3 ??(0)| 00:00:01 |???????????????????????????
|?? 4 |?? TABLE ACCESS BY INDEX ROWID| DEPARTMENTS???????????? |???? 1 |??? 16 |???? 1?? (0)| 00:00:01 |???????????????????????????
|*? 5 |??? INDEX UNIQUE SCAN???????? | DEPT_ID_PK????????????? |???? 1 |???? ??|???? 0?? (0)| 00:00:01 |???????????????????????????
--------------------------------------------------------------------------------------------------------???????????????????????????
?????


Rebuilding the virtual index.? Notice that the error message states that
you can not alter a ?fake? index.

08:21:03 orcl> alter index hr.emp2_emp_id_non_virtual rebuild;
Index altered.
08:21:39 orcl> alter index hr.emp2_emp_id_virtual rebuild;
alter index hr.emp2_emp_id_virtual rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index



Executing DBMS_STATS to gather statistics on both the virtual and standard index.?? I haverun tests with statistics and without and it does seem to affect virtual index access paths.
?
08:21:55 orcl>
08:21:57 orcl>
08:21:57 orcl> exec dbms_stats.gather_index_stats('HR', 'EMP2_EMP_ID_NON_VIRTUAL');
PL/SQL procedure successfully completed.
08:23:10 orcl> exec dbms_stats.gather_index_stats('HR', 'EMP2_EMP_ID_VIRTUAL');
PL/SQL procedure successfully completed.



Looking for information on indexes built on the EMPLOYEES2 table.? Oracle returns a row for the standard index but not the virtual index.

08:20:31 orcl> select index_name, last_analyzed from dba_indexes where
? 2* table_name = 'EMPLOYEES2'
INDEX_NAME???????????????????? LAST_ANAL???????????????????????????????????????????????????????????????????????????????????????????
------------------------------ ---------????????????????????? ??????????????????????????????????????????????????????????????????????
EMP2_EMP_ID_NON_VIRTUAL??????? 31-MAY-07???????????????????????????????????????????????????????????????????????????????????????????



Determining f we can find the virtual index in DBA_SEGMENTS.? No success.

08:26:09 orcl> select segment_name, segment_type from dba_segments where segment_name like 'EMP2%';
SEGMENT_NAME???????? SEGMENT_TYPE??????????????????????????????????????????????????????????????????????????????????????????????? ???
-------------------- ------------------????????????????????????????????????????????????????????????????????????????????????????????
EMP2_EMP_ID_NON_VIRT INDEX???????????????????????????????????????????????????????????????????????????????????????????? ?????????????
UAL????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???


Looking for the the virtual index in DBA_OBJECTS.? Finally, we find some sort of evidence that the
virtual index exists in the database!
??????????????????????????????????????????????????????????????????????????????????????????? ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
08:30:21 orcl> col object_name for a30
08:30:29 orcl> r
? 1? select object_name, object_type, created, status, temporary
? 2* from dba_objects where object_name like 'EMP2%'
OBJECT_NAME??????????????????? OBJECT_TYPE???????? CREATED?? STATUS? T??????????????????????????????? ??????????????????????????????
------------------------------ ------------------- --------- ------- -?????????????????????????????????????????????????????????????
EMP2_EMP_ID_NON_VIRTUAL??????? INDEX?????????????? 31-MAY-07 VALID?? N????????????????????? ????????????????????????????????????????
EMP2_EMP_ID_VIRTUAL??????????? INDEX?????????????? 31-MAY-07 VALID?? N?????????????????????????????????????????????????????????????
??????????????????????????????????????????????????????????????????????????????????
08:31:01 orcl> spool off

Verifying Physical Standby

Verifying physical standby:

-- on Standby --
Alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
--

select database_role, open_mode, switchover_status from v$database ;


Detailed Approach to check physical standby issues:

Steps:
1. Physical standby database archivelogs
Count the existing archived logs in the standby database:
select count(*) from v$archived_log;
Then archive the current log on the primary database:
alter system archive log current;
Then, count the archived logs on the standby database again. It should have been increased by one.
2. Level of synchronization
To see if there is a problem with the archiving destinations (such as that destionation that transmits logs), quere v$archive_dest_status on primary and standby.
set lines 120
select dest_id, status, error from v$archive_dest_status;
select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status where archived_thread# <> 0;
3. Show received logs
On a physical standby database, the received archived redo logs can be displayed like this:
select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;
4. Show applied archived redo log
select thread#, sequence#, first_change#, next_change# from v$log_history;
5. Show messages
Use v$dataguard_status to display messages. dest_id refers to what is configured with log_archive_dest_n.
select message from v$dataguard_status where dest_id = 2;
The query was executed on the primary database, and it shows that the destination 2 cannot deliver its logs.
ARCH: Error 12535 Creating archive log file to 'to_standby' ARCH: Error 12535 Creating archive log file to 'to_standby' ARCH: Error 12535 Creating archive log file to 'to_standby'
Broken network connections:
If the network connection is broken, the standby database writes RFS: Possible network disconnect with primary database. The primary
database writes: Network asynch I/O wait error 3114 log 3 service 'to_standby'.

6. Archived logs that are not transmitted
The following query assumes that the archives are locally stored on destination 1 and sent to the remote server on destionation 2.
set lines 120
select substr(local.name,1,50) "Archive Name", case when remote.sequence# is null then 'NOT TRANSMITTED' else 'transmitted' end, local.sequence#, local.thread#from (select * from v$archived_log where dest_id = 1) local left join (select * from v$archived_log where dest_id = 2) remote on local.sequence# = remote.sequence# and local.thread# = remote.thread# order by local.sequence#;

6. Is standby database performing managed recovery
select process, status from v$managed_standby;
If there is a MRP or a MRP0 process, then the database is performing managed recovery.

7. Monitoring the recovery process
select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

Steps to rename a RAC database

Steps to Rename RAC Database
Assumption:
Two Node RAC; Database name orcl and Instance orcl1 and orcl2 running on HostA and HostB
Name to be changed: Databse - Prod and instances prod1 and prod2
Steps:
a. From the instance orcl1, Backup the control file to trace. (Alter database backup controlfile to trace; )
b. Create pfile from the spfile (Create pfile=<’path/filename’> from spfile; )
c. Check the online redo-log files name, path and size on each nodes.
d. Take the complete backup of the database (To be towards safer side ).
———————————————————————————————
1. ) Shutdown the database using srvctl
$ srvctl stop database -d orcl
2. ) On one of the nodes, say on HostA,
Edit the pfile ( Created earlier), and change the following parameters.
Cluster_database=False
db_name=prod
Also, change all the occurances of orcl to prod.., Change the path of the Control_files..etc., as required.
Save the file as $ORACLE_HOME/dbs/initprod1.ora
3. ) Startup the database to nomount state by using the pfile.
4. ) Edit the Control file script , which should look as below. Make sure the script contains online redo-logs of instance orcl1 only. Run this script from SQL prompt in nomount state.
SQL> CREATE CONTROLFILE set DATABASE “PROD“ RESETLOGS NOARCHIVELOGMAXLOGFILES 192MAXLOGMEMBERS 3MAXDATAFILES 1024MAXINSTANCES 32MAXLOGHISTORY 292LOGFILEGROUP 1 ‘/u03/oradata/redo01.log’ SIZE 50M,GROUP 2 ‘/u03/oradata/redo02.log’ SIZE 50MDATAFILE‘/u03/oradata/system01.dbf’,‘/u03/oradata/undotbs01.dbf’,‘/u03/oradata/sysaux01.dbf’,‘/u03/oradata/users01.dbf’,‘/u03/oradata/example01.dbf’,‘/u03/oradata/undotbs02.dbf’CHARACTER SET WE8ISO8859P1;
ALTER DATABASE ADD LOGFILE THREAD 2GROUP 3 ‘/u03/oradata/redo03.log’ SIZE 50M REUSE,GROUP 4 ‘/u03/oradata/redo04.log’ SIZE 50M REUSE;
ALTER DATABASE ENABLE PUBLIC THREAD 2;
ALTER DATABASE OPEN RESETLOGS;
5. ) Change the init.ora parameter Cluster_database=False back to Cluster_database=True.
6.) Create spfile from pfile and place it in the shared location as it was earlier.
SQL> Create spfile=’/spfileprod.ora’ from pfile;
Shutdown immediate and start the instance with spfile
7. ) Start the second instance on on NodeB with the new spfile. (From sql prompt)
8. ) Remove the ORCL instance from CRS.
srvctl remove database -d orcl
9. ) Register PROD isntance with the CRS.
srvctl add database -d prod -o $ORACLE_HOMEsrvctl add instance -d prod -i prod1 -n NodeAsrvctl add instance -d prod -i prod2 -n NodeB
10. ) Change the instance names in the tnsnames.ora and listener.ora files.

Online Redefinition

online redefinition:

-- Table_name: DPO
-- Step 1. Backup DDL and confirm online_redef.-- Extract table DDL and save it as a backup.-- Confirm that the table is a candidate for online reorganization.

exec dbms_redefinition.can_redef_table('CW','DPO',DBMS_REDEFINITION.CONS_USE_PK);

-- Step 2. Build Interim table and make sure to "enable Row Movement". (add “_2” to the table_name)

CREATE TABLE CW.DPO_2 ( CWORDERID VARCHAR2(16 BYTE), ORDER_NUMBER VARCHAR2(30 BYTE), ORDER_VERSION VARCHAR2(2 BYTE), ORDER_EXPEDITE VARCHAR2(1 BYTE), ORDER_TYPE_SERVICE VARCHAR2(4 BYTE), RELATEDORDERTYPE VARCHAR2(4 BYTE), BILL_NAME VARCHAR2(25 BYTE), BILL_SECONDARY_NAME VARCHAR2(25 BYTE), ORDER_CREATION_DATE DATE, QCCREFLOWCOUNT VARCHAR2(3 BYTE) ) TABLESPACE OMS_DATA PCTFREE 40 PARTITION BY RANGE (ORDER_CREATION_DATE) ( PARTITION P_DPO_AUG_2007 VALUES LESS THAN (to_date('01-SEP-2007','DD-MON-YYYY')) TABLESPACE OMSE_AUG_2007_DATA, PARTITION P_DPO_SEP_2007 VALUES LESS THAN (to_date('01-OCT-2007','DD-MON-YYYY')) TABLESPACE OMSE_SEP_2007_DATA, PARTITION P_DPO_NOV_2008 VALUES LESS THAN (to_date('01-DEC-2008','DD-MON-YYYY')) TABLESPACE OMSE_NOV_2008_DATA, PARTITION P_DPO_DEC_2008 VALUES LESS THAN (to_date('01-JAN-2009','DD-MON-YYYY')) TABLESPACE OMSE_DEC_2008_DATA, PARTITION P_OVERFLOW VALUES LESS THAN (MAXVALUE) TABLESPACE OMSE_OVERFLOW_DATA ) NOCOMPRESS NOCACHE NOPARALLEL MONITORING ENABLE ROW MOVEMENT;

-- Step 3. Start Redefinition

begin dbms_redefinition.start_redef_table('CW','DPO','DPO_2'); end; /

-- Step 4. Automatically rebuild all table dependencies (indexes, triggers and so forth)

exec dbms_redefinition.copy_table_dependents('CW','DPO','DPO_2', TRUE, TRUE, TRUE, FALSE );
-- Step 5. Sync Interim table

exec dbms_redefinition.sync_interim_table('CW','DPO','DPO_2');

-- Step 6. Complete the online re-organization and make sure the grants are given to users/Roles.

begin dbms_redefinition.finish_redef_table('CW','DPO','DPO_2'); end; /

GRANT SELECT ON CW.DPO TO E073813; GRANT SELECT ON CW.DPO TO OMSE_RO; GRANT SELECT ON CW.DPO TO OMSE_EVM_READ;
-- Step 7. Drop the interim table

drop table CW.DPO_2;

Moving datafiles between ASM Diskgroups - Version 10g.

RMAN is the prefered tool to move datafiles from one Diskgroup to another, or, to move files from traditional mount points(filesystem) to ASM diskgroups. Alternatively, DBMS_FILE_TRANSFER can also be used for this purpose.

RMAN is critical to Automatic Storage Management and is responsible for tracking the ASM filenames and for deleting obsolete ASM files. Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM files.

Lets take 2 different scenarioes, 1. move a datafile from one diskgroup to another and second one is to move the datafile from filesystem mount point to ASM diskgroup.

Note: The steps provided here assume that the database is open and in Archivelog mode and not for system or sysaux datafiles.
For SYSTEM and SYSAUX datafiles, please see my next post.

Steps:

1) Identify the data file to be moved.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES:

FILE_NAME
-------------------
+DATA/orcl/datafile/sysaux01.dbf
+DATA/orcl/datafile/undotbs01.dbf
+DATA/orcl/datafile/system01.dbf
/oradb/u0001/orcl/test_tbsp.dbf <=== Move to DATA Diskgroup.
+REDO/orcl/datafile/users.256.565313879 <=== Move to DATA Diskgroup.

2) Identify the diskgroup on to which the file has to be moved.

SQL> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;

GROUP_NUMBER NAME
------------ ---------
1 DATA
2 REDO

3) Take the datafiles offline.

SQL> ALTER DATABASE DATAFILE '+REDO/orcl/datafile/users.256.565313879' OFFLINE;
SQL> ALTER DATABASE DATAFILE '/oradb/u0001/orcl/test_tbsp.dbf' OFFLINE;

4) Now Copy the files to ASM Diskgroup DATA using RMAN.

$ rman target /

connected to target database: ORCL (DBID=1089529226)

RMAN> COPY DATAFILE '+REDO/orcl/datafile/users.256.565313879' TO '+DATA';

Starting backup at 03-AUG-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+REDO/orcl/datafile/users.256.565313879
output filename=+DATA/orcl/datafile/users.259.565359071 tag=TAG20050803T121109 recid=2 stamp=565359071
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-05

RMAN> COPY DATAFILE '/oradb/u0001/orcl/test_tbsp.dbf' TO '+DATA';

Starting backup at 03-AUG-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/oradb/u0001/orcl/test_tbsp.dbf
output filename=+DATA/orcl/datafile/test_tbsp.260.675369081 tag=TAG20050803T121109 recid=2 stamp=675369081
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-05

5) Rename the file to point to new location.

RMAN> run
{
set newname for datafile '+REDO/orcl/datafile/users.256.565313879' to '+DATA/orcl/datafile/users.259.565359071' ;
set newname for datafile '/oradb/u0001/orcl/test_tbsp.dbf' to '+DATA/orcl/datafile/test_tbsp.260.675369081' ;
switch datafile all;
}

OR use alter database and then switch datafile.

SQL> alter database rename file '+REDO/orcl/datafile/users.256.565313879' to '+DATA/orcl/datafile/users.259.565359071' ;
SQL> alter database rename file '/oradb/u0001/orcl/test_tbsp.dbf' to '+DATA/orcl/datafile/test_tbsp.260.675369081' ;


6) If needed, recover the files.

SQL> RECOVER DATAFILE '+DATA/orcl/datafile/users.259.565359071' ;
SQL> RECOVER DATAFILE '+DATA/orcl/datafile/test_tbsp.260.675369081' ;

7) Bring the files online.

SQL> ALTER DATABASE DATAFILE '+DATA/orcl/datafile/users.259.565359071' ONLINE;
SQL> ALTER DATABASE DATAFILE '+DATA/orcl/datafile/test_tbsp.260.675369081' ONLINE;

8) Verify the newlocation of the files.

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;

FILE_NAME
-------------------
+DATA/orcl/datafile/users.259.565359071
+DATA/orcl/datafile/sysaux01.dbf
+DATA/orcl/datafile/undotbs01.dbf
+DATA/orcl/datafile/system01.dbf
+DATA/orcl/datafile/test_tbsp.260.675369081'

9) Delete the file from its original location

ASM:SQL> ALTER DISKGROUP REDO DROP FILE '+REDO/orcl/datafile/users.256.565313879 ' ;

------------------------------------------------------------------------------------------------

For System and Sysaux an approach similar to the one given below can be used.

A. Create a Copy of datafile in target Diskgroup:

RMAN> backup as copy tablespace system format '+DATA';
RMAN> backup as copy tablespace sysaux format '+DATA';


B. Then shutdown the database on all nodes and restart one instance to a mounted state

RMAN> shutdown immediate;
RMAN> startup mount;

C. switch the datafiles to the copy

RMAN> switch tablespace system to copy;
RMAN> switch tablespace sysaux to copy;

3. Recover the changes made to these tablespaces;

RMAN> recover database;

-------------------------------------------------------------------------------------------------

Moving/Multiplexing control files when ASM is involved.

Assume there is only one controlfile: +DATA_01/db_name/controlfile/current.256.636907509, and you want to move and multiplex this to other diskgroups (DATA_01, DATA-03 and DATA_04), then an approach similar to one given below can be used.


First Backup spfile or create a pfile and do a show parameters control_file on sqlplus.

1) shutdown database on all nodes; ensure no instance is mounting the database.

2) startup nomount one instance

3) alter system set control_files=
'+DATA_02/db_name/controlfile/control01.ctl',
'+DATA_03/db_name/controlfile/control02.ctl',
'+DATA_04/db_name/controlfile/control03.ctl' scope=spfile sid='*';

4) rman target /

5) restore controlfile from '+DATA_01/db_name/controlfile/current.256.636907509';

OR

restore controlfile to '+DATA_02/db_name/controlfile/control01.ctl' from '+DATA_01/db_name/controlfile/current.256.636907509';

Repeat for other controlfiles

restore controlfile to '+DATA_03/db_name/controlfile/control02.ctl' from '+DATA_01/db_name/controlfile/current.256.636907509';
restore controlfile to '+DATA_04/db_name/controlfile/control03.ctl' from '+DATA_01/db_name/controlfile/current.256.636907509';

6) shutdown (immediate)

7) srvctl start database -d