11G externalized alert log table

Alert log can be monitored through SQL using "sys.x$dbgalertext".

SQL> desc sys.x$dbgalertext
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 ORIGINATING_TIMESTAMP                              TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                               TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID                                    VARCHAR2(64)
 COMPONENT_ID                                       VARCHAR2(64)
 HOST_ID                                            VARCHAR2(64)
 HOST_ADDRESS                                       VARCHAR2(46)
 MESSAGE_TYPE                                       NUMBER
 MESSAGE_LEVEL                                      NUMBER
 MESSAGE_ID                                         VARCHAR2(64)
 MESSAGE_GROUP                                      VARCHAR2(64)
 CLIENT_ID                                          VARCHAR2(64)
 MODULE_ID                                          VARCHAR2(64)
 PROCESS_ID                                         VARCHAR2(32)
 THREAD_ID                                          VARCHAR2(64)
 USER_ID                                            VARCHAR2(64)
 INSTANCE_ID                                        VARCHAR2(64)
 DETAILED_LOCATION                                  VARCHAR2(160)
 PROBLEM_KEY                                        VARCHAR2(64)
 UPSTREAM_COMP_ID                                   VARCHAR2(100)
 DOWNSTREAM_COMP_ID                                 VARCHAR2(100)
 EXECUTION_CONTEXT_ID                               VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE                         NUMBER
 ERROR_INSTANCE_ID                                  NUMBER
 ERROR_INSTANCE_SEQUENCE                            NUMBER
 VERSION                                            NUMBER
 MESSAGE_TEXT                                       VARCHAR2(2048)
 MESSAGE_ARGUMENTS                                  VARCHAR2(128)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(128)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(128)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER


SQL> col MESSAGE_TEXT for a120
SQL> select * from sys.x$dbgalertext where ORIGINATING_TIMESTAMP > sysdate -1/24
  2  /

no rows selected

SQL> create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M;
create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M
*
ERROR at line 1:
ORA-01119: error in creating database file '/root/not_able_to_create.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 13: Permission denied
Additional information: 1


SQL> select * from sys.x$dbgalertext where ORIGINATING_TIMESTAMP > sysdate -1/24;

ADDR               INDX    INST_ID
---------------- ---------- ----------
ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
NORMALIZED_TIMESTAMP
---------------------------------------------------------------------------
ORGANIZATION_ID
----------------------------------------------------------------
COMPONENT_ID
----------------------------------------------------------------
HOST_ID
----------------------------------------------------------------
HOST_ADDRESS                       MESSAGE_TYPE MESSAGE_LEVEL
---------------------------------------------- ------------ -------------
MESSAGE_ID
----------------------------------------------------------------
MESSAGE_GROUP
----------------------------------------------------------------
CLIENT_ID
----------------------------------------------------------------
MODULE_ID
----------------------------------------------------------------
PROCESS_ID
--------------------------------
THREAD_ID
----------------------------------------------------------------
USER_ID
----------------------------------------------------------------
INSTANCE_ID
----------------------------------------------------------------
DETAILED_LOCATION
--------------------------------------------------------------------------------
PROBLEM_KEY
----------------------------------------------------------------
UPSTREAM_COMP_ID
--------------------------------------------------------------------------------
DOWNSTREAM_COMP_ID
--------------------------------------------------------------------------------
EXECUTION_CONTEXT_ID
--------------------------------------------------------------------------------
EXECUTION_CONTEXT_SEQUENCE ERROR_INSTANCE_ID ERROR_INSTANCE_SEQUENCE    VERSION
-------------------------- ----------------- ----------------------- ----------
MESSAGE_TEXT
--------------------------------------------------------------------------------
MESSAGE_ARGUMENTS
--------------------------------------------------------------------------------
SUPPLEMENTAL_ATTRIBUTES
--------------------------------------------------------------------------------
SUPPLEMENTAL_DETAILS
--------------------------------------------------------------------------------
 PARTITION  RECORD_ID
