How to fix /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

During upgrade from 11.2.0.3 to 11.2.0.4 I got  /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

To solve this problem you should install glibc.i686 packages.

yum install glibc.i686

 

Thanks to Giorgi Peikrishvili for this case 🙂

EM: CPU chart Error: Invalid BoundDataSource: no class, method or dataSource provided.

Error:

CPU_Chart

Also no other tabs are available to click.

Reason:

One of the reason could be that you converted single instance database to RAC.

or you created EM repository without indicating -cluster option.

run:

emca -config dbcontrol db -repos recreate -cluster

answer the questions carefully 🙂

My settings:

You have specified the following settings

Database ORACLE_HOME ……………. /u01/app/oracle/product/11.2.0/db_1

Database instance hostname ……………. Listener ORACLE_HOME ……………. /u01/app/11.2.0/grid
Listener port number ……………. 1521
Cluster name ……………. oracle-db
Database unique name ……………. orcl
Email address for notifications …………… mariam.kupa@gmail.com
Outgoing Mail (SMTP) server for notifications …………… mail.ar.ge
ASM ORACLE_HOME ……………. /u01/app/11.2.0/grid
ASM port ……………. 1521
ASM user role ……………. SYSDBA
ASM username ……………. ASMSNMP

 

Linker error while installing Oracle 11g /u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk

During installing 11g on OEL7 I got error when linking libraries.

To solve you should change syntax in the following file:

vi $ORACLE_HOME/sysman/lib/ins_emagent.mk

Change the following line from

$(MK_EMAGENT_NMECTL)

to:

$(MK_EMAGENT_NMECTL) -lnnz11

and click retry on error window to continue?

Good Luck!

Oracle proxy user, create dblink, job in different schema

This post describes how database administrator can use to log on to a user when the password is not known.

Sometimes DBAs want to create database links in another schema, that is not possible just with create database link username.database_link_name, but the connected user should be the same as the owner of the link.
Also if DBA want the job, during creation, to take different user credentials than they are connected there are two methods(maybe more):

1. By resetting user password

  1.  -- Get user password hash
    SELECT password
    FROM   sys.user$
    WHERE  name = 'MARI';
    
    PASSWORD
    ------------------------------
    D456844C36682A67
    -- Reset the password
    ALTER USER MARI IDENTIFIED BY 123;
  2. --Connect by mari and do what you want.
    CONN MARI/123
  3. --Return back mari's password
    CONN / AS SYSDBA
    ALTER USER MARI IDENTIFIED BY VALUES 'D456844C36682A67';

2. By using proxy user. Assume that we have DBA user named my_dba, by which we will be able to connect to the database as MARI without knowing MARI password.

  1. ALTER USER MARI GRANT CONNECT THROUGH my_dba;
  2. CONN my_dba[MARI]/my_dba_password
  3. SHOW USER
    USER is "MARI"

    Do what you want with mari user.

  4. Proxy users can be identified using the PROXY_USERS view.
    SELECT * FROM proxy_users;
  5. –To revoke permission
    ALTER USER scott REVOKE CONNECT THROUGH test_user;

ORA-01031: Insufficient Privileges while creating mview in different schema.

I will discuss one of the reason, why creation of materialized view in another schema can cause ORA-01031 error.

SQL> conn test/test

SQL> create materialized view test1.mview1
as select * from test1.table1;

ORA-01031: insufficient privileges

sqlplus / as sysdba

SQL> grant create table to test;

SQL> conn test1/test1

SQL> create materialized view test1.mview1
as select * from test1.table1;

Materialized view created.

In my case test user did not have create table permission. This permission is necessary because during creation materialized view , additional table is created automatically.

SQL> select * from dba_objects where owner=’TEST’;

OBJECT_NAME OWNER OBJECT_TYPE
———– ——- ————-
mv_table1 TEST TABLE
mv_table1 TEST MATERIALIZED VIEW

 

 

 

DROP COLUMN raises ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist

Today I came across with the strange error on developing environment , not in production fortunately 🙂

