Tag Archives: Oracle

12c, Administration, CDB, Multitenant

Determining SID(s) from Database Server

Leave a comment

Oracle Databases implemented on Unix and Linux have a file /etc/oratab file which determines the current SID, Oracle Homes, or
active status which exist on the server, if a proper installation is done. This procedure uses the file to extract
current SID(s).

1. Logon to your Oracle Server as the Oracle software owner.

[root@linux2 ~]# su – oracle
Last login: Wed Oct 5 15:16:55 EDT 2016 on pts/2
[oracle@linux2 ~]$

2. Execute the command: egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $1}’

[oracle@linux2 ~]$ egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $1}’
cdb1
[oracle@linux2 ~]$

3. For this system there is only one database with a SID of ‘cdb1’.

Larry Catt
OCP

12c, Administration

Determining ORACLE_HOME(s) from Database Server

Leave a comment

Oracle Databases implemented on Unix and Linux have a file /etc/oratab file which determines the current SID, Oracle Homes, or
active status which exist on the server, if a proper installation is done. This procedure uses the file to extract
current ORACLE_HOME(s).

1. Logon to your Oracle Server as the Oracle software owner.

[root@linux2 ~]# su – oracle
Last login: Wed Oct 5 15:16:55 EDT 2016 on pts/2
[oracle@linux2 ~]$

2. Execute the command: egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $2}’

[oracle@linux2 ~]$ egrep -E -v ‘^(#)’ /etc/oratab | awk /./ | awk -F: ‘{print $2}’
/opt/app/oracle/product/12.1.0.2/db_1
[oracle@linux2 ~]$

3. For this system there is only one database with a ORACLE_HOME of ‘/opt/app/oracle/product/12.1.0.2/db_1’.

Larry Catt
OCP

11g, 12c, Administration

Current sid and serial# of a session from SQL*PLUS

Leave a comment

1. From within Oracle SQL*Plus session.

2. Using the userenv(‘SESSIONID’) to retrieve current sid and serial#.

select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);

SQL> select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);

SID SERIAL#
———- ———-
408 16328

SQL>

3. Note: That logging out of SQL*Plus session or reconnecting will change your SID and SERIAL#.

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, 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

Oracle

Recreating OEM in 11g on MS Windows

Leave a comment

There are multiple reasons to recreate your Enterprise Manager installation on a Oracle RDBMS system. The following procedures provide the steps to accomplish this task on a MS Windows OS.

1. Remove SYSMAN from database if he exists by using emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -deconfig dbcontrol db -repos drop

EXAMPLE:
—————————————
C:\Windows\system32>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Apr 20, 2013 10:38:10 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
———————————————————————-
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————-
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:38:43 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_38_10.log.
Apr 20, 2013 10:38:45 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Apr 20, 2013 10:39:17 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …
Apr 20, 2013 10:40:29 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 20, 2013 10:40:30 AM

C:\Windows\system32>
——————————————-

2. Create OEM repository by using the emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -repos create

EXAMPLE:
——————————————-
C:\Windows\system32>emca -repos create

STARTED EMCA at Apr 20, 2013 10:42:48 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Password for SYS user:
Password for SYS user: password

Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:43:12 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_42_48.log.
Apr 20, 2013 10:43:14 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
Apr 20, 2013 10:45:30 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 20, 2013 10:45:30 AM

C:\Windows\system32>
——————————————-

3. Finally, configure EM Grid Control using the emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -config dbcontrol db

EXAMPLE:
——————————————-
C:\Windows\system32>emca -config dbcontrol db

STARTED EMCA at Apr 20, 2013 10:32:32 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Listener ORACLE_HOME [ C:\oracle\product\11.2.0\dbhome_orcl ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
—————————————————————–

You have specified the following settings

Database ORACLE_HOME ……………. C:\oracle\product\11.2.0\dbhome_orcl

Local hostname ……………. orcl_SERVER-PC.CATT.Net
Listener ORACLE_HOME ……………. C:\oracle\product\11.2.0\dbhome_orcl
Listener port number ……………. 1521
Database SID ……………. orcl1
Email address for notifications ……………
Outgoing Mail (SMTP) server for notifications ……………

—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:33:00 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_32_31.log.
Apr 20, 2013 10:33:06 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepo
sitory
INFO: Uploading configuration data to EM repository (this may take a while) …
Apr 20, 2013 10:33:41 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 20, 2013 10:33:43 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) …
Apr 20, 2013 10:33:51 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 20, 2013 10:33:51 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Apr 20, 2013 10:34:37 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 20, 2013 10:34:37 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://orcl_SERVER-PC.CATT.Net:5501/em < <<<<<<<<<< Apr 20, 2013 10:34:38 AM oracle.sysman.emcp.EMDBPostConfig invoke WARNING: ************************ WARNING ************************ Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: C:/orac le/product/11.2.0/dbhome_orcl/orcl_SERVER-PC.CATT.Net_orcl/sysman/config/emkey. ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost. *********************************************************** Enterprise Manager configuration completed successfully FINISHED EMCA at Apr 20, 2013 10:34:38 AM C:\Windows\system32>
——————————————-

4. Now attempt to access the EM console through the URL given in the INFO section of the last command. In this example, we access the EM console at URL:
https://orcl_SERVER-PC.CATT.Net:5501/em

Larry Catt, OCP

11g, Oracle, Oracle 10g, RAC, utility

Use of rconfig to modify a single instance 11g database to RAC

Leave a comment

This article covers the conversion of a pre-existing Oracle 11g single instance database into a two node RAC. The following assumptions are made for this procedure to execute correctly:
1. Oracle 11g GRID has been installed on both RAC nodes.
2. Oracle 11g RDBMS has been installed on both RAC nodes.
3. Oracle 11g single instance database has been copied to RAC node 1 and operates correctly as a single instance database.

