Monthly Archives: May 2014

su: cannot set user id: Resource temporarily unavailable

Today my friend a customer complaining about his DB that nobody can login anymore. The number of max sessions (in the DB) was set to 4096 and there were about 2048 current seesion on the DB. So, what was wrong?I decided to login as root into the DB server which worked wihtout any issue. The system […]

Updating the NUM_ROWS in USER_TABLES in Oracle

Good or bad, I use the NUM_ROWS values in the USER_TABLES table to help filter lists of tables when presenting them to the user. If I have a database and only about 25% of tables have records (and there are thousands of tables), then it is a lot nicer to only sift through the tables […]

Shutting down background process GTXn

In RAC Database DBA may found Auto-tuning: Shutting down background process GTXn in alert log file, which is non corruptive. GTXn Global Transaction Process Provides transparent support for XA global transactions in an Oracle RAC environment These processes help maintain the global information about XA global transactions throughout the cluster. Also, the processes help perform […]

Database Vault / Verinin Güvenliği

Database Vault / Verinin Güvenliği from Anar Godjaev

WARNING: Detected too many memory locking problems

My alert log file entries: Current log# 3 seq# 4645 mem# 0: <DATAFILE_LOCATION>/redo03.log Wed May 21 21:19:53 2014 WARNING: Detected too many memory locking problems. WARNING: Performance degradation may occur. The real cause of those warnings is the file oradism located on $ORACLE_HOME/bin folder was lacking permission. As per meta link note 374367.1, issuing the […]

ORA-00600: internal error code, arguments: [kzvdvesjc – kzvjobinfo not valid], [], [], [], [], [], [], [], [], [], [], [] ORA-01403: no data found

Hi, Internal error in Oracle DB version This is our test database. I have check alert log and see following error: Errors in file /u01/app/oracle/diag/rdbms/azkkuat/AZKKUAT/ trace/AZKKUAT_j000_14225.trc (incident=189939): ORA-00600: internal error code, arguments: [kzvdvesjc – kzvjobinfo not valid], [], [], [], [], [], [], [], [], [], [], [] ORA-01403: no data found Incident details in: /u01/app/oracle/diag/rdbms/azkkuat/AZKKUAT/incident /incdir_189939/AZKKUAT_j000_14225_i189939.trc Use ADRCI or […]

Identifying Open Uncommitted Transactions

select s.INST_ID, s.SID, s.SERIAL#,s.USERNAME,s.MACHINE from gv$transaction t , gv$session s where t.INST_ID = s.INST_ID and t.ses_addr = s.SADDR;

waiting sessions across a RAC database