---------- ----------
00007FAA553E5840       3110         1
06-MAY-12 11.42.14.357 PM +08:00

oracle
rdbms
ncsdb02.ncs.edu.sg
192.168.251.141                       5           16
opiexe:3025:4222364190
admin_ddl

sqlplus@ncsdb02.ncs.edu.sg (TNS V1-V3)
17422








             0           0               0          0
create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M



     1     3111

00007FAA553E5840       3111         1
06-MAY-12 11.42.14.384 PM +08:00

oracle
rdbms
ncsdb02.ncs.edu.sg
192.168.251.141                       5           16
opiexe:3087:2780954927
admin_ddl

sqlplus@ncsdb02.ncs.edu.sg (TNS V1-V3)
17422








             0           0               0          0
ORA-1119 signalled during: create tablespace tbl_failed datafile '/root/not_able
_to_create.dbf' size 10M...



     1     3112

Manual fix OGG Replicat Broken Example

-- insert into target first
SQL> insert into bbadmin.tbl_p values (1,'p');
SQL> commit;

-- insert into source later
SQL> insert into bbadmin.tbl_p values (1,'p');
SQL> commit;
SQL> insert into bbadmin.tbl_p values (2,'p');
SQL> commit;


File: $GGS_HOME/ggserr.log

2012-05-06 22:56:13  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  REPLICAT RBBP01 started.
2012-05-06 22:56:18  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT RBBP01 starting.
2012-05-06 23:14:44  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL .
2012-05-06 23:14:44  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Aborted grouped transaction on 'BBADMIN.TBL_P', Database error 1 (OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL ).
2012-05-06 23:14:44  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Repositioning to rba 11860 in seqno 13.
2012-05-06 23:14:44  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  SQL error 1 mapping BBADMIN.TBL_P to BBADMIN.TBL_P OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL .
2012-05-06 23:14:44  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Repositioning to rba 11860 in seqno 13.
2012-05-06 23:14:44  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Error mapping from BBADMIN.TBL_P to BBADMIN.TBL_P.
2012-05-06 23:14:44  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  PROCESS ABENDING.
2012-05-06 23:14:44  INFO    OGG-01237  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Trace file /u01/ggs/dirrpt/trace_RBBP01.trc closed.
2012-05-06 23:18:57  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  REPLICAT RBBP01 starting.
2012-05-06 23:18:57  INFO    OGG-03035  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Operating system character set identified as UTF-8. Locale: en_SG, LC_ALL:.
2012-05-06 23:18:57  INFO    OGG-01236  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Trace file /u01/ggs/dirrpt/trace_RBBP01.trc opened.
2012-05-06 23:18:57  INFO    OGG-01815  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Virtual Memory Facilities for: COM


File: $GGS_HOME/dirrpt/RBBP01.dsc

Current time: 2012-05-06 23:14:44
Discarded record from action ABEND on error 1

OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11860
                         error at seqno 13 rba 11860
Problem replicating BBADMIN.TBL_P to BBADMIN.TBL_P
Mapping problem with insert record (target format)...
*
PID = 1
NAME = p
*

Process Abending : 2012-05-06 23:14:44

Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 23:18:57

[oracle@ncsdb02 ggs]$ logdump

Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



Logdump 13 >open /u01/ggs/dirdat/ta000013
Current LogTrail is /u01/ggs/dirdat/ta000013
Logdump 14 >pos 11860
Reading forward from RBA 11860
Logdump 15 >n

2012/05/06 23:14:43.994.911 Insert               Len    18 RBA 11860
Name: BBADMIN.TBL_P
After  Image:                                             Partition 4   G  s  
 0000 0005 0000 0001 3100 0100 0500 0000 0170      | ........1........p 
  
Logdump 16 >n