1. Logon to node 1 of your Oracle RAC server as the RDBMS owner.

2. Copy the file $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml

[oracle@mylinux1 dbhome_1]$ cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml orcl.xml
[oracle@mylinux1 dbhome_1]$

2. The sample XML file gives detailed instruction on how to edit the file for your specific use. In this example edit the following lines:

SourceDBHome = Single instance Oracle Home directory
TargetDBHome = RAC instance Oracle Home, this could be the same as the single instance Oracle Home directory
SourceDBInfo SID= SID of the Source database.
Password = SYS password
Node name= node1 name, normally this is the machine name.
Node name= node2 name, normally this is the machine name.
InstancePrefix = prefix of your RAC database, normally this is equal to the SID of your source database.
SharedStorage type=”ASM” or “OCFS”

3. Ensure that the single instance database is up and running.

[oracle@mylinux1 dbhome_1]$ ps -ef|grep smon
oracle 18508 1 0 02:12 ? 00:00:00 ora_smon_orcl
oracle 26774 7777 0 02:46 pts/1 00:00:00 grep smon
[oracle@mylinux1 dbhome_1]$

4. Execute the rconfig command and pass your edited xml file to it.
[oracle@mylinux1 bin]$ ./rconfig ./orcl.xml
Converting Database “orcl” to Cluster Database. Target Oracle Home: /u01/app/oracle/product/11.2.0/dbhome_1. Database Role: PRIMARY.
Setting Data Files and Control Files
Adding Database Instances
Adding Redo Logs
Enabling threads for all Database Instances
Setting TEMP tablespace
Adding UNDO tablespaces
Adding Trace files
Setting Flash Recovery Area
Updating Oratab
Creating Password file(s)
Configuring Listeners
Configuring related CRS resources
Starting Cluster Database
< ?xml version=”1.0″ ?>

Operation Succeeded

/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@mylinux1 bin]$>

5. Now the database can be confirmed in rac mode on both nodes.

[oracle@mylinux1 bin]$ ps -ef|grep smon
oracle 16544 1 0 03:04 ? 00:00:00 ora_smon_orcl1
oracle 21072 7777 0 03:07 pts/1 00:00:00 grep smon
[oracle@mylinux1 bin]$

[root@mylinux2 ~]# ps -ef|grep smon
oracle 27298 1 0 03:03 ? 00:00:00 ora_smon_orcl2
root 29474 23387 0 03:07 pts/4 00:00:00 grep smon
[root@mylinux2 ~]#

This completes configuration of single instance to Oracle 11g RAC.

Larry J. Catt, OCP
oracle@allcompute.com
www.allcompute.com

error

RMAN utility produces error LFI-00005 and LFI-00004

Leave a comment

Error: During use of RMAN utility the following is generated.

RMAN-00554: initialization of internal recovery manager package failed
RMAN-03000: recovery manager compiler component initialization failed
RMAN-06001: error parsing job step library
RMAN-01006: error signalled during parse
RMAN-00600: internal error, arguments [8083] [] [] [] []
LFI-00005: Free some memory failed in lfibrdt().
LFI-00004: Call to lfibgl() failed.
[oracle@mylinux1 bin]$

Check: Look at the storage configuration of both the RMAN binaries and location of backups. In this example the storage was OCFS, which can be used for both the backup pieces and the binaries, but not with the tag – datavolume.


/dev/mapper/oracle_home /rac1 ocfs2 _netdev,datavolume,nointr 0 0

1. Logon to the Oracle server as the root user.

2. Dismount the storage in question.

umount /rac1

3. Mount the storage without the datavolume tag.

mount /dev/mapper/oracle_home /rac1 ocfs2 _netdev,nointr 0 0

4. Update your /etc/fstab file with new entries.

This completes resolution of RMAN error due to use of OCFS storage.

Larry Catt, OCP
oracle@allcompute.com
www.allcompute.com

dbca, utility

Remove Oracle Database with DBCA from command line

Leave a comment

The following procedure shows how to remove an Oracle database with dbca utility from the command line.

1. Logon as the oracle software owner.

[root@mylinux2 root]# su – oracle
[oracle@mylinux2 ~]$

2. Change directories to $ORACLE_HOME/bin

[oracle@mylinux2 ~]$ cd $ORACLE_HOME/bin
[oracle@mylinux2 bin]$

3. Obtain the ORACLE_SID of the database you wish to delete.

[oracle@mylinux2 bin]$ ps -ef|grep smon
oracle 4746 1 0 Feb09 ? 00:00:00 ora_smon_orcl
oracle 7839 7796 0 00:39 pts/1 00:00:00 grep smon
[oracle@mylinux2 bin]$

4. Obtain the userid and password of a user with sysdba privileges, normally this would be sys.

5. Logon to the database if open with the immediate option and exit.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mylinux2 bin]$

6. Ensure that no oracle database processes are running for the ORACLE_SID you wish to delete


[oracle@mylinux2 bin]$ ps -ef|grep orcl
oracle 11485 7796 0 01:46 pts/1 00:00:00 grep orcl
[oracle@mylinux2 bin]$

7. Execute the command dbca -deleteDatabase -sourceDB -sysDBAUserName -sysDBAPassword


[oracle@mylinux2 bin]$ ./dbca -silent -deleteDatabase -sourceDB orcl – sysDBAUserName larry -sysDBAPassword larry
Connecting to database
4% complete
9% complete
14% complete
19% complete
23% complete
28% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instance and datafiles
76% complete
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/orcl.log” for
further details.
[oracle@mylinux2 bin]$

This completes deleting an oracle database from command line with dbca.

Larry J. Catt, OCP
oracle@allcompute.com
www.allcompute.com