All posts by Larry Catt

11g, linux

Creating multiple Oracle Environments in Single Linux Server

Leave a comment

When preparing your RHEL server for an Oracle RDBMS or other application software installations, it is common to have multiple oracle RDBMS instances or other related applications which share the same environmental variables, but with different values.    This procedure shows how to setup your RHEL environment to support multiple Oracle software installs.

 

  1. Logon as the oracle software user.
  2. Go to the user’s home directory and edit the .bash_profile file with the following lines and placing the environmental variables for each database instance in the separate block.

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# User specific environment and startup programs

echo “Choose the number associated with the SID you wish to administrate:”

echo ” 1 – ORCL”

echo ” 2 – MYDB”

read SID_NUMBER

if [ $SID_NUMBER = 1 ]; then

echo “SID Number is $SID_NUMBER”

<ENTER ENVIRONMENTAL Variables for instance 1 here>

elif [ $SID_NUMBER = 2 ]; then

echo “SID Number is $SID_NUMBER”

<ENTER ENVIRONMENTAL Varaibles for instance 2 here>

else

<Default environmental variables>

echo “Invalid SID Number, defaulting to $ORACLE_SID”

fi

 

  1. In our example the .bash_profile file looks like:

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

echo “Choose the number associated with the SID you wish to administrate:”

echo ” 1 – ORCL”

echo ” 2 – MYDB”

read SID_NUMBER

if [ $SID_NUMBER = 1 ]; then

echo “SID Number is $SID_NUMBER”

export JAVAPATH=/usr/bin/java

export ORACLE_BASE=/opt/app/oracle

export ORACLE_HOME=/opt/app/oracle/ORCL_db

export ORACLE_SID=ORCL

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$JAVAPATH:$ORACLE_HOME/lib

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

umask 022

elif [ $SID_NUMBER = 2 ]; then

echo “SID Number is $SID_NUMBER”

export JAVAPATH=/usr/bin/java

export ORACLE_BASE=/opt/app/oracle

export ORACLE_HOME=/opt/app/oracle/MYDB_db

export ORACLE_SID=MYDB

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$JAVAPATH:$ORACLE_HOME/lib

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

umask 022

else

export JAVAPATH=/usr/bin/java

export ORACLE_BASE=/opt/app/oracle

export ORACLE_HOME=/opt/app/oracle/MYDB_db

export ORACLE_SID=MYDB

export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$JAVAPATH:$ORACLE_HOME/lib

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

umask 022

echo “Invalid SID Number, Defaulting to $ORACLE_SID”

fi

  1. No logon as the oracle user and select the appropriate choose for your environment.

[root@MYLINUX ~]# su – oracle

Choose the number associated with the SID you wish to administrate:

1 – ORCL

2 – MYDB

1

SID Number is 1

[oracle@MYLINUX ~]$ exit

logout

[root@MYLINUX ~]# su – oracle

Choose the number associated with the SID you wish to administrate:

1 – ORCL

2 – MYDB

2

SID Number is 2

[oracle@MYLINUX ~]$ exit

logout

[root@MYLINUX ~]# su – oracle

Choose the number associated with the SID you wish to administrate:

1 – ORCL

2 – MYDB

5

Invalid SID Number, Defaulting to MYDB

[oracle@MYLINUX ~]$

  1. This completes creating multiple oracle environmental variables for a single Linux Server

 

Larry Catt, OCP

11g, error

Oracle error ORA-12541 during EMCA execution

Leave a comment

Normally failure of oracle utility commands occurs from improper environmental parameters or configuration problems.   This procedure shows the steps in resolving oracle error ORA-12541 during execution of EMCA repository creation.

 

 

Initial Error:

 

[oracle@mylinux1 ~]$ emca -repos create

 

STARTED EMCA at Apr 7, 2014 4:31:20 PM

EM Configuration Assistant, Version 11.2.0.3.0 Production

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

 

Enter the following information:

Database SID: ORCL

Listener port number: 1521

Password for SYS user:

Password for SYSMAN user:

 

Do you wish to continue? [yes(Y)/no(N)]: Y

Apr 7, 2014 4:31:36 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /opt/app/oracle/cfgtoollogs/emca/ORCL/emca_2014_04_07_16_31_19.log.

Apr 7, 2014 4:31:36 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl

WARNING: ORA-12541: TNS:no listener

 

Apr 7, 2014 4:31:36 PM oracle.sysman.emcp.EMConfig perform

SEVERE:

 

Database connection through listener failed. Fix the error and run EM Configuration Assistant again.

 

Some of the possible reasons may be:

 

1) Listener port 1521 provided is incorrect. Provide the correct port.

2) Listener is not up. Start the Listener.

3) Database service ORCL is not registered with listener. Register the database service.

4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to virtual host. Unset ORACLE_HOSTNAME environment variable.

5) Listener is up on virtual host. Set environment variable ORACLE_HOSTNAME=<virtual host>.

6) /etc/hosts does not have correct entry for hostname.

 

Refer to the log file at /opt/app/oracle/cfgtoollogs/emca/ORCL/emca_2014_04_07_16_31_19.log for more details.

Could not complete the configuration. Refer to the log file at /opt/app/oracle/cfgtoollogs/emca/ORCL/emca_2014_04_07_16_31_19.log for more details.

[oracle@mylinux1 ~]$

 

 

 

Solution:

 

1.  Validate listener port is 1521 with command “netstat -tulpn|grep 1521”:

 

[oracle@mylinux1 ~]$ netstat -tulpn|grep 1521

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp        0      0 192.168.1.120:1521           0.0.0.0:*                   LISTEN      10883/tnslsnr

udp        0      0 :::15212                    :::*                                    1831/ora_p025_ORCL

[oracle@mylinux1 ~]$

 

NOTE:  Oracle is listening on port 1521 with process 10883/tnslsnr.   If the oracle listener is not on port 1521, open the file listener.ora to determine the correct port.

 

 

2. Validate that the Listener is up with command “ps -ef|grep tns”:

 

[oracle@mylinux1 ~]$ ps -ef|grep tns

root        90     2  0 Apr01 ?        00:00:00 [netns]

oracle   10883     1  0 Apr01 ?        00:03:54 /opt/app/oracle/ORCL_db/bin/tnslsnr LISTENER -inherit

oracle   21669 20714  0 16:45 pts/0    00:00:00 grep tns

[oracle@mylinux1 ~]$

 

NOTE:  The listener is up with process id of 10883.   If the listener is not running, start the listener with command: “lsnrctl start”

 

3. Verify that the Database service ORCL is registered with listener by using the command “tnsping ORCL”:

 

[oracle@mylinux1 ~]$ tnsping ORCL

TNS Ping Utility for Linux: Version 11.2.0.3.0 – Production on 07-Apr-2014 16:47:16

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

 

Used parameter files:

/opt/app/oracle/ORCL_db/network/admin/sqlnet.ora

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.120)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL) (SID = ORCL)))

OK (200 msec)

[oracle@mylinux1 ~]$

 

NOTE:  The ORCL service is register with the listener.  If it is not, edit the file listener.ora to add the service.

 

 

4. Verify that ORACLE_HOSTNAME is set with command “echo $ORACLE_HOSTNAME”:

 

[oracle@mylinux1 ~]$ echo $ORACLE_HOSTNAME

 

[oracle@mylinux1 ~]$

 

 

NOTE:  the ORACLE_HOSTNAME variable is not set, set the ORACLE_HOSTNAME variable with the command “export ORACLE_HOSTNAME=mylinux1”   NOTE: place this variable in your bash startup profile.

 

 

[oracle@mylinux1 ~]$ export ORACLE_HOSTNAME=mylinux1

[oracle@mylinux1 ~]$ echo $ORACLE_HOSTNAME

mylinux1

[oracle@mylinux1 ~]$

 

5. Same as step 4 but using virtual hostname.

 