2012/05/06 23:23:50.917.770 Insert               Len    18 RBA 11995
Name: BBADMIN.TBL_P
After  Image:                                             Partition 4   G  s  
 0000 0005 0000 0001 3200 0100 0500 0000 0170      | ........2........p 



GGSCI (ncsdb02.ncs.edu.sg) 1> info replicat r*

REPLICAT   RBBP01    Last Started 2012-05-06 23:23   Status ABENDED
Checkpoint Lag       00:09:14 (updated 00:03:34 ago)
Log Read Checkpoint  File /u01/ggs/dirdat/ta000013
                     2012-05-06 23:14:43.994911  RBA 11860


GGSCI (ncsdb02.ncs.edu.sg) 6> alter replicat RBBP01 extseqno 13, extrba 11995
REPLICAT altered.


GGSCI (ncsdb02.ncs.edu.sg) 8> start replicat RBBP01

Sending START request to MANAGER ...
REPLICAT RBBP01 starting

Simulate effects without "DEFERREFCONST"

To simulate the issue, make sure following DBOPTIONS not enabled on Replicat.

--DBOPTIONS DEFERREFCONST


DDL statement on Source Database (with DDL replication is on)
alter session set current_schema=bbadmin;
drop table tbl_p purge;
drop table tbl_c purge;
create table tbl_p (pid number,name varchar2(20));
alter table tbl_p add constraint tbl_p_pk primary key (pid);
create table tbl_c (cid number,pid number,name varchar2(20));
alter table tbl_c add constraint tbl_c_pk primary key (cid);
alter table tbl_c add constraint tbl_c_tbl_p_fk foreign key (pid) references tbl_p on delete cascade not deferrable;
create index tbl_c_n1 on tbl_c(pid);
insert into tbl_p values (1,'a');
insert into tbl_p values (2,'b');
insert into tbl_c values (100,1,'c');
insert into tbl_c values (200,2,'c');
commit;

delete from tbl_p where pid=2;


[oracle@ncsdb02 ggs]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Feb  1 2012 00:55:59

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



GGSCI (ncsdb02.ncs.edu.sg) 1> info replicat r*, detail

REPLICAT   RBBP01    Last Started 2012-05-06 22:38   Status ABENDED
Checkpoint Lag       00:10:35 (updated 00:02:52 ago)
Log Read Checkpoint  File /u01/ggs/dirdat/ta000013
                     2012-05-06 22:28:18.921409  RBA 11633

  Extract Source                          Begin             End            

  /u01/ggs/dirdat/ta000013                2012-05-06 22:27  2012-05-06 22:28
  /u01/ggs/dirdat/ta000013                2012-05-06 22:27  2012-05-06 22:28
  /u01/ggs/dirdat/ta000013                2012-05-06 22:27  2012-05-06 22:28
  /u01/ggs/dirdat/ta000013                2012-05-02 23:32  2012-05-06 22:27
  /u01/ggs/dirdat/ta000012                2012-05-02 23:20  2012-05-02 23:32
  /u01/ggs/dirdat/ta000011                2012-05-02 22:42  2012-05-02 23:20
  /u01/ggs/dirdat/ta000007                2012-05-01 21:45  2012-05-02 22:42
  /u01/ggs/dirdat/ta000006                2012-05-01 07:51  2012-05-01 21:45
  /u01/ggs/dirdat/ta000005                2012-05-01 07:46  2012-05-01 07:51
  /u01/ggs/dirdat/ta000005                2012-05-01 07:14  2012-05-01 07:46
  /u01/ggs/dirdat/ta000004                2012-04-30 22:39  2012-05-01 07:14
  /u01/ggs/dirdat/ta000003                2012-04-29 12:06  2012-04-30 22:39
  /u01/ggs/dirdat/ta000002                2012-04-28 18:17  2012-04-29 12:06
  /u01/ggs/dirdat/ta000000                * Initialized *   2012-04-28 18:17
  /u01/ggs/dirdat/ta000000                * Initialized *   First Record   


Current directory    /u01/ggs