select dl.inst_id, s.sid, p.spid, dl.resource_name1, decode(substr(dl.grant_level,1,8),’KJUSERNL’,’Null’,’KJUSERCR’,’Row-S (SS)’, ‘KJUSERCW’,’Row-X (SX)’,’KJUSERPR’,’Share’,’KJUSERPW’,’S/Row-X (SSX)’, ‘KJUSEREX’,’Exclusive’,request_level) as grant_level, decode(substr(dl.request_level,1,8),’KJUSERNL’,’Null’,’KJUSERCR’,’Row-S (SS)’, ‘KJUSERCW’,’Row-X (SX)’,’KJUSERPR’,’Share’,’KJUSERPW’,’S/Row-X (SSX)’, ‘KJUSEREX’,’Exclusive’,request_level) as request_level, decode(substr(dl.lockstate,1,8),’KJUSERGR’,’Granted’,’KJUSEROP’,’Opening’, ‘KJUSERCA’,’Cancelling’,’KJUSERCV’,’Converting’) as lockstate, s.sid, sw.event, sw.seconds_in_wait sec from gv$dlm_locks dl, gv$process p, gv$session s, gv$session_wait sw where blocked = 1 and (dl.inst_id = p.inst_id and = p.spid) and (p.inst_id = s.inst_id and […]

UX: /usr/sbin/useradd: ERROR: Inconsistent password files. See pwconv(1M).

Solution:  Found an entry in /etc/user_attr oracle::::project=oracledeleted that line  and problem solved

how to clone databases using RMAN

$ export ORACLE_SID=OCM_PROD $ rman target / rman> run {allocate channel ch1 device type disk format ‘/backup/OCM_PROD/%_U’; backup database plus archivelog; } On successful completion the backupsets created can be used for cloning OCM_PROD to OCM_TEST. Cloning of OCM_TEST database from OCM_PROD using RMAN $ cd $ORACLE_HOME/dbs orapwd password=oracle entries=20 file=orapwOCM_TEST create initOCM_TEST.ora file with […]

Moving Control Files

Moving Datafiles while the Instance is Mounted

ORA-00305: log 1 of thread 1 inconsistent; belongs to another database

SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 19635 Session ID: 105 Serial number: 3 check alert.log : ORA-00305: log 1 of thread 1 inconsistent; belongs to another database ORA-00312: online log 1 thread 1: ‘/data/PREAZKKDB/redolog/redo01.log’ Solution: SQL>Shutdown immediate SQL>startup mount SQL> recover database until […]

metadata expdp & impdp

Take export of metadata of schema only using expdpexpdp directory=exp_dp dumpfile=j.dmp content=metadata_only schemas=<schema_name>After that create sqlfile using impdp -impdp directory=exp_dp dumpfile=j.dmp logfile=imp_j.log sqlfile=j.sql include=viewYou can filter the views only like above, you can get everything in the schema as follows:impdp directory=exp_dp dumpfile=j.dmp logfile=imp_j.log sqlfile=j.sql

Moving Control Files

sqlplus “/ as sysdba” SQL> shutdown immediate bash-3.2$ mv /u01/app/oracle/oradata/PREAZKKDB/control01.ctl /data/PREAZKKDB/controlfile/control01.ctl bash-3.2$ mv /u01/app/oracle/fast_recovery_area/PREAZKKDB/control02.ctl /data/PREAZKKDB/controlfile/control02.ctl sqlplus / as sysdba; create pfile from spfile; exit bash-3.2$ vi initPREAZKKDB.ora “initPREAZKKDB.ora” 27 lines, 1048 characters PREAZKKDB.__db_cache_size=3388997632 PREAZKKDB.__java_pool_size=16777216 PREAZKKDB.__large_pool_size=16777216 PREAZKKDB.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment PREAZKKDB.__pga_aggregate_target=158779572224 PREAZKKDB.__sga_target=4294967296 PREAZKKDB.__shared_io_pool_size=0 PREAZKKDB.__shared_pool_size=788529152 PREAZKKDB.__streams_pool_size=16777216 *.audit_file_dest=’/u01/app/oracle/admin/PREAZKKDB/adump’ *.audit_trail=’db’ *.compatible=’′ *.control_files=’/data/PREAZKKDB/controlfile/control01.ctl’, ‘/data/PREAZKKDB/controlfile/control02.ctl’ *.db_block_size=8192 *.db_domain=” *.db_name=’PREAZKKD’ *.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’ *.db_recovery_file_dest_size=4322230272 *.db_unique_name=’PREAZKKDB’ […]

Moving Online Redo Log Files

SQL> sqlplus “/ as sysdba”; SQL>shut immediate SQL> exit bash-3.2$ mv /u01/app/oracle/oradata/PREAZKKDB/redo03.log /data/PREAZKKDB/redolog/redo03.log bash-3.2$ mv /u01/app/oracle/oradata/PREAZKKDB/redo02.log /data/PREAZKKDB/redolog/redo02.log bash-3.2$ mv /u01/app/oracle/oradata/PREAZKKDB/redo01.log /data/PREAZKKDB/redolog/redo01.log bash-3.2$ sqlplus “/ as sysdba”; SQL> startup mount SQL> alter database rename file ‘/u01/app/oracle/oradata/PREAZKKDB/redo03.log’ to ‘/data/PREAZKKDB/redolog/redo03.log’; SQL> alter database rename file ‘/u01/app/oracle/oradata/PREAZKKDB/redo02.log’ to ‘/data/PREAZKKDB/redolog/redo02.log’; SQL> alter database rename file ‘/u01/app/oracle/oradata/PREAZKKDB/redo01.log’ to ‘/data/PREAZKKDB/redolog/redo01.log’; SQL> alter […]

Moving Datafiles while the Instance is Mounted

Shutdown the instance Use operating system commands to move or rename the files(s). Mount the database and use the ALTER DATABASE to rename the file within the database. Opening the Database   sqlplus “/ as sysdba” SQL> shutdown immediate SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf SQL> startup mount SQL> alter database rename file ‘/u05/app/oradata/ORA920/indx01.dbf’ to ‘/u06/app/oradata/ORA920/indx01.dbf’; Do […]

Install Database Software

[oracle@crs1 cluvfy]$ /u01/software/clusterware/cluvfy/ stage -pre dbinst -n crs1,crs2 -verbose Ignore Linux package version warnings [oracle@crs1 ~]$ . /home/oracle/.bash_profile [oracle@crs1 ~]$ echo $ORACLE_HOME /u01/app/oracle/product/10.2.0/db_1 [oracle@crs1 ~]$ echo $ORACLE_SID crsdb1 ƒ Welcome Screen: Click Next ƒ Select Installation Type: Enterprise Edition (1.24GB) ƒ Select Configuration Option: Install database Software only   The file “coraenv” already exists in […]

Database in Archive log Mode

Database in Archive log Mode, Flashback On and Force Logging i. [oracle@crs1 ~]$ srvctl STOP DATABASE -d crsdb ii. [oracle@crs1 ~]$ sqlplus / AS SYSDBA iii. SQL> STARTUP MOUNT EXCLUSIVE; iv. SQL> ALTER DATABASE ARCHIVELOG; Database altered. v. SQL> ALTER DATABASE FLASHBACK ON; Database altered. vi. SQL> ALTER DATABASE FORCE LOGGING; Database altered. vii. SQL> […]

Server Control Utility

Stop and Start Database [oracle@crs1 ~]$ srvctl stop database -d crsdb [oracle@crs1 ~]$ srvctl start database -d crsdb ƒ Stop and Start Instance [oracle@crs1 ~]$ srvctl stop instance -d crsdb -i crsdb2 [oracle@crs1 ~]$ srvctl start instance -d crsdb -i crsdb2 ƒ Stop and Start asm [oracle@crs1 ~]$ srvctl stop asm -n crs2 [oracle@crs1 ~]$ […]