I have a table

create table ONLINECREDIT.MERCHANTS
(
id NUMBER(9) not null,
PASSWORD2 VARCHAR2(20)
)

I was trying to drop column:

alter table ONLINECREDIT.MERCHANTS drop  COLUMN PASSWORD2;

got error:

ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

1. ONLINECREDIT.MERCHANTS exists ,
2. Table name is not case sensitive
3. There is not trigger on the column, that may cause the problem.. no system trigger, I mean after drop, before drop or after ddl trigger should be causing the problem..

so what else?

I did the following:

alter table ONLINECREDIT.MERCHANTS add (test number);

alter table ONLINECREDIT.MERCHANTS drop column test;

And it works.. so newly added columns can be dropped. So there is some problem with system tables…and YES it is true!

Let’s find:

–We are planning to trace our session. For easily finding trace file , run the following:

ALTER SESSION SET TRACEFILE_IDENTIFIER = “MY_TEST_SESSION”;

–Enable tracing

begin
dbms_session.session_trace_enable;
END;

–Try to run drop statement again, to get error

alter table ONLINECREDIT.MERCHANTS drop COLUMN PASSWORD2;

–Disable tracing

BEGIN
dbms_session.session_trace_disable;
END;

You can also find your trace file by running the following query, from the same session:

SELECT value
FROM v$diag_info
WHERE name = ‘Default Trace File’;

Result
—————-
/u01/app/oracle/diag/rdbms/lbankdev/LBANKDEV/trace/LBANKDEV_ora_6038_MY_TEST_SESSION.trc

–From OS
–Go to the tracing directory

cd /u01/app/oracle/diag/rdbms/lbankdev/LBANKDEV/trace

— And make trace file readable using tkprof

tkprof output=prof_readme.txt trace=LBANKDEV_ora_6038_MY_TEST_SESSION.trc

–Open file using notepad. You will find the error at the beginning:

The following statement encountered a error during parse:

update radm_mc$ set intcol#=intcol#-:1 where (obj#=:2 or obj#=:3) and intcol#>:4

Error encountered: ORA-00942

–Check if the table exists

SELECT * FROM DBA_objects
WHERE object_name=’RADM_MC$’

Nothing is returned.

Now the solution:

I’ve connected to the production server and generated METADATA for this sys.RADM_MC$  [note table doesn’t contain data.. for other tables containing data this method is not preferable :), it depends ]