Report file          /u01/ggs/dirrpt/RBBP01.rpt
Parameter file       /u01/ggs/dirprm/rbbp01.prm
Checkpoint file      /u01/ggs/dirchk/RBBP01.cpr
Checkpoint table     ggs_admin.ggschkpt
Process file         /u01/ggs/dirpcs/RBBP01.pcr
Stdout file          /u01/ggs/dirout/RBBP01.out
Error log            /u01/ggs/ggserr.log





File: $GGS_HOME/ggserr.log

2012-05-06 22:28:00  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  REPLICAT RBBP01 started.
2012-05-06 22:28:05  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  REPLICAT RBBP01 starting.
2012-05-06 22:28:19  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Aborted grouped transaction on 'BBADMIN.TBL_C', Database error 1403 (No data found).
2012-05-06 22:28:19  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Repositioning to rba 11633 in seqno 13.
2012-05-06 22:28:19  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  SQL error 1403 mapping BBADMIN.TBL_C to BBADMIN.TBL_C No data found.
2012-05-06 22:28:19  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Repositioning to rba 11633 in seqno 13.
2012-05-06 22:28:19  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Error mapping from BBADMIN.TBL_C to BBADMIN.TBL_C.
2012-05-06 22:28:19  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  PROCESS ABENDING.
2012-05-06 22:28:19  INFO    OGG-01237  Oracle GoldenGate Delivery for Oracle, rbbp01.prm:  Trace file /u01/ggs/dirrpt/trace_RBBP01.trc closed.

File: $GGS_HOME/dirrpt/RBBP01.dsc

Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 22:28:00

Current time: 2012-05-06 22:28:19
Discarded record from action ABEND on error 1403

No data found
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11633
                         error at seqno 13 rba 11757
Problem replicating BBADMIN.TBL_C to BBADMIN.TBL_C
Record not found
Mapping problem with delete record (target format)...
*
CID = 200
*

Continuing to discard records up to the last discarded record from action ABEND

Operation discarded from seqno 13 rba 11633
Aborted delete from BBADMIN.TBL_P to BBADMIN.TBL_P (target format)...
*
PID = 2
*
Process Abending : 2012-05-06 22:28:19

Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 22:33:54

Current time: 2012-05-06 22:33:54
Discarded record from action ABEND on error 1403

No data found
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11633
                         error at seqno 13 rba 11757
Problem replicating BBADMIN.TBL_C to BBADMIN.TBL_C
Record not found
Mapping problem with delete record (target format)...
*
CID = 200
*

Continuing to discard records up to the last discarded record from action ABEND

Operation discarded from seqno 13 rba 11633
Aborted delete from BBADMIN.TBL_P to BBADMIN.TBL_P (target format)...
*
PID = 2
*
Process Abending : 2012-05-06 22:33:54


[oracle@ncsdb02 ggs]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.



Logdump 1 >open /u01/ggs/dirdat/ta000013
Current LogTrail is /u01/ggs/dirdat/ta000013
Logdump 2 >pos 11633
Reading forward from RBA 11633
Logdump 3 >fileheader detail
Logdump 4 >ghdr on
Logdump 5 >detail on
Logdump 6 >usertoken detail
Logdump 7 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04) 
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42) 
RecLength  :     9  (x0009)   IO Time    : 2012/05/06 22:28:18.921.409  
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x00)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         58       AuditPos   : 33719824
Continued  :     N  (x00)     RecCount   :     1  (x01)

2012/05/06 22:28:18.921.409 Delete               Len     9 RBA 11633
Name: BBADMIN.TBL_P
Before Image:                                             Partition 4   G  b  
 0000 0005 0000 0001 32                            | ........2 
Column     0 (x0000), Len     5 (x0005) 
  
