Sunday, January 8, 2012

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