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

How to take RMAN Backup from BCV copy of the Database:

How to take RMAN Backup from BCV copy of the Database:


1.
a.
DB Release info of target DB, catalog DB, BCV copy DB:
a) 10.2.0.4 - ASM storage
b) 10.2.0.4
c) 10.2.0.4 - ASM storage

b..
OS Release info of Primary and BCV:
a) Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
b) Red Hat Enterprise Linux AS release 4 (Nahant Update 4)


2. Please verify fhrba_seq column values, should match between Primary and BCV copy:

-- On Primary
set linesize 120;
select hxfil FILE#,fhsta STAT,fhscn SCN, fhthr thr, fhrba_Seq SEQUENCE,fhtnm TABLESPACE
from x$kcvfh
order by 1;

-- On BCV
set linesize 120;
select hxfil FILE#,fhsta STAT,fhscn SCN, fhthr thr, fhrba_Seq SEQUENCE,fhtnm TABLESPACE
from x$kcvfh
order by 1;

Note: bug 6004226 may show different fhrba_seq column values between Primary and BCV copy.
Workaround is in step 4.

3.
Error:

RMAN> resync catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 12/10/2008 07:45:32
ORA-00236: snapshot operation disallowed: mounted control file is a backup

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> run {
2> startup mount;
3> allocate channel edw_backup_sbt1 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
4> allocate channel edw_backup_sbt2 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
5> allocate channel edw_backup_sbt3 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
6> allocate channel edw_backup_sbt4 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
7> backup full filesperset = 10 as BACKUPSET tag '%d_full_backup_%TAG' database;
8> release channel edw_backup_sbt1;
9> release channel edw_backup_sbt2;
10>release channel edw_backup_sbt3;
11>release channel edw_backup_sbt4;
12>}

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 4194304000 bytes

Fixed Size 2089272 bytes
Variable Size 3976203976 bytes
Database Buffers 201326592 bytes
Redo Buffers 14684160 bytes

allocated channel: edw_backup_sbt1
channel edw_backup_sbt1: sid=4392 devtype=SBT_TAPE
channel edw_backup_sbt1: Veritas NetBackup for Oracle - Release 6.5 (2007111606)

allocated channel: edw_backup_sbt2
channel edw_backup_sbt2: sid=4386 devtype=SBT_TAPE
channel edw_backup_sbt2: Veritas NetBackup for Oracle - Release 6.5
(2007111606)

allocated channel: edw_backup_sbt3
channel edw_backup_sbt3: sid=4384 devtype=SBT_TAPE
channel edw_backup_sbt3: Veritas NetBackup for Oracle - Release 6.5 (2007111606)

allocated channel: edw_backup_sbt4
channel edw_backup_sbt4: sid=4382 devtype=SBT_TAPE
channel edw_backup_sbt4: Veritas NetBackup for Oracle - Release 6.5 (2007111606)

Starting backup at 10-DEC-08
released channel: edw_backup_sbt1
released channel: edw_backup_sbt2
released channel: edw_backup_sbt3
released channel: edw_backup_sbt4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/10/2008 07:47:10
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high recid


4.
Following is the complete procedure to take RMAN backup of BCV copy:

STEPS :
=======

1. Primary database has to be registered in the recovery catalog.


2. Verify the current RMAN Configuration.

show all;

* autobackup controlfile OFF


3. Take the BCV cut after the production database was shutdown with "shutdown immediate;".


4. On the staging server using BCV cut, Mount Database :

$ sqlplus / as sysdba

SQL> startup mount;


5. Check the controfile_type: (Controlfile type shoud be CURRENT ):

SQL> select controlfile_type from v$database;

CONTROL
---------
CURRENT


6. Connect to RMAN in nocatalog, take a backup of the controlfile and then restore it, you will have a backup controlfile then:

a.
$ rman target /

RMAN> run {
allocate channel disk1 device type disk;
backup current controlfile FORMAT '/u00/oradata/EDW/controlfile/current_controlfile1.ctl'; }
RMAN> exit;

b.
$ rman target /

RMAN> run {
allocate channel disk1 device type disk;
restore controlfile to '/u00/oradata/EDW/controlfile/backup_controlfile1.ctl' from '/u00/oradata/EDW/controlfile/current_controlfile1.ctl'; }
RMAN> exit;


7. Edit the init.ora file to point to the new controlfile (/u00/oradata/EDW/controlfile/backup_controlfile1.ctl)


8. Shutdown database:

SQL> shutdown immediate;


9. mount database:

SQL> startup mount;


10. Check the controfile_type : (Controlfile type shoud be BACKUP) :

SQL> select controlfile_type from v$database;

CONTROL
---------
BACKUP


11. Start RMAN session connected to this database mounted and the recovery catalog :

