Small change make difference

Small change make difference.

How small , small things make difference , here is one live example
One of my friend want to learn 11g, so she downloaded 11g and started installing on

Linux Box and created Database manually. Next time when she logged in, she did not know where she installed 11g , since she created database manually , so there was no entry in “oratab”

I remember command “pwdx” on Unix Solaris , which give current working directory of processes

$ uname -a SunOS mysun 5.8 Generic_xyz sun4u sparc SUNW,Ultra-Enterprise MYSUN:oracle> (10.1.0.4) /usr/proc/bin $ ps -aef grep pmon oracle 2424 1 0 Jan 18 ? 13:39 ora_pmon_test oracle 8337 13002 0 05:31:47 pts/7 0:00 grep pmon mysun:oracle> (10.1.0.4) /usr/proc/bin $ pwdx 2424 2424: /u01/app/oracle/product/10.1.0.4/dbs <

But on Linux(RHEL 4 ) there is no command like “pwdx” . In linux you can check current working directory of processes ( ORACLE_HOME/dbs i.e lock file location ) in /proc/

[root@apps001 proc]# ps -aef grep pmon oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g root 23180 13728 0 23:19 pts/2 00:00:00 grep pmon [root@apps001 proc]# ls -l /proc/8268 total 0 dr-xr-xr-x 2 oracle oinstall 0 Jan 27 23:20 attr -r-------- 1 oracle oinstall 0 Jan 27 23:20 auxv -r--r--r-- 1 oracle oinstall 0 Jan 27 23:19 cmdline lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 cwd -> /u01/app/oracle/11.1.0/dbs -r-------- 1 oracle oinstall 0 Jan 27 23:20 environ lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 exe -> /u01/app/oracle/11.1.0/bin/oracle dr-x------ 2 oracle oinstall 0 Jan 27 23:20 fd -rw-r--r-- 1 oracle oinstall 0 Jan 27 23:20 loginuid -r-------- 1 oracle oinstall 0 Jan 27 23:20 maps -rw------- 1 oracle oinstall 0 Jan 27 23:20 mem -r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 mounts lrwxrwxrwx 1 oracle oinstall 0 Jan 27 23:20 root -> / -r--r--r-- 1 oracle oinstall 0 Jan 27 23:18 stat -r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 statm -r--r--r-- 1 oracle oinstall 0 Jan 27 23:16 status dr-xr-xr-x 3 oracle oinstall 0 Jan 27 23:20 task -r--r--r-- 1 oracle oinstall 0 Jan 27 23:20 wchan

>

< [oracle@apps001 oracle]$ cd /u01/app/oracle/11.1.0/dbs [oracle@apps001 dbs]$ ls hc_orcl11g.dat initdw.ora init.ora lkORCL11G orapworcl11g spfileorcl11g.ora [oracle@apps001 dbs]$ /sbin/fuser * hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372 lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372

I asked her to create one small shell script to source oracle 11g environment variable ,
she wrote following script

ORACLE_HOME=/u01/app/oracle/11.1.0/ export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH ORACLE_SID=orcl11g export ORACLE_SID ~ "11g.env" 7L, 136C written [oracle@apps001 ~]$chmod 755 11g.env [oracle@apps001 ~]$ . ./11g.env

She called me after some time and said , after sourcing 11g environment variable , when she try to connect oracle 11g as sysdba , it says “connect to ideal instance”

[oracle@apps001 ~]$ . ./11g.env [oracle@apps001 ~]$ sqlplus “/ as sysdba” SQL*Plus: Release 11.1.0.6.0 - Production on Sun Jan 27 23:45:42 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to an idle instance. Ctl-d [oracle@apps001 ~]$ ps -aef grep pmon oracle 2826 1 0 20:55 ? 00:00:00 xe_pmon_XE oracle 8268 1 0 21:21 ? 00:00:00 ora_pmon_orcl11g oracle 24791 24446 0 23:46 pts/4 00:00:00 grep pmon [oracle@apps001 8268]$ cd /u01/app/oracle/11.1.0/dbs [oracle@apps001 dbs]$ /sbin/fuser * hc_orcl11g.dat: 8268 8268m 8270 8270m 8274 8274m 8276 8276m 8278 8278m 8282 8282m 8284 8284m 8286 8286m 8288 8288m 8290 8290m 8292 8292m 8294 8294m 8296 8296m 8298 8298m 8300 8302 8312 8312m 8314 8314m 8316 8316m 8320 8322 8394 8394m 22755 25372 lkORCL11G: 8268 8276 8278 8284 8286 8288 8290 8292 8294 8296 8298 8312 8314 8316 8320 8322 8394 22755 25372 # It means database is up (may be nomount , mount or open mode ) #

When ran “fuser” on lock file oracle BG processes are connected to lock file, It means database is up (may be nomount , mount or open mode )

Checked alert.log , to make sure things are fine. It shows database is OPEN

[oracle@apps001 oracle]$ adrci ADRCI: Release 11.1.0.6.0 - Beta on Sun Jan 27 23:52:16 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. ADR base = "/u01/app/oracle" adrci> set editor vi adrci> show alert Choose the alert log from the following homes to view: 1: diag/tnslsnr/apps001/listener 2: diag/rdbms/orcl2/orcl2 3: diag/rdbms/stdorcl2/stdorcl2 4: diag/rdbms/orcl11g/orcl11g Q: to quit Please select option:4 space available in the underlying filesystem or ASM diskgroup. 2008-01-27 21:22:07.366000 +05:30 Completed: ALTER DATABASE OPEN 2008-01-27 21:22:26.499000 +05:30 Starting background process CJQ0 CJQ0 started with pid=26, OS id=8394 2008-01-27 21:23:24.231000 +05:3 Thread 1 advanced to log sequence 3 Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/orcl11g/redo03.log 2008-01-27 21:29:03.616000 +05:30

