Oracle, Linux, AWS, Azure, GCP
August 25, 2016 1 Comment
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 🙂
June 10, 2016 Leave a comment
Error:
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
February 5, 2016 Leave a comment
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!
January 15, 2016 2 Comments
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
-- Get user password hash SELECT password FROM sys.user$ WHERE name = 'MARI'; PASSWORD ------------------------------ D456844C36682A67
-- Reset the password ALTER USER MARI IDENTIFIED BY 123;
--Connect by mari and do what you want. CONN MARI/123
--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.
ALTER USER MARI GRANT CONNECT THROUGH my_dba;
CONN my_dba[MARI]/my_dba_password
SHOW USER USER is "MARI"
Do what you want with mari user.
SELECT * FROM proxy_users;
ALTER USER scott REVOKE CONNECT THROUGH test_user;
August 12, 2015 Leave a comment
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
June 26, 2015 Leave a comment
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;
March 6, 2014 Leave a comment
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
January 17, 2014 Leave a comment
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.
November 21, 2013 Leave a comment
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
November 6, 2013 10 Comments
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”.
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
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
2. Wallet->New
3. Enter the password, which protects wallet from opening. OK
4. Click Yes(for creating necessary folders for wallet)
5. Click Yes(for creating certification request)
6. Fill the items by your info.
7. click OK
8. Highlight certification request and from menu choose Operations->Export Certificate Request
9. Save the file with the extension .csr in /0 folder
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 passwordSelf-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.
Operations-> Import Trusted Certificate
Select a file that contains the certificate. click OK.
Choose ca.crt
Choose CerReq.crt
Save the wallet from the menu Wallet->Save
click OK.
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
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.
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.
DBA Knowledge Base 