$ rman target / catalog rman/****@prman.world


12. Execute the backup.

RMAN> run {
2> startup mount;
3> allocate channel edw_backup_sbt1 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
4> allocate channel edw_backup_sbt2 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
5> allocate channel edw_backup_sbt3 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
6> allocate channel edw_backup_sbt4 type sbt format '%d_full_backup_%U' parms 'ENV=(NB_ORA_POLICY=BCV_ASM,NB_ORA_SCHED=FULL_BCV)' maxpiecesize 8 G;
7> backup full filesperset = 10 as BACKUPSET tag '%d_full_backup_%TAG' database;
8> release channel edw_backup_sbt1;
9> release channel edw_backup_sbt2;
10>release channel edw_backup_sbt3;
11>release channel edw_backup_sbt4;
12>}

Imp Note: If you want to open the database, edit the init.ora file to point to the original controlfile.

How to move a datafile from one diskgroup to another diskgroup in ASM using RMAN:

How to move a datafile from one diskgroup to another diskgroup in ASM using RMAN:

In this test, I will be moving the datafile from A_T1_DATA_01 to B_T1_DATA_01 diskgroup:

a. select the datafile to move:
sqlplus / as sysdba on DB:
col file_name for a50
select file_id, file_name from dba_data_files where file_id=7;


b. select the diskgroup to move:
sqlplus / as sysdba on ASM:
select GROUP_NUMBER, NAME, TOTAL_MB, FREE_MB, STATE from v$asm_diskgroup;


c. On DB:
. oraenv ORADB

rman target /

RMAN> report schema;
RMAN> sql 'alter database datafile 7 offline';
RMAN> backup as copy datafile 7 format '+B_T1_DATA_01';
RMAN> switch datafile 7 to COPY;
RMAN> recover datafile 7;
RMAN> sql 'alter database datafile 7 online';
RMAN> report schema;


d. Check and see the datafile new location:
sqlplus / as sysdba on DB:
col file_name for a50
select file_id, file_name from dba_data_files where file_id=7;


e. Remove the datafile from OLD disk group:
$ asmcmd
ASMCMD> cd A_T1_DATA_01/ORADB/datafile
ASMCMD> rm FLOW_1.776.644706747

Oracle masala at work - 2:

Oracle masala at work - 2:
1. How to make Oracle data Case-Insensitive search:
2. How to setup OEM Command Line Interface:
3. How to Update RMAN Recovery Catalog with Older Archive Log's that are on the Tape:
4. How to run the Oracle RAC Add Node procedure in Silent mode:

1. How to make Oracle data Case-Insensitive search:
a. Database Level Trigger to make Oracle data Case-Insensitive:

connect sys@ORADB as sysdba

--Schema Level
CREATE OR REPLACE TRIGGER sys.make_case_insensitive_AL
AFTER LOGON ON DATABASE
DECLARE
BEGIN
IF (user = 'TEST') THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC'; -- 10gR2
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END IF;
END;
/

-- DB Level
CREATE OR REPLACE TRIGGER sys.make_case_insensitive_AL
AFTER LOGON ON DATABASE
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP = LINGUISTIC'; -- 10gR2
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_CI';
END;
/

b. Verifying Index usage:

connect test@ORADB
create table emp(ename varchar2(50));
insert into emp values('ViJay');
insert into emp values('VIJAY');
insert into emp values('Vijay');
commit;

create index idx1_emp on emp(ename);

exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', degree=> 2, cascade => TRUE);

SET AUTOTRACE ON;
select * from emp where ename = 'vijay'; -- No Index use
select /*+ index(emp) */ * from emp where ename = 'vijay'; -- No Index use
select * from emp where ename LIKE 'Vi%'; -- This uses IDX1 Index FULL scan

-- To make use of the Index, we need to create below Index on ename column:
create index idx2_emp on emp(NLSSORT(ename,'NLS_SORT=BINARY_CI'));

exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'EMP', degree=> 2, cascade => TRUE);

select * from emp where ename = 'vijay'; -- This uses IDX2 Index
select * from emp where ename LIKE 'Vi%'; -- This uses IDX1 Index FULL scan (NOT IDX2 Index)


2. How to setup OEM Command Line Interface:
a.
export JAVA_HOME=/u00/app/oracle/product/OEM/oms10g/jdk
export PATH=$PATH:$JAVA_HOME/bin

cd /u00/app/oracle/product/OEM/oms10g/sysman/jlib

java -jar emclikit.jar client -install_dir=/export/home/oracle/OEMCL

b.
emcli setup -url="http://atlp158:4890/em" -username=sysman -password=oracle -dir=/export/home/oracle/OEMCL -trustall -novalidate

emcli sync
emcli help
emcli help update_password
emcli get_targets


3. How to Update RMAN Recovery Catalog with Older Archive Log's that are on the Tape:
a.Identify the Backup Pieces:

rman target / catalog=rman/rman1@prman

spool log to 'x.log';
list backup of archivelog all;
spool log off;

SELECT start_time, handle
FROM v$backup_piece
WHERE TRUNC (start_time) = '01-Feb-2009'
ORDER BY start_time;

b. Attach the missing Backup Pieces to RMAN Recovery Catalog:
rman target / catalog=rman/rman1@prman

-- Syntax for one Backup Pieces
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'aik6806g_1_1';

-- Syntax for more that one Backup Pieces
CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'aik6806g_1_1,ahk6806g_1_1,ajk6806g_1_1';


4. How to run the Oracle RAC Add Node procedure in Silent mode:
a. Run below command from the existing node:
atlp260 > /DBHome/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={atlp259,atlp258,atlp257}" -logLevel trace -debug

Once all required DB binaries are copied from atlp260 to atlp257, atlp258 and atlp259 nodes, execute "root.sh" file:
/u00/app/oracle/product/10.2.0/DB/root.sh on atlp257, atlp258 and atlp259 nodes.

b.
Just to be sure, execute "crs_start -all" and check crs_stat -t for all the resources availability.