6. Verify that the file /etc/hosts contains the hostname of current system with command “cat /etc/hosts”

 

[oracle@mylinux1 ~]$ cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 mylinux1 mylinux1.mydomain.com

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 mylinux1 mylinux1.mydomain.com

192.168.1.120    mylinux1 mylinux1.mydomain.com

[oracle@mylinux1 ~]$

 

 

NOTE: The hostname is in the /etc/hosts file.   But if it is not edit the file /etc/hosts.

 

7.  Re-execute the EMCA command for successful completion:

 

 

[oracle@mylinux1 ~]$ emca -repos create

 

STARTED EMCA at Apr 7, 2014 4:59:36 PM

EM Configuration Assistant, Version 11.2.0.3.0 Production

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

 

Enter the following information:

Database SID: ORCL

Listener port number: 1521

Password for SYS user:

Password for SYSMAN user:

 

Do you wish to continue? [yes(Y)/no(N)]: Y

Apr 7, 2014 5:00:12 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /opt/app/oracle/cfgtoollogs/emca/ORCL/emca_2014_04_07_16_59_35.log.

Apr 7, 2014 5:00:13 PM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) …

Apr 7, 2014 5:14:27 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully created

Enterprise Manager configuration completed successfully

FINISHED EMCA at Apr 7, 2014 5:14:27 PM

[oracle@mylinux1 ~]$

 

 

 

Larry Catt, OCP

 

Oracle

Removal of Oracle Software and Database from Linux

Leave a comment

Remove an Oracle RDBMS from a configured system can be performed in three steps: Removal of existing RDBMS, removal of Oracle binaries, and finally removal of Oracle system references.  This procedure demonstrates the execution of this process.

 

  1. Logon to Linux as oracle software owner:

 

 [root@mylinux ~]# su – oracle

[oracle@mylinux ~]$

 

  1. Execute the following DBCA command to remove the existing database, you will need to know the information:  ORACLE_SID, User with SYSDBA privileges, and SYSDBA password.

 

 [oracle@mylinux bin]$ dbca -silent  -deleteDatabase -sourceDB orcl -sysDBAUserName sys -sysDBAPassword xxxxxx

 

Connecting to database

4% complete

9% complete

14% complete

19% complete

23% complete

28% complete

47% complete

Updating network configuration files

52% complete

Deleting instance and datafiles

76% complete

100% complete

Look at the log file “/opt/app/oracle/cfgtoollogs/dbca/orcl1.log” for further details.

[oracle@mylinux bin]$

 

  1. Remove Oracle Home directory as the root user.

 

[root@mylinux oracle]# rm -rf /opt/app/oracle/orcl_db

[root@mylinux oracle]#

 

  1. Remove Oracle storage directories if they exist.

 

[root@mylinux u01]# cd /u01/oradata

[root@mylinux oradata]#

 

  1. Remove Oracle system references in the /etc directory

 