Logdump 8 >reclen 1000
Reclen set to 1000
Logdump 9 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04) 
UndoFlag   :     .  (x00)     BeforeAfter:     B  (x42) 
RecLength  :    11  (x000b)   IO Time    : 2012/05/06 22:28:18.921.409  
IOType     :     3  (x03)     OrigNode   :   255  (xff)
TransInd   :     .  (x02)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         58       AuditPos   : 33720668
Continued  :     N  (x00)     RecCount   :     1  (x01)

2012/05/06 22:28:18.921.409 Delete               Len    11 RBA 11757
Name: BBADMIN.TBL_C
Before Image:                                             Partition 4   G  e  
 0000 0007 0000 0003 3230 30                       | ........200 
Column     0 (x0000), Len     7 (x0007) 

How to achieve sub-second latency when your application workload is low

Add following parameter to your Extract process (Including the one for PUMP):  

FLUSHCSECS 10 

EOFDELAYCSECS 10 

And add following parameter to your Replicat process.

 EOFDELAYCSECS 10 

 Remark: Verified in OGG 11.2.

Raise ASM diskgroup compatibility using SQL

SQL> create diskgroup FRA external redundancy disk '/dev/sdc1';

Diskgroup created.

SQL> desc v$asm_diskgroup
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
NAME VARCHAR2(30)
SECTOR_SIZE NUMBER
BLOCK_SIZE NUMBER
ALLOCATION_UNIT_SIZE NUMBER
STATE VARCHAR2(11)
TYPE VARCHAR2(6)
TOTAL_MB NUMBER
FREE_MB NUMBER
HOT_USED_MB NUMBER
COLD_USED_MB NUMBER
REQUIRED_MIRROR_FREE_MB NUMBER
USABLE_FILE_MB NUMBER
OFFLINE_DISKS NUMBER
COMPATIBILITY VARCHAR2(60)
DATABASE_COMPATIBILITY VARCHAR2(60)
VOTING_FILES VARCHAR2(1)

SQL> select * from v$asm_diskgroup;

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
1 DATA 512 4096
1048576 MOUNTED EXTERN 20473 20414 0
59 0 20414 0
11.2.0.0.0
10.1.0.0.0 N

2 FRA 512 4096
1048576 MOUNTED EXTERN 10236 10186 0
50 0 10186 0
10.1.0.0.0
10.1.0.0.0 N

SQL> alter diskgroup data set attribute 'compatible.asm'='11.2.0.0.0';

Diskgroup altered.


SQL> alter diskgroup data set attribute 'compatible.rdbms'='11.2.0.0.0';

Diskgroup altered.

SQL> select * from v$asm_diskgroup;

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
1 DATA 512 4096
1048576 MOUNTED EXTERN 20473 20414 0
59 0 20414 0
11.2.0.0.0
11.2.0.0.0 N

2 FRA 512 4096
1048576 MOUNTED EXTERN 10236 10186 0
50 0 10186 0
10.1.0.0.0
10.1.0.0.0 N


SQL> alter diskgroup fra set attribute 'compatible.asm'='11.2';

Diskgroup altered.

SQL> alter diskgroup fra set attribute 'compatible.rdbms'='11.2';

Diskgroup altered.

SQL> select * from v$asm_diskgroup;

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
1 DATA 512 4096
1048576 MOUNTED EXTERN 20473 20414 0
59 0 20414 0
11.2.0.0.0
11.2.0.0.0 N

2 FRA 512 4096
1048576 MOUNTED EXTERN 10236 10184 0
52 0 10184 0
11.2.0.0.0
11.2.0.0.0 N

Create directory implictly grant permission to creator (owner is SYS)

