Oracle DBACategory

OVERVIEW OF SQL Please create the scott schema from the following GITHUB repository https://github.com/oracle/dotnet-db-samples/blob/master/schemas/scott.sql EXECUTING SELECT STATEMENT SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ———- ————– ————- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON HANDLING NULL VALUES SQL> SELECT EMPNO,ENAME,NVL(MGR,0) FROM EMP; EMPNO… Continue Reading BASIC SQL REFERENCE FOR ORACLE DBA

We faced this error during Oracle12cR2 installation. During our installation of oracle 12.2 in new home on existing Oracle 11g installation,we faced the error   LOG INFO: Executing [/opt/oracle/product/12.2.0.1/db1/bin/diagsetup, clustercheck=false, basedir=/opt/oracle/product, oraclehome=/opt/oracle/product/12.2.0.1/db1] /opt/oracle/product/12.2.0.1/db1/bin/diagsetup WARNING: java.lang.UnsatisfiedLinkError: /opt/oracle/product/12.2.0.1/db1/oui/lib/linux64/liboraInstaller.so: /opt/oracle/product/12.2.0.1/db1/oui/lib/linux64/liboraInstaller.so: undefined symbol: JNU_ThrowByName WARNING:        at java.lang.ClassLoader$NativeLibrary.load(Native Method) WARNING:        at java.lang.ClassLoader.loadLibrary0(ClassLoader.java:1941) WARNING:        at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1824)… Continue Reading Error undefined symbol: JNU_ThrowByName during oracle 12c R2 installation

Today I observed that we were getting following error when we were going to connect using SERVICE_NAME from (DR site) to new exadata server. The connection was working fine with SID in tnsnames.ora to individual instances. [oracle@XXX admin]$ sqlplus dba/XXX@PROD_CDV SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 27 09:08:38 2017… Continue Reading ORA-12545: Connect failed because target host or object does not exist using SERVICE_NAME in tnsnames.ora

Error in DR alert log:- FAL[client]: Failed to request gap sequence GAP – thread 2 sequence 9432-9473 DBID 2085418592 branch 924361120 FAL[client]: All defined FAL servers have been attempted. ———————————————————— Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that’s sufficiently large enough to maintain adequate log switch… Continue Reading Restore Archivelog from particular sequence to another location using tape backup

1.OPatch installation Copy the opatch binary using root user to $ORACLE_HOME and $GRID_HOME. Then please keep backup of old OPatch directory. Unzip the binary in the $ORACLE_HOME and $GRID_HOME. unzip -o -d $ORACLE_HOME /oracle/soft/p6880880_112000_Linux-x86-64.zip unzip -o -d $GRID_HOME /oracle/soft/p6880880_112000_Linux-x86-64.zip Change owner of the folder OPatch. chown -R oracle:oinstall $ORACLE_HOME/OPatch chown… Continue Reading PSU patch Oracle 11gR2 oracle single instance HA

1.Please download latest version of OPatch and install under $ORACLE_HOME and $GRID_HOE Copy the opatch binary using root user to $ORACLE_HOME and $GRID_HOME. Then please keep backup of old OPatch directory. Unzip the binary in the $ORACLE_HOME and $GRID_HOME. unzip p6880880_112000_Linux-x86-64.zip -d /u01/app/11.2.0/grid unzip p6880880_112000_Linux-x86-64.zip -d /u01/app/oracle/product/11.2.0/db_1 Change owner of… Continue Reading Grid and Oracle PSU patching using OPatch 11gR2 RAC

1.V$LOGSTDBY_PROCESS displays dynamic information about what is happening to the Data Guard log apply services. This view is helpful when diagnosing performance problems during the logical application of archived redo logs to the standby database, and it can be helpful for other problems. This view is for logical standby databases… Continue Reading Monitor steps for Logical Standby Database

LOGICAL STANDBY DATABASE STEP BY STEP     Primary DB: CLRVMS (ip:10.3.248.5 and 10.3.248.8) Logical Standby DB: CLRVMS_B(ip:10.3.248.129)   Please check whether Primary database is in archivelog or not.It should be in archivelog.   select log_mode from v$database; LOG_MODE ———— ARCHIVELOG   Enable Force Logging, SQL> ALTER DATABASE FORCE LOGGING; Database… Continue Reading Logical Standby Database setup step by step from RAC to Single Instance in 11g

FLASHBACK FEATURES   Purging the Recycle Bin   purge table sales.woodscrews; purge index sales.woodscrews_pk_idx; purge tablespace sales; purge recyclebin;   Undropping Objects in the Recycle Bin   flashback table ws_app.woodscrews to before drop; select object_name, original_name, droptime, dropscn from user_recyclebin; flashback table bonus to before drop rename to bonus_deb;  … Continue Reading FLASHBACK DATABASE AND QUERIES

Oracle Network Configuration =========================== In its most basic form, Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration. This article gives an example of each file as a starting point for simple network configuration. •Assumptions •Listener.ora •Tnsnames.ora •Sqlnet.ora •Testing Assumptions ========== The example files below are relevant for… Continue Reading Network configuration listener and tnsnames in oracle