[oracle@mylinux ~]$ rm -rf /etc/*ora*

[oracle@mylinux ~]$

 

 

  1. Remove Oracle Inventory.

 

 

[root@mylinux app]# rm -rf /opt/app/inventory_location/

[root@mylinux app]#

 

  1. This completes removing oracle software from a Linux environment.

 

 

 

 

Larry Catt, OCP

listener, network, Oracle

Oracle defining different name for SID and Database

Leave a comment

There are a ton of reasons to define a different name for Oracle SID and database, however you must ensure that your network configuration is properly setup to support this function. The key is defining the variable GLOBAL_DBNAME in your tnsnames.ora file to match your init parameter db_name, which allows various applications to include EM to resolve the database regardless of names defined.

TNSNAMES.ora for orcl on both SID and DB_NAME.


(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(SERVICE_NAME = orcl)
(ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_orcl)
)
)

TNSNAMES.ora for orcl on SID and ORACLE on DB_NAME.

(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(SERVICE_NAME = orcl)
(GLOBAL_DBNAME=ORACLE)
(ORACLE_HOME = C:\oracle\product\11.2.0\dbhome_orcl)
)
)

Larry Catt OCP

Oracle, Oracle 10g

Testing Rate of Oracle inserts into a table

Leave a comment

You can use any type of table or any data to see the rate of insert you are getting out of your Oracle RDBMS.   For this demonstration, we used a test table named INSERT_TEST and repeating insert of ever changing record sets.    You can change the scripts attached to satisfy your particular needs.

 

1.  Create table to hold insert records.

create table insert_test( var_a varchar2(100), var_b number(10,1));

2.  Insert a single record into your test table.

insert into insert_test VALUES(‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’,999999999.9);

3.  Determine the size of row inserted.

SQL> select avg_row_len from dba_tab_statistics where table_name=’INSERT_TEST’; AVG_ROW_LEN

———–        

112

SQL>

4.  Edit the following script, replacing the record size with the actual size you received in step 3 above.

SET SERVEROUTPUT ON

DECLARE  

l_loops           NUMBER := 100000;

 l_start           NUMBER;

 total_time number;

v_ct  number:=0;

v_insert_ct number:=1.1;

v_var_a varchar2(100);

v_mb number;

v_rec_ct number;

record_size number:=112;  

BEGIN

   l_start := DBMS_UTILITY.get_time; 

  FOR i IN 1 .. l_loops LOOP

  v_insert_ct:=v_insert_ct+1; v_var_a:=’AAAAAAAAAAAAAAA’||v_insert_ct; insert into insert_test VALUES(v_var_a,v_insert_ct);

v_ct:=v_ct+1;

if v_ct>99

then

commit;

v_ct:=0;

 end if;

   END LOOP;   

 DBMS_OUTPUT.put_line(‘Execution Time   : ‘ ||(DBMS_UTILITY.get_time – l_start)||’  ms’); DBMS_OUTPUT.put_line(chr(10));

select round(100000/((DBMS_UTILITY.get_time – l_start)/100),2) into v_rec_ct from dual;

DBMS_OUTPUT.put_line(‘Number of Inserts per second: ‘||v_rec_ct||’ records’);

DBMS_OUTPUT.put_line(chr(10));

select round((100000/((DBMS_UTILITY.get_time – l_start)/100)*record_size)/1024/1024,2) into v_mb from dual;

DBMS_OUTPUT.put_line(‘MB of Inserts per second: ‘||v_mb|| ‘ MB’);

END; 

 /

5.  The result set below is returned, showing the insert rate achieved by your Oracle installation.   NOTE:  Multiple things will impact your insert rate, so for additional question leave a comment.

Execution Time   : 513  ms

Number of Inserts per second: 19493.18 records

MB of Inserts per second: 2.08 MB

PL/SQL procedure successfully completed.

SQL>

Larry Catt

Oracle, Oracle 10g, oracle 9i, script

Determining the record insert rate for a table

Leave a comment

Some system require the insertion of large amounts of data in as short a period of time as possible. In this cases, you must be able to measure actual physical time of insertion per give number of records or number of records inserted per second.   The following block allows you to perform this action on a give oracle operation.

We will use the following anonymous block to track our processing time:

SET SERVEROUTPUT ON  DECLARE      v_loops          NUMBER := 1000000;    v_start_time     NUMBER;    v_total_time  NUMBER;    v_ct    NUMBER :=1;    BEGIN

— Time we started our process.    v_start_time := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP   < Oracle Action:  Insert, function call, procedure call, etc >   v_ct:=v_ct+1;   commit;   END LOOP;       –Calculate the time in seconds     v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100;           DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’);  END;  /     Example Execution:

1.  Connect as the user scott and create a table test_data

SQL> connect scott/tiger@orcl Connected. SQL> create table test_data(user_no number, fname varchar2(30), lname varchar2(30));

Table created.

SQL>

2.  Execute your block with < Oracle Action …> replaced with an insert statement for test data.

SET SERVEROUTPUT ON  DECLARE      v_loops          NUMBER := 1000000;    v_start_time     NUMBER;    v_total_time  NUMBER;    v_ct    NUMBER :=1234;    BEGIN

— Time we started our process.    v_start_time := DBMS_UTILITY.get_time;

FOR i IN 1 .. v_loops LOOP   insert into scott.test_data(user_no, fname, lname) values(v_ct, ‘JANE’, ‘DOE’);   v_ct:=v_ct+1;   commit;   END LOOP;       –Calculate the time in seconds     v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100;           DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’);  END;  /      Results:    SQL> SET SERVEROUTPUT ON SQL>  DECLARE   2   3     v_loops          NUMBER := 1000000;   4     v_start_time     NUMBER;   5     v_total_time            NUMBER;   6     v_ct                            NUMBER :=1234;   7   8   BEGIN   9  10     — Time we started our process.  11     v_start_time := DBMS_UTILITY.get_time;  12  13    FOR i IN 1 .. v_loops LOOP  14  15  insert into scott.test_data(user_no, fname, lname) values(v_ct, ‘JANE’, ‘DOE’);  16    v_ct:=v_ct+1;  17    commit;  18    END LOOP;  19  20     –Calculate the time in seconds  21      v_total_time := (DBMS_UTILITY.get_time – v_start_time)/100;  22  23      DBMS_OUTPUT.put_line(‘This process took a total of : ‘ ||v_total_time||’ seconds to complete’);  24     END;  25   / This process took a total of : 86.45 seconds to complete

PL/SQL procedure successfully completed.

SQL> SQL>

3.  The results show a insert rate of 86.45 seconds for 1 million records or 11,567.38 records per second.

Larry Catt

Oracle, script, SQL, sqlplus

Currently executing SQL in Oracle database

Leave a comment

I was just asked by a junior administrator, how to determine what SQL is currently executing against her database. While this may seem a trivial task for those in the industry for some time, I thought it a very good question. The below SQL will display currently executing SQL within an Oracle RDBMS.

1. Logon to SQL*PLUS with dba privileges.

2. Execute the following SQL block against the v$sql and v$session tables. NOTE: in a grid environment, use the gv$ views.

select sql_text from v$sql where sql_id in
(select sql_id from v$session where status=’ACTIVE’);

3. Displayed results show currently executing SQL blocks.

SQL> select sql_text from v$sql where sql_id in
2 (select sql_id from v$session where status=’ACTIVE’);

SQL_TEXT
——————————————————————————–
select sql_text from v$sql where sql_id in (select sql_id from v$session where s
tatus=’ACTIVE’)

Larry Catt, OCP

hp-ux, linux, Oracle

Translate UNIX / Linux EPOCH time to Oracle date

Leave a comment

Translate UNIX / Linux EPOCH time to Oracle date

Epoch time in computers is general defined as the number of milliseconds since 1 January 1970 and provides for a very useful way of determining times between system and other time specific functions.   However, it is very difficult for humans to read.   The following code will convert an Epoch time to Oracle date time format.

Given epoch time is 579052800000. we can retrieve oracle time as below.

SQL> define test_time=579052800000

SQL> select  (TO_DATE(’01-01-1970′,’MM-DD-YYYY’) + (&test_time / 86400000)-1) from dual;

old   1: select  (TO_DATE(’01-01-1970′,’MM-DD-YYYY’) + (&test_time / 86400000)-1) from dual

new   1: select  (TO_DATE’01-01-1970′,’MM-DD-YYYY’) + (579052800000 / 86400000)-1) from dual

(TO_DATE(

———

07-MAY-88

SQL>

and convert standard oracle date to epoch

 

select to_char(sysdate – to_date(’01-01-1970′,’MM-DD-YYYY’))*86400000 from dual;

 

SQL> select to_char(sysdate – to_date(’01-01-1970′,’MM-DD-YYYY’))*86400000 from dual;

TO_CHAR(SYSDATE-TO_DATE(’01-01-1970′,’MM-DD-YYYY’))*86400000 ————————————————————

1.3807E+12

SQL>

Larry Catt