No Segment Vs Invisible Vs Unusable Indexes in Oracle 11gR2:

No Segment Vs Invisible Vs Unusable Indexes in Oracle 11gR2:

No Segment/Virtual Indexes (8i and up):

Virtual indexes allow us to simulate the existence of an index and test its impact without actually building the actual index.
Only sessions marked for Virtual Index usage will be affected by their existence. Their creation does not affect new sessions.
Virtual indexes will be used only when the initialization parameter "_use_nosegment_indexes" is set to TRUE.
The Rule based optimizer does not recognize Virtual Indexes but the CBO does recognize them.
Dictionary view DBA_SEGMENTS will not show entries for Virtual Indexes. [DBA|ALL|USER]_OBJECTS view will have an entry.
They are permanent and continue to exist unless dropped. Make sure to drop virtual indexes after analysis and tuning is completed.
Statistics can be gathered on virtual indexes in the same way as regular indexes.
Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.

To detect a virtual index in the database run the following SQL (these indexes don't have any columns in dba_ind_columns):

SELECT index_owner, index_name
FROM dba_ind_columns
WHERE index_name NOT LIKE 'BIN$%'
MINUS
SELECT owner, index_name
FROM dba_indexes;



Invisible Indexes (11gR1 and up):

Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.

ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
ALTER SYSTEM SET optimizer_use_invisible_indexes=TRUE;


Using invisible indexes, you can do the following:
- Test the optimizer behave on the application before dropping an index.
- Use the index for certain operations or modules of an application without affecting the overall performance of the application.
- It's also useful for some DELETE operations in Database Machine (Oracle EXADATA).


An invisible index is maintained during DML statements.
Statistics can be gathered on an invisible indexes.
The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.
Using ALTER INDEX we can make the index INVISIBLE or VISIBLE.
We can REBUILD an invisible index.

INDEX hit will not work with an invisible index.
/*+ opt_param('optimizer_use_invisible_indexes','TRUE') */ -- we CAN'T use opt_param hint
We CAN'T modify index partition to Invisible, it's at Index level.



Unusable Indexes (11gR2):

Zero Sized Unusable Indexes:
In Oracle 11gR2 when the index or index partition marked as unusable, oracle automatically drop any index segment space.
This means we can release OLD(Not Active) index partition space and we can keep NEW(Active) partition indexes.

ALTER INDEX MODIFY PARTITION UNSABLE;

Once we make an index unusable, we will not see the segment in [DBA|ALL|USER]_SEGMENTS view, but we will have entry in [DBA|ALL|USER]_IND_PARTITIONS view.
The column SEGMENT_CREATED(column value: YES/NO) shows whether a segment exists for that partition.
unlike previous oracle releases(< 11gR2), we can query the unusable partition data, but it uses full table scan instead of an index scan, because the corresponding index partition is unusable and cannot be used by the optimizer.
When you query against NEW(Active) partition, it uses the corresponding index scan.
If a table is truncated, the unusable index partition will become usable again and Oracle Database will re-create the segment.

OEM Grid Control Maintenance Tasks - Weekly/Monthly:

OEM Grid Control Maintenance Tasks - Weekly/Monthly:

OMS Monthly Maintenance Tasks:

1. OEM database partition Maintenance:

a. Shut down all OMSs:
$OMS_HOME/bin/emctl stop oms

b. Connect as SYSMAN to the OEM database and run following two procedures:
SQL>connect sysman/xxxx
SQL>set timing on;
SQL>exec emd_maintenance.analyze_end_schema('SYSMAN');
SQL>commit;
SQL>exec emd_maintenance.partition_maintenance;
SQL>commit;

If you are in a scenario where you hit Bug 5357916 and can't apply the patch or upgrade to a version where the patch is available you need to do the following:

SQL>connect / as sysdba
SQL>alter system set job_queue_processes = 0;
SQL>connect sysman/xxxx
SQL>set timing on;
SQL>exec emd_maintenance.remove_em_dbms_jobs;
SQL>exec emd_maintenance.partition_maintenance;

SQL>@/sysman/admin/emdrep/sql/core/latest/admin/admin_recompile_invalid.sql
SQL>alter system set job_queue_processes = 10;
SQL>exec emd_maintenance.submit_em_dbms_jobs;
SQL>commit;

Note: The partition maintenance will be performed automatically if you have the following configuration:
Grid Control 10gR3 (10.2.0.3) or later with the repository installed in a 10.2.0.2 database or later.

c. Confirm the Partition maintenance was successful:
select count(1) from sysman.mgmt_metrics_raw where collection_timestamp < sysdate -9;

d. Restart all OMSs:
$OMS_HOME/bin/emctl start oms

2. Rebuild/Shrink tables and indexes as required:


OMS Weekly Maintenance Tasks:

1. Check and Clear OMS and OEM database system errors.

a. OMS:
Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Errors' Tab

b. OEM Database alertlog errors:
Goto: $ORACLE_BASE/POEM/bdump/alert_POEM[12].log

2. Fix target metric collection errors.

a. Cluster agent:
$AGENT_HOME/bin/agentca -d -c catlmsxt261 -- Discover
$AGENT_HOME/bin/agentca -f -c catlmsxt261 -- Reconfigure
$AGENT_HOME/bin/emctl clearstate agent
$AGENT_HOME/bin/emctl status agent

b. Standalone agent:
$AGENT_HOME/bin/agentca -f
$AGENT_HOME/bin/emctl clearstate agent
$AGENT_HOME/bin/emctl status agent

3. Start or remove targets in DOWN status and also remove all the Duplicate Targets.

4. Force delete all the "Deleted Targets"
Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Overview' Tab >> Deleted Targets list
(or DELETE FROM SYSMAN.MGMT_TARGETS_DELETE; COMMIT ;)

5. Clear Critical and Warning alerts:
Goto: Alerts tab on the OEM main page.

6. Analyze Metric Rollup Tables as required:

MGMT_METRICS_RAW
MGMT_METRICS_1DAY
MGMT_METRICS_1HOUR

exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_RAW', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1HOUR', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1DAY', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);


Reference:
Doc ID: 456101.1
Doc ID: 370695.1

How to automate partition (Range - Seconds to Years) creation using Oracle Interval Partitioning in 11g:

How to automate partition (Range - Seconds to Years) creation using Oracle Interval Partitioning in 11g:

1. Create a composite Interval-List/Interval-Range partition table:

drop table x;
create table x (create_date date, site_id number, all_data varchar2(100))
PARTITION BY RANGE (create_date)
INTERVAL (NUMTODSINTERVAL(1,'HOUR'))
SUBPARTITION BY LIST (site_id)
(
PARTITION part_01 values LESS THAN (TO_DATE('18-SEP-2009 16:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_01_01 VALUES (01),
SUBPARTITION part_01_126 VALUES (126),
SUBPARTITION part_01_132 VALUES (132),
SUBPARTITION part_01_135 VALUES (135)
),
PARTITION part_02 values LESS THAN (TO_DATE('18-SEP-2009 17:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_02_01 VALUES (01),
SUBPARTITION part_02_126 VALUES (126),
SUBPARTITION part_02_132 VALUES (132),
SUBPARTITION part_02_135 VALUES (135)
),
PARTITION part_03 values LESS THAN (TO_DATE('18-SEP-2009 18:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_03_01 VALUES (01),
SUBPARTITION part_03_126 VALUES (126),
SUBPARTITION part_03_132 VALUES (132),
SUBPARTITION part_03_135 VALUES (135)
),
PARTITION part_04 values LESS THAN (TO_DATE('18-SEP-2009 19:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_04_01 VALUES (01),
SUBPARTITION part_04_126 VALUES (126),
SUBPARTITION part_04_132 VALUES (132),
SUBPARTITION part_04_135 VALUES (135)
),
PARTITION part_05 values LESS THAN (TO_DATE('18-SEP-2009 20:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_05_01 VALUES (01),
SUBPARTITION part_05_126 VALUES (126),
SUBPARTITION part_05_132 VALUES (132),
SUBPARTITION part_05_135 VALUES (135)
),
PARTITION part_06 values LESS THAN (TO_DATE('18-SEP-2009 21:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_06_01 VALUES (01),
SUBPARTITION part_06_126 VALUES (126),
SUBPARTITION part_06_132 VALUES (132),
SUBPARTITION part_06_135 VALUES (135)
),
PARTITION part_07 values LESS THAN (TO_DATE('18-SEP-2009 22:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_07_01 VALUES (01),
SUBPARTITION part_07_126 VALUES (126),
SUBPARTITION part_07_132 VALUES (132),
SUBPARTITION part_07_135 VALUES (135)
),
PARTITION part_08 values LESS THAN (TO_DATE('18-SEP-2009 23:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_08_01 VALUES (01),
SUBPARTITION part_08_126 VALUES (126),
SUBPARTITION part_08_132 VALUES (132),
SUBPARTITION part_08_135 VALUES (135)
)
);


2. Create bitmap LOCAL indexes:

create bitmap index idx1_x on x(create_date) local;
create bitmap index idx2_x on x(site_id) local;


3. Insert and Check the data in each partition:

truncate table x;
insert into x values(TO_DATE('18-SEP-2009 15:30:00','DD-MON-YYYY HH24:MI:SS'),01,'18/SEP/09 of 15''Th HR - SITE 01');
insert into x values(TO_DATE('18-SEP-2009 16:30:00','DD-MON-YYYY HH24:MI:SS'),126,'18/SEP/09 of 16''Th HR - SITE 126');
insert into x values(TO_DATE('18-SEP-2009 17:30:00','DD-MON-YYYY HH24:MI:SS'),132,'18/SEP/09 of 17''Th HR - SITE 132');
insert into x values(TO_DATE('18-SEP-2009 18:30:00','DD-MON-YYYY HH24:MI:SS'),135,'18/SEP/09 of 18''Th HR - SITE 125');
insert into x values(TO_DATE('18-SEP-2009 19:30:00','DD-MON-YYYY HH24:MI:SS'),01,'18/SEP/09 of 19''Th HR - SITE 01');
insert into x values(TO_DATE('18-SEP-2009 20:30:00','DD-MON-YYYY HH24:MI:SS'),126,'18/SEP/09 of 20''Th HR - SITE 126');
insert into x values(TO_DATE('18-SEP-2009 21:30:00','DD-MON-YYYY HH24:MI:SS'),132,'18/SEP/09 of 21''Th HR - SITE 132');
insert into x values(TO_DATE('18-SEP-2009 22:30:00','DD-MON-YYYY HH24:MI:SS'),135,'18/SEP/09 of 22''Th HR - SITE 135');
commit;

set linesize 200;
col all_data for a50;

SELECT * FROM x PARTITION (PART_01);
SELECT * FROM x PARTITION (PART_02);
SELECT * FROM x PARTITION (PART_03);
SELECT * FROM x PARTITION (PART_04);
SELECT * FROM x PARTITION (PART_05);
SELECT * FROM x PARTITION (PART_06);
SELECT * FROM x PARTITION (PART_07);
SELECT * FROM x PARTITION (PART_08);


4. Check and record the HIGH VALUE of the partition table:

col table_name for a10;
col partition_name for a15;
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

8 rows selected.


5. Check and record the HIGH VALUE of the Subpartition table:

col subpartition_name for a20;
col high_value for a10;
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
X PART_01 PART_01_135 135
X PART_01 PART_01_132 132
X PART_01 PART_01_126 126
X PART_01 PART_01_01 01
X PART_02 PART_02_135 135
X PART_02 PART_02_132 132
X PART_02 PART_02_126 126
X PART_02 PART_02_01 01
X PART_03 PART_03_135 135
X PART_03 PART_03_132 132
X PART_03 PART_03_126 126
X PART_03 PART_03_01 01
X PART_04 PART_04_135 135
X PART_04 PART_04_132 132
X PART_04 PART_04_126 126
X PART_04 PART_04_01 01
X PART_05 PART_05_135 135
X PART_05 PART_05_132 132
X PART_05 PART_05_126 126
X PART_05 PART_05_01 01
X PART_06 PART_06_135 135
X PART_06 PART_06_132 132
X PART_06 PART_06_126 126
X PART_06 PART_06_01 01
X PART_07 PART_07_135 135
X PART_07 PART_07_132 132
X PART_07 PART_07_126 126
X PART_07 PART_07_01 01
X PART_08 PART_08_135 135
X PART_08 PART_08_132 132
X PART_08 PART_08_126 126
X PART_08 PART_08_01 01

32 rows selected.


6. Insert new record (Next day 15'th Hour) and validate the partition creation:

insert into x values(TO_DATE('19-SEP-2009 15:30:00','DD-MON-YYYY HH24:MI:SS'),01,'19/SEP/09 of 15''Th HR - SITE 01');
commit;

col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..

X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT <------- New Partition

33 rows selected.

col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition

9 rows selected.


7. Insert new record(Next day 17'th Hour) and validate the partition creation:

insert into x values(TO_DATE('19-SEP-2009 17:30:00','DD-MON-YYYY HH24:MI:SS'),132,'19/SEP/09 of 17''Th HR - SITE 132');
commit;

col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT
X SYS_P44 SYS_SUBP43 DEFAULT <------- New Partition

34 rows selected.

col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P44 TO_DATE(' 2009-09-19 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition

10 rows selected.


8. Insert new record (Next day 16'th Hour) and validate the partition creation:
# This record data is one hour less than the previous record in Step 7.
# No problem with creating the partition even with jumbled hourly data!!!!

insert into x values(TO_DATE('19-SEP-2009 16:30:00','DD-MON-YYYY HH24:MI:SS'),126,'19/SEP/09 of 16''Th HR - SITE 126');
commit;

col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT
X SYS_P44 SYS_SUBP43 DEFAULT
X SYS_P46 SYS_SUBP45 DEFAULT <------- New Partition

35 rows selected.

col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P44 TO_DATE(' 2009-09-19 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P46 TO_DATE(' 2009-09-19 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition

11 rows selected.


Side Note:
If you want to create different intervals, here are the available options:

NUMTODSINTERVAL - An Interval Day to Second:

'DAY' - INTERVAL (NUMTODSINTERVAL(1,'DAY')) or INTERVAL (NUMTODSINTERVAL(7,'DAY'))
'HOUR' - INTERVAL (NUMTODSINTERVAL(1,'HOUR')) or INTERVAL (NUMTODSINTERVAL(8,'HOUR'))
'MINUTE' - INTERVAL (NUMTODSINTERVAL(1,'MINUTE')) or INTERVAL (NUMTODSINTERVAL(15,'MINUTE'))
'SECOND' - INTERVAL (NUMTODSINTERVAL(1,'SECOND')) or INTERVAL (NUMTODSINTERVAL(30,'SECOND'))


NUMTOYMINTERVAL - An Interval Year to Month:

'YEAR' - INTERVAL (NUMTOYMINTERVAL(1,'YEAR')) or INTERVAL (NUMTOYMINTERVAL(3,'YEAR'))
'MONTH' - INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) or INTERVAL (NUMTOYMINTERVAL(4,'MONTH'))

How to Restore Database BCV backup copy to ASM storage:

How to Restore Database BCV backup copy to ASM storage:

How big is the database: 2TB
How often we take BCV copy: Daily
How often we delete Archive log's: Every 30 mns.
How much redo, does this DB generate per Hour: 70GB/24= ~3GB
Is this RAC database: Yes.


1.
Stop all instances of database:
srvctl stop database -d ORADB

2.
Check the services are offline:
crs_stat -t

3.
Dismount diskgroups on all instances:

alter diskgroup ORADB_T1_BACKUP_01 dismount;
alter diskgroup ORADB_T1_DATA_01 dismount;
alter diskgroup ORADB_T1_DATA_02 dismount;
alter diskgroup ORADB_T1_DATA_03 dismount;
alter diskgroup ORADB_T1_DATA_04 dismount;
alter diskgroup ORADB_T1_DATA_05 dismount;
alter diskgroup ORADB_T1_REDO_01 dismount;

4.
Have SysAdmin restore the database from BCV:

Is there any difference in restoring BCV copy to file system Vs ASM?
There is technically no difference in restoring BCV copy to file system Vs ASM.

Can ASM disk names be different?
No, should be same between source and target.

5.
Mount back diskgroups on all instances:

alter diskgroup ORADB_T1_BACKUP_01 mount;
alter diskgroup ORADB_T1_DATA_01 mount;
alter diskgroup ORADB_T1_DATA_02 mount;
alter diskgroup ORADB_T1_DATA_03 mount;
alter diskgroup ORADB_T1_DATA_04 mount;
alter diskgroup ORADB_T1_DATA_05 mount;
alter diskgroup ORADB_T1_REDO_01 mount;

6.
Startup and Recover the database: -- Source to DB instance

SQL> startup;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 419430968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 14692352 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'+ORADB_T1_DATA_01/oradb/datafile/system.256.667735945'


SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

7.
Restart all services/instances:
crs_start -all

8.
Check the services are online:
crs_stat -t

How to swinging the RAC(ASM) database from one cluster to another cluster:

How to swinging the RAC(ASM) database from one cluster to another cluster:


1. Un-mount all the diskgroups from the source RAC cluster on all the instances.

ALTER DISKGROUP ALL DISMOUNT; or diskgroups specific to the DB

2. Shutdown all the instances(database).

srvctl stop database -d DB_NAME

3. Copy DB init.ora file to all the RAC nodes.

4. Swinging the disks from one cluster to other cluster. (Storage and Sys Admin Task).

High Level: After ASM(un-mount) and DB are stopped, SAN team un-zone the storage from source RAC cluster and zone it to destination RAC cluster.
And perform the ASM scan disk (/etc/init.d/oracleasm scandisks) to see all the disks in destination ASM instance.

5. Mount all the diskgroups on the destination RAC cluster on all the instances.

ALTER DISKGROUP ALL MOUNT;

6. Register all the instances and database.

srvctl add instance -d DB_NAME -i INSTANCE1 -n NODE1
srvctl add instance -d DB_NAME -i INSTANCE2 -n NODE2
srvctl add instance -d DB_NAME -i INSTANCE3 -n NODE3
srvctl add instance -d DB_NAME -i INSTANCE4 -n NODE4

srvctl add database -d DB_NAME -o $ORACLE_HOME
srvctl modify instance -d DB_NAME -i INSTANCE_NAME -s ASM_INSTANCE_NAME

7. Startup all the instances(database).

srvctl start database -d DB_NAME
or crs_start -all

8. Create all the services using dbca. Incase of any issues with dbca use below command.

srvctl add service -d DB_NAME -s SERVICE_NAME -r INSTANCE1,INSTANCE2,INSTANCE3 -a INSTANCE4 -P BASIC

-r for preferred nodes
-a for available nodes
-P for TAF Policy (NONE, BASIC or PRECONNECT)

9. Update /etc/oratab, tnsnames.ora on all the servers and S drive.

10. Update all the new diskgroups in ASM init.ora file on the destination RAC cluster on all the nodes.

11. Cleanup the database and instances CRS resources from the source RAC cluster.

srvctl remove instance -d DB_NAME -i INSTANCE1
srvctl remove instance -d DB_NAME -i INSTANCE2
srvctl remove instance -d DB_NAME -i INSTANCE3
srvctl remove instance -d DB_NAME -i INSTANCE4

srvctl remove database -d DB_NAME

srvctl remove service -d DB_NAME -s SERVICE_NAME