Today, a client came up with a question on whether or not password protected listener is safer than non-password protected listener in 10g.
To answer, In Oracle 10g, listener is secure by itself and there is no need to set a password for listener as in older version to protect listener.
By default, listener uses local OS authentication which means that only the user who owned listener can admin it. This feature is enabled by default.
If you run lsnrctl status in 10g, you should see any line like the following in output:
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production
Start Date
Uptime
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File
Listener Log File
In above example, If listener is started as Oracle user and user X attempts to admin it or Oracle user from a different node attempts to admin it, the following error will appear.
TNS-01190: The user is not authorized to execute the requested
On the other hand, if a password is set for 10g listener, all users who know the password can admin listener.For the password protected listener in 10g, the result of lsnrctl status would be something like this :
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Production
Start Date
Uptime
Trace Level off
Security ON: Password or Local OS Authentication
SNMP ON
Listener Parameter File
Listener Log File
To conclude, if only user who started listener is allowed to admin listener, you do not need to set password for 10g listener.Listener by itself is protected and the only user who can admin the listener is listener owner.To me, it seems to be more restricted.
However, if you want other users to admin listener, you still need to have password protected listener. All users who knows password can run admin command for listener.
To me, it seems less restricted.
TIP # 64 : How to determine bind variable value and type from trace file.
In TIP 48 , I explained on how to make more sense of trace which is generated by event 10046.
One of our reader asked a question on how to identify data type of bind variable, As the result I dedicated this post to answer.
For finding more information about bind variable, user should be able to locate BINDS keyword in trace file.
This part is something like this :
BINDS #2:
bind 0: dty=1 mxl=32(30) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0
bfp=030a79ec bln=32 avl=01 flg=05
value="A"
dty determines bind variable type and value shows bind variable value at the time of execution.
Different values can be assigned to dty which presents different data type.
Typical dty value is :
1 VARCHAR2 or NVARCHAR2
2 NUMBER
8 LONG
11 ROWID
12 DATE
23 RAW
24 LONG RAW
96 CHAR
112 CLOB or NCLOB
113 BLOB
114 BFILE
To demosnstrate, I ran some queries.
------ Query #1 : Number Bind variable
SQL> alter session set statistics_level=ALL;
Session altered.
SQL> alter session set max_dump_file_size=UNLIMITED;
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> variable b number;
SQL> declare
2 cnt number;
3 begin
4 :b:=1;
5 select count(*) into cnt from dba_objects where object_id=:b;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context off';
Session altered.
Checking trace file ......
PARSING IN CURSOR #2 len=52 dep=1 uid=0 oct=3 lid=0 tim=2797155817 hv=1220784193 ad='130a9d4c'
SELECT count(*) from dba_objects where object_id=:b1
END OF STMT
PARSE #2:c=15625,e=24270,p=0,cr=5,cu=0,mis=1,r=0,dep=1,og=0,tim=2797155809
BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=24 offset=0
bfp=030a7b2c bln=22 avl=02 flg=05
value=1
EXEC #2:c=0,e=2062,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2797159524
FETCH #2:c=0,e=37,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=2797159885
------ Query #2 : Varhchar2 Bind variable
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> variable b varchar2(30);
SQL> declare
2 cnt number;
3 begin
4 :b:='A';
5 select count(*) into cnt from dba_objects where object_name=:b;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context off';
Checking trace file .....
PARSING IN CURSOR #2 len=54 dep=1 uid=0 oct=3 lid=0 tim=3007778755 hv=2029951970 ad='12ff2c80'
SELECT count(*) from dba_objects where object_name=:b1
END OF STMT
PARSE #2:c=15625,e=12991,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=0,tim=3007778748
BINDS #2:
bind 0: dty=1 mxl=32(30) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0
bfp=030a79ec bln=32 avl=01 flg=05
value="A"
EXEC #2:c=0,e=2167,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3007782524
WAIT #2: nam='db file sequential read' ela= 9458 p1=1 p2=89 p3=1
WAIT #2: nam='db file sequential read' ela= 6606 p1=1 p2=26791 p3=1
One of our reader asked a question on how to identify data type of bind variable, As the result I dedicated this post to answer.
For finding more information about bind variable, user should be able to locate BINDS keyword in trace file.
This part is something like this :
BINDS #2:
bind 0: dty=1 mxl=32(30) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0
bfp=030a79ec bln=32 avl=01 flg=05
value="A"
dty determines bind variable type and value shows bind variable value at the time of execution.
Different values can be assigned to dty which presents different data type.
Typical dty value is :
1 VARCHAR2 or NVARCHAR2
2 NUMBER
8 LONG
11 ROWID
12 DATE
23 RAW
24 LONG RAW
96 CHAR
112 CLOB or NCLOB
113 BLOB
114 BFILE
To demosnstrate, I ran some queries.
------ Query #1 : Number Bind variable
SQL> alter session set statistics_level=ALL;
Session altered.
SQL> alter session set max_dump_file_size=UNLIMITED;
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> variable b number;
SQL> declare
2 cnt number;
3 begin
4 :b:=1;
5 select count(*) into cnt from dba_objects where object_id=:b;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context off';
Session altered.
Checking trace file ......
PARSING IN CURSOR #2 len=52 dep=1 uid=0 oct=3 lid=0 tim=2797155817 hv=1220784193 ad='130a9d4c'
SELECT count(*) from dba_objects where object_id=:b1
END OF STMT
PARSE #2:c=15625,e=24270,p=0,cr=5,cu=0,mis=1,r=0,dep=1,og=0,tim=2797155809
BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=24 offset=0
bfp=030a7b2c bln=22 avl=02 flg=05
value=1
EXEC #2:c=0,e=2062,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2797159524
FETCH #2:c=0,e=37,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=2797159885
------ Query #2 : Varhchar2 Bind variable
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> variable b varchar2(30);
SQL> declare
2 cnt number;
3 begin
4 :b:='A';
5 select count(*) into cnt from dba_objects where object_name=:b;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context off';
Checking trace file .....
PARSING IN CURSOR #2 len=54 dep=1 uid=0 oct=3 lid=0 tim=3007778755 hv=2029951970 ad='12ff2c80'
SELECT count(*) from dba_objects where object_name=:b1
END OF STMT
PARSE #2:c=15625,e=12991,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=0,tim=3007778748
BINDS #2:
bind 0: dty=1 mxl=32(30) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0
bfp=030a79ec bln=32 avl=01 flg=05
value="A"
EXEC #2:c=0,e=2167,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3007782524
WAIT #2: nam='db file sequential read' ela= 9458 p1=1 p2=89 p3=1
WAIT #2: nam='db file sequential read' ela= 6606 p1=1 p2=26791 p3=1
TIP # 63 : DIRECT Load and redo log generation
I had a client who uses "direct load" for its nightly job on productiton database.Suprisingly many archive logs were generated during job run.The whole purpose of "Direct load" is to improve performance by gnerating less archive logs.
However, in this case does not see any difference.
I am trying to explain on how Direct load would help.
Some facts :
1.The undo would normally be used to un-insert the rows in the event of a
failure or rollback - with DIRECT LOAD, undo is not necessary since the new rows are added entirely above the high water mark for the table.
2. When "Direct load" (/*+ append */) is used, Oracle can skip undo generation for the TABLE data - but not on the indexes.
3. In archivelog mode , REDO is normally generated with "Direct load" (/*+ APPEND */), it is UNDO that is skipped and then only for the table itself.
4.If table is placed into "nologging" mode (or use nologging in insert
command), then redo for the table as well as undo can be skipped..Again, only for the table - not for any indexes on the table itself.
5. Small redos which are generated in nologgin/DIRECT LOAD is used to protect the data dictionary.
6. To prevent archivelog generation in "Direct load", database and tablespace should not be in "Force logging mode". (Check v$database and dba_tablespaces).
Considering above facts, my first answer to a client was: "Archives are generated because of indexes on tables".
To demonstrate, Please follow below simple steps :
========== Table without index in Direct load ===========
SQL> create table tbl2 as select * from dba_objects where 1=2;
Table created.
--- Checking redo and undo before direct load
select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';
VALUE NAME
---------- ----------
0 redo size
select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');
UNDO-MB
----------------------------
19.78125
---- Direct load
insert /*+ append */ into tbl2 nologging select * from dba_objects;
select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';
VALUE NAME
---------- ----------
7068580 redo size
select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');
UNDO-MB
----------------------------
25.78125
Generated redo : 7068580 bytes
Generated undo : 6 MB
========== Table with index in Direct load ===========
SQL> create table tbl1 as select * from dba_objects where 1=2;
Table created.
SQL> create index idx1 on tbl1(object_name);
Index created.
SQL> create index idx2 on tbl1(owner);
Index created.
---- redo log before direct load
select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';
VALUE NAME
---------- ----------
0 redo size
select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');
UNDO-MB
----------
25.78125
------- Direct load
SQL> insert /*+ append */ into tbl1 nologging select * from dba_objects;
62908 rows created.
------ Redo log and Undo after direct load.
select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';
VALUE NAME
---------- ----------
28251320 redo size
select sum(undoblks)*8192/1024/1024 from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');
UNDO-MB
----------------------------
42.5078125
Generated redo : 28251320 bytes
Generated undo : 17 MB
More archives and more UNDOs in direct load on table with indexes.
Sum up :
=========
To use direct load and to take advantage of less archivelog generation and better performance, always consider the followings :
1. Disable indexes during direct load.
2. Make sure to use both /*+ append */ with nologging at the same time.
3. Make sure database and tablespace are not in nologging mode.
However, in this case does not see any difference.
I am trying to explain on how Direct load would help.
Some facts :
1.The undo would normally be used to un-insert the rows in the event of a
failure or rollback - with DIRECT LOAD, undo is not necessary since the new rows are added entirely above the high water mark for the table.
2. When "Direct load" (/*+ append */) is used, Oracle can skip undo generation for the TABLE data - but not on the indexes.
3. In archivelog mode , REDO is normally generated with "Direct load" (/*+ APPEND */), it is UNDO that is skipped and then only for the table itself.
4.If table is placed into "nologging" mode (or use nologging in insert
command), then redo for the table as well as undo can be skipped..Again, only for the table - not for any indexes on the table itself.
5. Small redos which are generated in nologgin/DIRECT LOAD is used to protect the data dictionary.
6. To prevent archivelog generation in "Direct load", database and tablespace should not be in "Force logging mode". (Check v$database and dba_tablespaces).
Considering above facts, my first answer to a client was: "Archives are generated because of indexes on tables".
To demonstrate, Please follow below simple steps :
========== Table without index in Direct load ===========
SQL> create table tbl2 as select * from dba_objects where 1=2;
Table created.
--- Checking redo and undo before direct load
select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';
VALUE NAME
---------- ----------
0 redo size
select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');
UNDO-MB
----------------------------
19.78125
---- Direct load
insert /*+ append */ into tbl2 nologging select * from dba_objects;
select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';
VALUE NAME
---------- ----------
7068580 redo size
select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');
UNDO-MB
----------------------------
25.78125
Generated redo : 7068580 bytes
Generated undo : 6 MB
========== Table with index in Direct load ===========
SQL> create table tbl1 as select * from dba_objects where 1=2;
Table created.
SQL> create index idx1 on tbl1(object_name);
Index created.
SQL> create index idx2 on tbl1(owner);
Index created.
---- redo log before direct load
select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';
VALUE NAME
---------- ----------
0 redo size
select sum(undoblks)*8192/1024/1024 UNDO-MB from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');
UNDO-MB
----------
25.78125
------- Direct load
SQL> insert /*+ append */ into tbl1 nologging select * from dba_objects;
62908 rows created.
------ Redo log and Undo after direct load.
select a.value,b.name from v$sesstat a,v$statname b where a.statistic#=b.statistic# and a.sid in (select sid from v$mystat) and name like '%redo size%';
VALUE NAME
---------- ----------
28251320 redo size
select sum(undoblks)*8192/1024/1024 from v$undostat where trunc(begin_time,'DD')=trunc(sysdate,'DD');
UNDO-MB
----------------------------
42.5078125
Generated redo : 28251320 bytes
Generated undo : 17 MB
More archives and more UNDOs in direct load on table with indexes.
Sum up :
=========
To use direct load and to take advantage of less archivelog generation and better performance, always consider the followings :
1. Disable indexes during direct load.
2. Make sure to use both /*+ append */ with nologging at the same time.
3. Make sure database and tablespace are not in nologging mode.
oradbatips is 1 year old !
I am more than happy to celebrate the first anniversary of this blog.
It was exactly last year which I decided to create this blog in order to share my little knowledge with others around the world.
Thanks to all of you to give me the courage with your comments, positive criticism, which is my main motivation on keep this blog updated.
My plan for upcoming years is to keep this blog up-to-date.
Also I am thinking to have a place in this blog where readers can ask any questions/problems and issues related to Oracle Administration.
As always, any comment is really appreciated.
It was exactly last year which I decided to create this blog in order to share my little knowledge with others around the world.
Thanks to all of you to give me the courage with your comments, positive criticism, which is my main motivation on keep this blog updated.
My plan for upcoming years is to keep this blog up-to-date.
Also I am thinking to have a place in this blog where readers can ask any questions/problems and issues related to Oracle Administration.
As always, any comment is really appreciated.
Subscribe to:
Posts (Atom)