[oracle@vmxdb05b ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 20:09:10 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> create user donghua2 identified by donghua2;

User created.

SQL> grant create session to donghua2;

Grant succeeded.

SQL> grant create any directory to donghua2;

Grant succeeded.


SQL> conn donghua2/donghua2
Connected.
SQL> create directory HOMEDIR as '/home/oracle';

Directory created.

SQL> exit


SQL> select * from all_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS SUBDIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep

SYS SS_OE_XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/

SYS HOMEDIR
/home/oracle


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS LOG_FILE_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/

SYS MEDIA_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/

SYS XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml


OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DATA_FILE_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

SYS DATA_PUMP_DIR
/u01/app/oracle/admin/orcl05b/dpdump/

SYS ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state


9 rows selected.


SQL> select * from all_tab_privs where grantee='DONGHUA2';

GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA2
SYS HOMEDIR
WRITE YES NO

SYS DONGHUA2
SYS HOMEDIR
READ YES NO

GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---

SYS DONGHUA2
SYS HOMEDIR
EXECUTE YES NO


[oracle@vmxdb05b ~]$ sqlplus donghua2/donghua2

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 20:17:44 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


SQL> set echo on
SQL> @utl_file.sql
SQL> DECLARE
2 V1 VARCHAR2(32767);
3 F1 UTL_FILE.FILE_TYPE;
4 BEGIN
5 F1 := UTL_FILE.FOPEN('HOMEDIR','MYFILE','W');
6 UTL_FILE.PUT_LINE(F1,'TEST LINE');
7 UTL_FILE.FCLOSE(F1);
8
9 F1 := UTL_FILE.FOPEN('HOMEDIR','MYFILE','R');
10 UTL_FILE.GET_LINE(F1,V1,32767);
11 UTL_FILE.FCLOSE(F1);
12 END;
13 /

PL/SQL procedure successfully completed.

SQL> ! cat ~/MYFILE
TEST LINE

SQL> connect donghua/donghua
Connected.
SQL> create directory homedir2 as '/home/oracle';

Directory created.

SQL> select * from all_tab_privs where grantee='DONGHUA';

GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA
SYS HOMEDIR2
WRITE YES NO

SYS DONGHUA
SYS HOMEDIR2
READ YES NO

GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---

SYS DONGHUA
SYS HOMEDIR2
EXECUTE YES NO

No error detected to change OS time for oracle DB running on 11.2

[oracle@vmxdb05b ~]$ vi trigger_changes.sql
[oracle@vmxdb05b ~]$ sqlplus donghua/donghua

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 12:42:07 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> truncate table t;

Table truncated.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@vmxdb05b ~]$ vi trigger_changes.sql
[oracle@vmxdb05b ~]$ sqlplus donghua/donghua

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 12:42:26 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time,rownum rn from v$archived_log order by sequence#)
3 where rn<11;

SEQUENCE# NEXT_TIME RN
---------- ------------------------------ ----------
3 25-nov-11 08.09.55 1
4 27-nov-11 17.25.37 2
5 27-nov-11 21.00.08 3
6 07-dec-11 22.05.11 4
7 11-dec-11 10.41.06 5
8 11-dec-11 11.11.00 6
9 08-jan-12 09.46.15 7
10 08-jan-12 09.57.38 8
11 08-jan-12 11.08.41 9
12 08-jan-12 11.09.56 10

10 rows selected.

SQL>

[grid@vmxdb05b ~]$ crsctl stop has

[root@vmxdb05b ~]# date
Sun Jan 8 12:45:02 SGT 2012
You have new mail in /var/spool/mail/root
[root@vmxdb05b ~]# date 01081446
Sun Jan 8 14:46:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 14:46:01 SGT 2012
[root@vmxdb05b ~]#


[grid@vmxdb05b ~]$ crsctl start has

SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1248897 24 08-JAN-12 02.48.55.368836 PM


SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc )
3 where rownum<11
4 /

SEQUENCE# NEXT_TIME
---------- ------------------------------
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42
12 08-jan-12 11.09.56
11 08-jan-12 11.08.41
10 08-jan-12 09.57.38

10 rows selected.

[grid@vmxdb05b ~]$ crsctl stop has


[root@vmxdb05b ~]# date
Sun Jan 8 14:59:13 SGT 2012
[root@vmxdb05b ~]# date 01081259
Sun Jan 8 12:59:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 12:59:02 SGT 2012
[root@vmxdb05b ~]#

