From Primary:
SQL> select protection_mode,
2 protection_level,
3 database_role,
4 switchover_status
5 from v$database;
PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY TO STANDBY
SQL> set pages 999
SQL> select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message
2 from v$dataguard_status;
TO_CHAR(TIMESTAMP,'YYYY-MON-DDHH24:MI:SS')||''||MESSAGE
--------------------------------------------------------------------------------
2011-JAN-10 08:43:34 ARC0: Archival started
2011-JAN-10 08:43:34 ARC1: Archival started
2011-JAN-10 08:43:34 ARC2: Archival started
2011-JAN-10 08:43:34 ARC1: Becoming the 'no FAL' ARCH
2011-JAN-10 08:43:34 ARC1: Becoming the 'no SRL' ARCH
2011-JAN-10 08:43:34 ARC2: Becoming the heartbeat ARCH
2011-JAN-10 08:43:35 Error 12514 received logging on to the standby
2011-JAN-10 08:43:35 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.
2011-JAN-10 08:43:35 ARC1: Beginning to archive thread 1 sequence 58 (305558-3
26454)
2011-JAN-10 08:43:35 ARC1: Completed archiving thread 1 sequence 58 (305558-32
6454)
2011-JAN-10 08:43:35 ARC3: Archival started
2011-JAN-10 08:43:37 Error 12514 received logging on to the standby
2011-JAN-10 08:43:37 FAL[server, ARC2]: Error 12514 creating remote archivelog
file 'drorcl'
2011-JAN-10 08:43:38 ARC3: Beginning to archive thread 1 sequence 59 (326454-3
26515)
2011-JAN-10 08:43:38 ARC3: Completed archiving thread 1 sequence 59 (326454-32
6515)
2011-JAN-10 08:46:52 ARC2: Standby redo logfile selected for thread 1 sequence
58 for destination LOG_ARCHIVE_DEST_2
2011-JAN-10 08:46:52 ARC0: Beginning to archive thread 1 sequence 60 (326515-3
26771)
2011-JAN-10 08:46:52 ARC0: Completed archiving thread 1 sequence 60 (326515-32
6771)
2011-JAN-10 08:46:52 LNS: Standby redo logfile selected for thread 1 sequence
61 for destination LOG_ARCHIVE_DEST_2
2011-JAN-10 08:46:52 LNS: Beginning to archive log 1 thread 1 sequence 61
2011-JAN-10 08:46:53 ARC0: Standby redo logfile selected for thread 1 sequence
60 for destination LOG_ARCHIVE_DEST_2
2011-JAN-10 10:20:03 LNS: Attempting destination LOG_ARCHIVE_DEST_2 network re
connect (3135)
2011-JAN-10 10:20:03 LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect aba
ndoned
2011-JAN-10 10:20:03 Error 3135 for archive log file 1 to 'drorcl'
2011-JAN-10 10:20:03 LNS: Failed to archive log 1 thread 1 sequence 61 (3135)
2011-JAN-10 10:25:22 Error 1031 received logging on to the standby
2011-JAN-10 10:25:22 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.
2011-JAN-10 10:26:41 Error 1031 received logging on to the standby
2011-JAN-10 10:26:41 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.
2011-JAN-10 10:28:02 Error 1031 received logging on to the standby
2011-JAN-10 10:28:02 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.
2011-JAN-10 10:29:21 Error 1031 received logging on to the standby
2011-JAN-10 10:29:21 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.
2011-JAN-10 10:30:40 Error 12514 received logging on to the standby
2011-JAN-10 10:30:40 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.
2011-JAN-10 10:31:59 Error 12514 received logging on to the standby
2011-JAN-10 10:31:59 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.
2011-JAN-10 10:33:18 Error 12514 received logging on to the standby
2011-JAN-10 10:33:18 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.
2011-JAN-10 10:34:37 Error 12528 received logging on to the standby
2011-JAN-10 10:34:37 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12528.
2011-JAN-10 10:34:37 ARC3: Beginning to archive thread 1 sequence 61 (326771-3
33704)
2011-JAN-10 10:34:38 ARC3: Completed archiving thread 1 sequence 61 (326771-33
3704)
2011-JAN-10 10:34:38 LNS: Standby redo logfile selected for thread 1 sequence
62 for destination LOG_ARCHIVE_DEST_2
2011-JAN-10 10:34:38 LNS: Beginning to archive log 2 thread 1 sequence 62
2011-JAN-10 10:34:38 ARC0: Standby redo logfile selected for thread 1 sequence
61 for destination LOG_ARCHIVE_DEST_2
46 rows selected.
From standby database:
SQL> select protection_mode,
2 protection_level,
3 database_role,
4 switchover_status
5 from v$database;
PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> select process,
2 status,
3 thread#,
4 sequence#,
5 block#,
6 blocks
7 from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 61 10240 1111
RFS IDLE 0 0 0 0
RFS IDLE 1 62 1057 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 62 1057 102400
8 rows selected.
SQL> select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message from v$dataguard_status;
TO_CHAR(TIMESTAMP,'YYYY-MON-DDHH24:MI:SS')||''||MESSAGE
--------------------------------------------------------------------------------
2011-JAN-10 10:34:36 ARC0: Archival started
2011-JAN-10 10:34:37 ARC1: Archival started
2011-JAN-10 10:34:37 ARC2: Archival started
2011-JAN-10 10:34:37 ARC1: Becoming the 'no FAL' ARCH
2011-JAN-10 10:34:37 ARC2: Becoming the heartbeat ARCH
2011-JAN-10 10:34:37 ARC2: Becoming the active heartbeat ARCH
2011-JAN-10 10:34:38 Primary database is in MAXIMUM PERFORMANCE mode
2011-JAN-10 10:34:38 RFS[1]: Assigned to RFS process 8979
2011-JAN-10 10:34:38 RFS[2]: Assigned to RFS process 8981
2011-JAN-10 10:34:38 ARC3: Archival started
2011-JAN-10 10:34:38 ARC3: Beginning to archive thread 1 sequence 61 (326771-3
33704)
2011-JAN-10 10:34:39 ARC3: Completed archiving thread 1 sequence 61 (0-0)
2011-JAN-10 10:34:51 Attempt to start background Managed Standby Recovery proc
ess
2011-JAN-10 10:34:51 MRP0: Background Managed Standby Recovery process started
2011-JAN-10 10:34:57 Managed Standby Recovery not using Real Time Apply
2011-JAN-10 10:34:57 Media Recovery Log /u01/app/oracle/fast_recovery_area/DRO
RCL/archivelog/2011_01_10/o1_mf_1_61_6lnw1yrp_.arc
2011-JAN-10 10:34:57 Media Recovery Waiting for thread 1 sequence 62 (in trans
it)
2011-JAN-10 10:35:06 MRP0: Background Media Recovery cancelled with status 160
37
2011-JAN-10 10:35:06 MRP0: Background Media Recovery process shutdown
2011-JAN-10 10:35:06 Managed Standby Recovery Canceled
2011-JAN-10 10:35:12 Attempt to start background Managed Standby Recovery proc
ess
2011-JAN-10 10:35:12 MRP0: Background Managed Standby Recovery process started
2011-JAN-10 10:35:18 Managed Standby Recovery starting Real Time Apply
2011-JAN-10 10:35:18 Media Recovery Waiting for thread 1 sequence 62 (in trans
it)
24 rows selected.
Wednesday, January 12, 2011
Tuesday, December 21, 2010
How to disable flush of ASH data to AWR?
MMON process will periodically flush ASH data into AWR tables.
Oracle introduced WF enqueue which is used to serialize the flushing of snapshots.
If for any reason ( space issue, bugs, hanging etc..) you need to disable flushing the run time statistics for
particular table than following procedure needs to be done.
First, locate the exact AWR Table Info (KEW layer):
SQL> select table_id_kewrtb, table_name_kewrtb from x$kewrtb order by 1
TABLE_ID_KEWRTB TABLE_NAME_KEWRTB
————— —————————————————————-
0 WRM$_DATABASE_INSTANCE
1 WRM$_SNAPSHOT
2 WRM$_BASELINE
3 WRM$_WR_CONTROL
—-
TABLE_ID_KEWRTB TABLE_NAME_KEWRTB
————— —————————————————————-
99 WRH$_RSRC_PLAN
100 WRM$_BASELINE_DETAILS
101 WRM$_BASELINE_TEMPLATE
102 WRH$_CLUSTER_INTERCON
103 WRH$_MEM_DYNAMIC_COMP
104 WRH$_IC_CLIENT_STATS
105 WRH$_IC_DEVICE_STATS
106 WRH$_INTERCONNECT_PINGS
107 rows selected.
1st option :
SQL> alter system set “_awr_disabled_flush_tables”=’’;
e.g.
alter system set “_awr_disabled_flush_tables”=’WRH$_INTERCONNECT_PINGS,WRH$_RSRC_PLAN’;
System altered.
2nd option:
SQL> alter session set events ‘immediate trace name awr_flush_table_off level 106′;
SQL> alter session set events ‘immediate trace name awr_flush_table_off level 99′
If you decide to turn on flushing statistics than
SQL> alter session set events ‘immediate trace name awr_flush_table_on level 106′;
SQL> alter session set events ‘immediate trace name awr_flush_table_on level 99′;
Oracle introduced WF enqueue which is used to serialize the flushing of snapshots.
If for any reason ( space issue, bugs, hanging etc..) you need to disable flushing the run time statistics for
particular table than following procedure needs to be done.
First, locate the exact AWR Table Info (KEW layer):
SQL> select table_id_kewrtb, table_name_kewrtb from x$kewrtb order by 1
TABLE_ID_KEWRTB TABLE_NAME_KEWRTB
————— —————————————————————-
0 WRM$_DATABASE_INSTANCE
1 WRM$_SNAPSHOT
2 WRM$_BASELINE
3 WRM$_WR_CONTROL
—-
TABLE_ID_KEWRTB TABLE_NAME_KEWRTB
————— —————————————————————-
99 WRH$_RSRC_PLAN
100 WRM$_BASELINE_DETAILS
101 WRM$_BASELINE_TEMPLATE
102 WRH$_CLUSTER_INTERCON
103 WRH$_MEM_DYNAMIC_COMP
104 WRH$_IC_CLIENT_STATS
105 WRH$_IC_DEVICE_STATS
106 WRH$_INTERCONNECT_PINGS
107 rows selected.
1st option :
SQL> alter system set “_awr_disabled_flush_tables”=’
e.g.
alter system set “_awr_disabled_flush_tables”=’WRH$_INTERCONNECT_PINGS,WRH$_RSRC_PLAN’;
System altered.
2nd option:
SQL> alter session set events ‘immediate trace name awr_flush_table_off level 106′;
SQL> alter session set events ‘immediate trace name awr_flush_table_off level 99′
If you decide to turn on flushing statistics than
SQL> alter session set events ‘immediate trace name awr_flush_table_on level 106′;
SQL> alter session set events ‘immediate trace name awr_flush_table_on level 99′;
Network Time Protocol ( NTP ) & Clusterware diagnostic script
One of the prerequisites to successfully install Oracle version 11.2 is to set Network Time Protocol ( NTP )
( file /etc/sysconfig/ntpd )
with -x flag which prevents time from adjusting backward.
This comes very crucial in debugging Oracle Clusterware.NTP will synchronize clocks among all nodes which will make correct analysis of trace files based on time stamps .
Oracle is providing diagnostic collection script diagcollection.pl to collect important log files.
Script is located under $/bin/ e.g. /u01/app/11.2.0/grid/bin
It will generate four tar.gz files in local directory which will have following information:
traces,logs and cores for CRS home
ocrcheck , ocrdump
CRS core files
OS logs
After you are done you can clean them with same script.Just run diagcollection.pl -clean option.
Script must be run as root .
( file /etc/sysconfig/ntpd )
with -x flag which prevents time from adjusting backward.
This comes very crucial in debugging Oracle Clusterware.NTP will synchronize clocks among all nodes which will make correct analysis of trace files based on time stamps .
Oracle is providing diagnostic collection script diagcollection.pl to collect important log files.
Script is located under $
It will generate four tar.gz files in local directory which will have following information:
traces,logs and cores for CRS home
ocrcheck , ocrdump
CRS core files
OS logs
After you are done you can clean them with same script.Just run diagcollection.pl -clean option.
Script must be run as root .
Add Unique key in a table that contains duplicate row
Requirement : A table contains some duplicate data. Now we want to add a unique constraint that skip existing duplicate values but check newly inserted duplicate values.
SQL> create table t2 (id number(10), t varchar2(20));
Table created.
SQL> insert into t2 values (1,'A');
1 row created.
SQL> insert into t2 values (1,'A');
1 row created.
SQL> insert into t2 values (1,'A');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE;
alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE
*
ERROR at line 1:
ORA-02299: cannot validate (HASAN.UK_T2) - duplicate keys found
SQL> select * from t2;
ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A
So normal method does not work !
Case 1: New Table That means initially the table does not have any data
SQL> create table t3 (id number(10), t varchar2(20));
Table created.
SQL> alter table t3 add constraint gpu unique (id) deferrable initially deferred;
Table altered.
SQL> alter table t3 disable constraint gpu;
Table altered.
SQL> insert into t3 values(1,'A');
1 row created.
SQL> insert into t3 values(1,'A');
1 row created.
SQL> insert into t3 values(1,'A');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL>
SQL> select * from t3;
ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A
SQL> alter table t3 enable novalidate constraint gpu;
Table altered.
SQL> insert into t3 values(2,'A');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t3 values(2,'A');
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.GPU) violated
SQL> alter table t3 modify constraint gpu INITIALLY IMMEDIATE;
Table altered.
SQL> insert into t3 values(1,'A');
insert into t3 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.GPU) violated
Case 2: Existing Table that contains duplicate data
SQL> create table t2 (id number(1),a varchar2(10));
Table created.
SQL> insert into t2 values(1,'A');
1 row created.
SQL> insert into t2 values(1,'A');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t2 add constraint uk_t2 unique(id) DEFERRABLE INITIALLY DEFERRED disable;
Table altered.
SQL> alter table t2 enable novalidate constraint uk_t2;
Table altered.
SQL> insert into t2 values(1,'A');
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.UK_T2) violated
SQL> alter table t2 modify constraint uk_t2 INITIALLY IMMEDIATE;
Table altered.
SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated
OR
SQL> alter table t2 add constraint uk_t2 unique(id) disable;
Table altered.
SQL> alter table t2 enable novalidate constraint uk_t2;
Table altered.
SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated
SQL> create table t2 (id number(10), t varchar2(20));
Table created.
SQL> insert into t2 values (1,'A');
1 row created.
SQL> insert into t2 values (1,'A');
1 row created.
SQL> insert into t2 values (1,'A');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE;
alter table t2 add constraint uk_t2 unique(id) ENABLE NOVALIDATE
*
ERROR at line 1:
ORA-02299: cannot validate (HASAN.UK_T2) - duplicate keys found
SQL> select * from t2;
ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A
So normal method does not work !
Case 1: New Table That means initially the table does not have any data
SQL> create table t3 (id number(10), t varchar2(20));
Table created.
SQL> alter table t3 add constraint gpu unique (id) deferrable initially deferred;
Table altered.
SQL> alter table t3 disable constraint gpu;
Table altered.
SQL> insert into t3 values(1,'A');
1 row created.
SQL> insert into t3 values(1,'A');
1 row created.
SQL> insert into t3 values(1,'A');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL>
SQL> select * from t3;
ID T
---------- ------------------------------------------------------------
1 A
1 A
1 A
SQL> alter table t3 enable novalidate constraint gpu;
Table altered.
SQL> insert into t3 values(2,'A');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t3 values(2,'A');
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.GPU) violated
SQL> alter table t3 modify constraint gpu INITIALLY IMMEDIATE;
Table altered.
SQL> insert into t3 values(1,'A');
insert into t3 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.GPU) violated
Case 2: Existing Table that contains duplicate data
SQL> create table t2 (id number(1),a varchar2(10));
Table created.
SQL> insert into t2 values(1,'A');
1 row created.
SQL> insert into t2 values(1,'A');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t2 add constraint uk_t2 unique(id) DEFERRABLE INITIALLY DEFERRED disable;
Table altered.
SQL> alter table t2 enable novalidate constraint uk_t2;
Table altered.
SQL> insert into t2 values(1,'A');
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HASAN.UK_T2) violated
SQL> alter table t2 modify constraint uk_t2 INITIALLY IMMEDIATE;
Table altered.
SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated
OR
SQL> alter table t2 add constraint uk_t2 unique(id) disable;
Table altered.
SQL> alter table t2 enable novalidate constraint uk_t2;
Table altered.
SQL> insert into t2 values(1,'A');
insert into t2 values(1,'A')
*
ERROR at line 1:
ORA-00001: unique constraint (HASAN.UK_T2) violated
Subscribe to:
Posts (Atom)