— Create table
create table SYS.RADM_MC$
(
obj# NUMBER not null,
intcol# NUMBER not null,
mfunc NUMBER not null,
mparams VARCHAR2(1000),
regexp_pattern VARCHAR2(512),
regexp_replace_string VARCHAR2(4000),
regexp_position NUMBER,
regexp_occurrence NUMBER,
regexp_match_parameter VARCHAR2(10),
mp_iformat_start_byte INTEGER,
mp_iformat_end_byte INTEGER,
mp_oformat_start_byte INTEGER,
mp_oformat_end_byte INTEGER,
mp_maskchar_start_byte INTEGER,
mp_maskchar_end_byte INTEGER,
mp_maskfrom INTEGER,
mp_maskto INTEGER,
mp_datmask_mo INTEGER,
mp_datmask_d INTEGER,
mp_datmask_y INTEGER,
mp_datmask_h INTEGER,
mp_datmask_mi INTEGER,
mp_datmask_s INTEGER
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
— Create/Recreate indexes
create index SYS.I_RADM_MC1 on SYS.RADM_MC$ (OBJ#)
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index SYS.I_RADM_MC2 on SYS.RADM_MC$ (OBJ#, INTCOL#)
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

and run on developing database.. I did it because this table doesn’t contain data. It is empty.

So after creating this system table on developing DB , I was able to drop the column.

alter table ONLINECREDIT.MERCHANTS drop  COLUMN PASSWORD2;

 

Oracleasm deletedisk failed Unable to clear disk

I’ve deleted disk from ASM diskgroup:

ALTER DISKGROUP DATA01 DROP DISK DISK6;

After it finished dropping the disk and all extents were moved to the remaining disks, I tried to delete disk from the ASM library and return it to the system:

oracleasm deletedisk -v DISK6

Clearing disk header: oracleasm-write-label: Unable to open device "/dev/oracleasm/disks/DISK6": Device or resource busy
failed
Unable to clear disk "DISK6"

The reason is that oracle leaves the disk open even after removing it from the diskgroup.

There are two solutions:________________________________________________

1. One for  24/7 databases:

–Find the device name

[root@r2n1 ~]# blkid |grep oracleasm

/dev/sdd1: LABEL="DISK6" TYPE="oracleasm"

–Clear the device header manually

Make sure, you use the right device name. If you specify wrong device name, the operation is unrecoverable.

su –

dd if=/dev/zero of=/dev/sdd1 bs=1024 count=100

or

dd if=/dev/zero of=/dev/oracleasm/disks/DISK6 bs=1024 count=100

If you have RAC system , run dd on all nodes.

Another solution:

1. Reboot the server

# reboot

Or

Restart database instance and CRS services

–Shutdown database

$ export ORACE_SID=orcl
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ sqlplus / as sysdba
SQL> shutdown immediate;

–Shutdown CRS services

./crsctl stop crs

–Start CRS services

./crsctl start crs

–Start database

$ export ORACE_SID=orcl
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
$ sqlplus / as sysdba
SQL> startup;

2. Then delete the disk.

oracleasm deletedisk -v DISK6

Add/Drop ASM disks to DISKGROUP on RAC(or Standalone)

Note: The steps are described for RAC, but you can easily guess what are the steps for the standalone database.

1. First of all find the disk or partition name, that should be added to the ASM.

fdisk -l

My disk partition name is /dev/sdi1.

2. Assign the disk to ORACLEASM.

–On node1

/etc/init.d/oracleasm createdisk DISK7 /dev/sdi1

3. Scan disks in ALL NODES and list them to check if is presented.

–On node1

/etc/init.d/oracleasm scandisks
/etc/init.d/oracleasm listdisks

–On node2

/etc/init.d/oracleasm scandisks
/etc/init.d/oracleasm listdisks

4. Change the environment to the grid infrastructure, by setting ORACLE_SID to +ASM and so on :

$ . oraenv
ORACLE_SID = [media1] ? +ASM1
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle

# Connect as an SYSASM

sqlplus / as sysasm

Note: If you don’t remember the password for the sysasm user see How to reset SYSASM password.

# Find the diskgroup name

SQL> select name from v$asm_diskgroup;

NAME
——————————
DATA01

# Increase power limit, if you want, to complete rebalance operation in a short time.

SQL>  alter system set asm_power_limit=10

# Indicate disks location by the parameter asm_diskstring

SQL> alter system set asm_diskstring=’ORCL:DISK*’

SQL> alter diskgroup DATA01 add disk ‘ORCL:DISK7’;

It will do the rebalance automatically.

# To drop the disk , do the following:

SQL >  alter diskgroup DATA01 drop disk DISK7;

It will rebalance first and then drops the disk automatically.

You can see the current operation in v$asm_operation view.

Note: Until the view v$asm_operation contains a record you are able to undrop the disks by the following way:

SQL> alter diskgroup DATA01 undrop disks;

If the operation is already completed , you are not able to undrop the disk . But you can re-add the disk , if you want.

That is all.

Upgrade 10g XE to 11g XE

I’ve had a lot of trouble with 10g XE. Especially with its upgrading process to 11g XE. So I want to share my experience with you.

There are at least 2 options to do the upgrade.

First one is backup/restore and second one is export/import.

Note: I ‘ve installed 10g XE and 11g XE on separate servers(because couldn’t handle to install them together)

Note2: The 1st one doesn’t worked for me and is described shortly. The 2nd  one worked for me and is fully descriptive.

1. Take utlu112i.sql script from 11g XE and place it on 10g XE server.

2. Run this script on 10g XE.

Go to the gen_inst.sql file location or indicate a full path to the file:

sqlplus sys as sysdba @gen_inst.sql

3. Backup 10g XE database.

4. Restore to 11g XE

5. Startup upgrade.

6. Run the following scripts on 11g XE.

–Upgrade

sqlplus sys as sysdba @catupgrd.sql

–Recompile

sqlplus sys as sysdba @utlrp.sql

BUT these steps that are described on most of the blogs and sites did not work for me.

Another option that worked for me is export/import.

1. Take gen_inst.sql script from 11g XE.

2. Place and run this script on 10g XE.

sqlplus sys as sysdba @gen_inst.sql

It will generate install.sql, gen_apps.sql and other .sql files

3. On 10g XE

CREATE DIRECTORY DUMP_DIR AS ‘C:\oraclexe\dump_dir’;

GRANT read, write ON DIRECTORY DUMP_DIR TO public;

expdp ‘sys/passwd as sysdba’ full=Y EXCLUDE=SCHEMA:\"LIKE \’APEX_%\’\",SCHEMA:\"LIKE \’FLOWS_%\’\" directory=DUMP_DIR dumpfile=DB10G.DMPlogfile=expdpDB10G.log

expdp ‘sys/passwd as sysdba’ TABLES=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ directory=DUMP_DIR dumpfile=DB10G2.dmp logfile=expdpDB10G2.log

Note: if ‘sys/passwd as sysdba’  doesn’t work try without it and then enter the credentials.

expdp  full=Y EXCLUDE=SCHEMA….
Username: sys@XE as sysdba
Password: *****

4. Import to 11g XE

CREATE DIRECTORY DUMP_DIR AS ‘C:\oraclexe\dump_dir’;
GRANT read, write ON DIRECTORY DUMP_DIR TO public;

impdp  ‘sys/passwd as sysdba’ full=Y directory=DUMP_DIR dumpfile=DB10G.DMP logfile=expdpDB10G1.log

impdp  ‘sys/passwd as sysdba’ directory=DUMP_DIR TABLE_EXISTS_ACTION=APPEND  TABLES=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ dumpfile=DB10G2.DMP logfile=expdpDB10G1b.log

5. Run install.sql. It will run other scripts itself.

sqlplus sys as sysdba @install.sql

Configure Oracle database to use SSL with self-signed certificate

You can use Oracle SSL to encrypt data exchange between Oracle database and Oracle client.

For the first time, let’s check that data is not encrypted by default when the client is selecting some info from the database.

For demonstration of this I will use WireShark.

Checking…

1. Run WireShark and highlight network card by which you use to connect to the database. For me it is “”Local Area Connection 3”.

image

2. click Capture Options and in Capture Filter write :

src host 192.168.171.153 and dst host 192.168.59.51 and tcp port 1521

Description: src host –is client ip
dst host – is database ip
tcp port – is the database port number

This means to capture requests from client(192.168.171.153) to the database(192.168.59.51) using tcp port 1521.

3. click Start.

4. Connect to the database and run testing select:

select *
from dual

WireShark with a lot of rows will contain the row consisting the following text, that contains our select

image

Configuring SSL…

Wallet configuration on DB server

0. Lets create working folder called /0 for simplicity.

mkdir /0

chmod –R 777 /0

1. Create certification request using Oracle Wallet Manager (/u01/app/oracle/product/11.2.0/db_1/bin/owm).

su – oracle

owm

image

2.  Wallet->New

image

3.  Enter the password, which protects wallet from opening. OK

4. Click Yes(for creating necessary folders for wallet)

image

5. Click Yes(for creating certification request)

image

6. Fill the items by your info.

7. click OK

8. Highlight certification request and from menu choose Operations->Export Certificate Request

image

9. Save the file with the extension .csr in /0 folder

image

10. Download ssl.ca-0.1.tar.gz file for to generate trusted and user certificates.

Here I want to note that trusted certificate is like a public key which will be sent to the client and user certificate is like a private key which has only the database server.

Move downloaded file to the /0 folder and extract. Then move certification request to the extracted folder.

cd /0/
tar -xvf ssl.ca-0.1.tar.gz
mv /0/CerReq.csr  /0/ssl.ca-0.1/

1.10  Create a self-signed root certificate by running the new-root-ca.sh script. This will create a file called ca.crt

cd /0/ssl.ca-0.1/

./new-root-ca.sh
No Root CA key round. Generating one
Generating RSA private key, 1024 bit long modulus
…………………++++++
………………………………………………………..++++++
e is 65537 (0x10001)
Enter pass phrase for ca.key:enter the password
Verifying – Enter pass phrase for ca.key:enter the password

Self-sign the root CA…
Enter pass phrase for ca.key:enter the password
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter ‘.’, the field will be left blank.
—–
Country Name (2 letter code) [MY]:GE
State or Province Name (full name) [Perak]:.     <- here is written dot (.)
Locality Name (eg, city) [Sitiawan]:Tbilisi
Organization Name (eg, company) [My Directory Sdn Bhd]:MjM
Organizational Unit Name (eg, section) [Certification Services Division]:IT
Common Name (eg, MD Root CA) []:ca_root
Email Address []:mariam.kupa@gmail.com

1.11 Create the self-signed server certificate by running the sign-server-cert.sh script.

./sign-server-cert.sh CerReq
CA signing: CerReq.csr -> CerReq.crt:
Using configuration from ca.config
Enter pass phrase for ./ca.key:enter the password for the ca
Check that the request matches the signature
Signature ok
The Subject’s Distinguished Name is as follows
commonName            : .PRINTABLE:’MjM’
organizationalUnitName: .PRINTABLE:’IT’
organizationName      : .PRINTABLE:’MjM’
localityName          : .PRINTABLE:’Tbilisi’
countryName           : .PRINTABLE:’GE’
Certificate is to be certified until Nov  5 12:40:48 2014 GMT (365 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
CA verifying: CerReq.crt <-> CA cert
CerReq.crt: OK

1.12 From the wallet manager import these certificates.

image

Operations-> Import Trusted Certificate

image

Select a file that contains the certificate. click OK.

image

Choose ca.crt

image

image

Choose CerReq.crt

Save the wallet from the menu Wallet->Save

image

click OK.

image

Check Auto Login and Exit.

Oracle Advanced Security and listener Configuration on DB Server

1.

su – oracle

netmgr

Choose Profile->Oracle Advanced Security-> SSL

choose server

image

Fill the items as it is shown on the picture, except that you should indicate your own wallet location. For me it is /u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle

Click File->Save Network Configuration.

2. Change listener entry on the database server by adding port 2484 using protocol TCPS :

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)

SSL_CLIENT_AUTHENTICATION = FALSE

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/owm/wallets/oracle)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server.gov.ge)(PORT = 1521))
)
(DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = server.gov.ge)(PORT = 2484))
    )
)

ADR_BASE_LISTENER = /u01/app/oracle

Look at the highlighted section , I am using 2484 as a port  number ,which is Oracle recommended port for SSL , and  TCPS as a protocol .

Restart the listener

lsnrctl stop

lsnrctl start

Client Configuration

1.  Do the same steps as we did on the database server.

1.1 Create new wallet

1.2 Create certificate request

1.3 Copy trusted certificate, which we have generated on the database server

1.4 Import only trusted certificate.

1.5 Save

1.6  Check Auto Login and Save.

2. Run Network Manager

2.1 Profile->Oracle Advanced Security –>SSL

choose client.

image

Fill items as it is shown on the picture(indicate your wallet location)

3. Configure tnsnames.ora by the following entry:

orcl_ssl=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = server.gov.ge)(PORT = 2484))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Check again by WhireShark.

You will see that no rows will contain clear words. Everything is encrypted.

Note: If you have any certification validation failure errors, first try to stop listener and start again..or write me on the comment and I will try to help you.