Finally decided to check shell script , which source 11g environment variable. There suppose to be no issue at script because we able to run “adrci” and able to see alert.log

[oracle@apps001 ~]$vi 11g.env ORACLE_HOME=/u01/app/oracle/11.1.0/ export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH ORACLE_SID=orcl11g export ORACLE_SID ~ ~ "11g.env" 7L, 136C written # # I made little change in script i.e. removed last “/” from ORACLE_HOME # vi 11g.env ORACLE_HOME=/u01/app/oracle/11.1.0 export ORACLE_HOME PATH=$ORACLE_HOME/bin:$PATH export PATH ORACLE_SID=orcl11g export ORACLE_SID ~ ~ "11g.env" 7L, 136C written

After I removed last “/” from ORACLE_HOME , she able to connect to database J

[oracle@apps001 ~]$ . ./11g.env [oracle@apps001 ~]$ sys SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 28 00:00:54 2008 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>

So small change make difference.

Oracle 11g Security Enhancements Part – 1

Oracle 11g Security Enhancements Part – 1

New parameters have been added to the Oracle Database 11g to enhance the default security of the database.

• SEC_RETURN_SERVER_RELEASE
• SEC_PROTOCOL_ERROR_FURTHER_ACTION
• SEC_PROTOCOL_ERROR_TRACE_ACTION
• SEC_MAX_FAILED_FAILED_LOGIN_ATTEMPTS
• SEC_DISABLE_OLDER_ORACLE_RPCS*

These parameters are system wide and static.

• Release of server information restriction

You can restrict the display of the database version banner to unauthenticated clients by setting the SEC_RETURN_SERVER_RELEASE_BANNER initialization parameter in the initsid.ora initialization parameter file to either YES or NO. By default, SEC_RETURN_SERVER_RELEASE_BANNER is set to FALSE.

SQL> show parameter SEC_RETURN_SERVER_RELEASE_BANNER NAME TYPE VALUE ----------------------------- -------------------- --------------------- sec_return_server_release_banner boolean FALSE

When set to true the full banner is displayed. When the value is set to FALSE, a limited generic banner is displayed.

• Protect against denial of Service (DoS) attacks

The two parameters shown specify the actions to be taken when the database receives bad packets from a client. The assumption is that the bad packets are from a possible malicious client. The SEC_PROTOCOL_ERROR_FURTHER_ACTION parameter specifies what action is to be taken with the client connection: Continue, drop the connection, or delay accepting requests.

# Continue connection after 10 bad packet SEC_PROTOCOL_ERROR_FURTHER_ACTION = Continue #Delay 4 Sec before sever accept next connection SEC_PROTOCOL_ERROR_FURTHER_ACTION = Delay,4 # Drop connection after 10 bad packet SEC_PROTOCOL_ERROR_FURTHER_ACTION = Drop,10

The other parameter SEC_PROTOCOL_ERROR_TRACE_ACTION specifies a monitoring action: NONE, TRACE(Default) , LOG, or ALERT.

# DEFAULT Creates the trace files, but it is useful for debugging purposes, SEC_PROTOCOL_ERROR_TRACE_ACTION = Trace # Writes a short, one-line error message to the server trace file and alert log. SEC_PROTOCOL_ERROR_TRACE_ACTION = Alert # Writes a short, one-line message to the server trace file. SEC_PROTOCOL_ERROR_TRACE_ACTION = Log # Configures the server to ignore the bad packets and does not generate any trace files or SEC_PROTOCOL_ERROR_TRACE_ACTION = None

>

SQL> show parameter SEC_PROTOCOL_ERROR_FURTHER_ACTION NAME TYPE VALUE ------------------------------- ------------------- ------------------------ sec_protocol_error_further_action string CONTINUE SQL> show parameter SEC_PROTOCOL_ERROR_TRACE_ACTION NAME TYPE VALUE ---------------------- ------------------------- ----------------------- sec_protocol_error_trace_action string TRACE

• Protect against intruder

If profile is not enabled then intruder can try unlimited number of authenticated requests with different user names and passwords in an attempt to gain access to the database.

A new initialization parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS that has a default setting of 10 causes a connection to be automatically dropped after the specified number of attempts. This parameter is enforced even when the password profile is not enabled.

SQL> show parameter SEC_MAX_FAILED_LOGIN_ATTEMPTS NAME TYPE VALUE -------------------------- -------------------------------------------------- sec_max_failed_login_attempts integer 10

>

The sqlnet.ora INBOUND_CONNECT_TIMEOUT parameter and the FAILED_LOGIN_ATTEMPTS initialization parameter also restrict failed logins, but the difference is that these two parameters only apply to valid user accounts.

>

Recently come across new parameter SEC_DISABLE_OLDER_ORACLE_RPCS on Database Error Messages 11g Release 1

>
ORA-03139: This OCI call has been disabled by the DBA
Cause: The SEC_DISABLE_OLDER_ORACLE_RPCS initialization parameter was enabled.
Action: Contact the Database Administrator

But when searched oracle documentation not found any detail about it , also not found any detail about it on Database

SQL> show parameter SEC_DISABLE_OLDER_ORACLE_RPCS SQL>