[grid@vmxdb05b ~]$ crsctl start has

SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269374 25 08-JAN-12 01.04.57.367069 PM
1248897 24 08-JAN-12 02.48.55.368836 PM

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;

SEQUENCE# NEXT_TIME
---------- ------------------------------
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42
12 08-jan-12 11.09.56
11 08-jan-12 11.08.41

10 rows selected.

[root@vmxdb05b ~]# date
Sun Jan 8 13:06:06 SGT 2012
[root@vmxdb05b ~]# date 01091306
Mon Jan 9 13:06:00 SGT 2012
[root@vmxdb05b ~]# date
Mon Jan 9 13:06:01 SGT 2012


SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269605 25 08-JAN-12 01.04.57.367069 PM
1269605 26 09-JAN-12 01.06.31.790207 PM
1248897 24 08-JAN-12 02.48.55.368836 PM

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;

SEQUENCE# NEXT_TIME
---------- ------------------------------
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42

10 rows selected.

SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;

SEQUENCE# NEXT_TIME
---------- ------------------------------
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30

10 rows selected.

SQL>

[root@vmxdb05b ~]# date
Mon Jan 9 13:09:48 SGT 2012
[root@vmxdb05b ~]# date 01081310
Sun Jan 8 13:10:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 13:10:00 SGT 2012


SQL> @@trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
1270330 28 08-JAN-12 01.10.21.538665 PM

6 rows selected.

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;

SEQUENCE# NEXT_TIME
---------- ------------------------------
26 09-jan-12 13.07.04
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34

10 rows selected.

SQL>
SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select ora_rowscn,id,curr_time from t;

ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
1270344 28 08-JAN-12 01.10.21.538665 PM
1270344 29 08-JAN-12 01.10.24.453826 PM

7 rows selected.

SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;

SEQUENCE# NEXT_TIME
---------- ------------------------------
28 08-jan-12 13.10.21
27 08-jan-12 13.10.21
26 09-jan-12 13.07.04
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55

10 rows selected.

SQL>
SQL>

[oracle@vmxdb05b trace]$ cat orcl05b_vktm_9981.trc
Trace file /u01/app/oracle/diag/rdbms/orcl05b/orcl05b/trace/orcl05b_vktm_9981.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: vmxdb05b.lab.dbaglobe.com
Release: 2.6.32-71.29.1.el6.x86_64
Version: #1 SMP Mon Jun 27 19:49:27 BST 2011
Machine: x86_64
VM name: VMWare Version: 6
Instance name: orcl05b
Redo thread mounted by this instance: 0
Oracle process number: 4
Unix process pid: 9981, image: oracle@vmxdb05b.lab.dbaglobe.com (VKTM)


*** 2012-01-08 13:01:04.109
*** SESSION ID:(4.1) 2012-01-08 13:01:04.109
*** CLIENT ID:() 2012-01-08 13:01:04.109
*** SERVICE NAME:() 2012-01-08 13:01:04.109
*** MODULE NAME:() 2012-01-08 13:01:04.109
*** ACTION NAME:() 2012-01-08 13:01:04.109

kstmmainvktm: succeeded in setting elevated priority
highres_enabled

*** 2012-01-08 13:01:04.109
VKTM running at (1)millisec precision with DBRM quantum (100)ms
[Start] HighResTick = 1325998864109366
kstmrmtickcnt = 0 : ksudbrmseccnt[0] = 1325998864

*** 2012-01-09 13:06:00.000
kstmchkdrift (kstmhighrestimecntkeeper:highres): Time jumped forward by (86384713465)usec at (1326085560000441) whereas (1000000) is allowed
[End] HighResTick = 1326085758255964

*** 2012-01-09 13:09:18.257
kstmrmtickcnt = 5066 : ksudbrmseccnt[510] = 1326085758