Oracle DBA Place

Step by step for installing instant client 12.1 Install RPMs 1. Download Oracle Instant client RPM files from http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html Download "Basic lite", and "SQL*Plus". 2. Convert these .rpm files into .deb packages and install using " alien " (" sudo apt-get install alien " if you don't have it). alien -i oracle-instantclient12.1-basiclite-12.1.0.2.0-1.x86_64.rpm alien -i oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm 3. Test your Instantclient install by using "sqlplus" to connect to your database: sqlplus username/password@//dbhost:1521/SID Integrate Oracle Libraries & ORACLE_HOME Add the Oracle libraries to the LD_LIBRARY_PATH to the system library list create a new file as follows: sudo vi /etc/ld.so.conf.d/oracle.conf && sudo chmod o+r /etc/ld.so.conf.d/oracle.conf and add the oracle library path as the first line: /usr/lib/oracle/12...

Hi , At Oracle Open World 2016 I collected the main improvements and changes   are going to be implemented in Oracle Database 12 Release 2 : 1.        Data Guard Creation with dbca, support automatic creation of Standby database and Far Sync instance   not for RAC (at this point) command : dbca -silent -createDuplicateDB     - PrimartDBConnectionString myprymary.domin:1523/chicago.domain     - gdbName chicago.domain -sid boston     - initParams instance_name=boston -createAsStandby     - customScripts /tmp/test.sql 2.        Multi-Instance Redo Apply, Parallel, multi-instance recovery - when standby is RAC, all of its instances will use the MRP0 process for applying 3.        Supports Diagnostic Pack on Active Data Guard   AWR+ADDM Reports for Standby are kept in the Primary 4.  ...

Hi, Trying to configure dNFS with multipath I ran into the need of routing traffic from a specific devices (10G), this article describing how to do this in the Operating System (Centos/Redhat/Oracle). There are 2 different ways of configuring persistent static routes. The first approach requires adding entries for each device (NIC) to the files /etc/sysconfig/network-scripts/route-eth[X] For eth0, the file would be /etc/sysconfig/network-scripts/route-eth0 Example: echo "192.168.10.30 via 192.168.10.10" >> /etc/sysconfig/network-scripts/route-eth0 echo "192.168.10.32 via 192.168.10.12" >> /etc/sysconfig/network-scripts/route-eth1 To add the routes instantly, run the script /etc/sysconfig/network-scripts/ifup-routes. It takes the device name as input. The script is always run at boot time so the static routes are recreated after reboots. Example: /etc/sysconfig/network-scripts/ifup-routes eth0 /etc/sysconfig/network-scripts/ifup-ro...

View all mounted asm_diskgroups in the ASM instance SYS@+ASM> select NAME,STATE from v$asm_diskgroup 2 / NAME STATE ------------------------------ ----------- DATA2 MOUNTED DATA3 MOUNTED DATADG MOUNTED View all defined asm_diskgroups in the current ASM instance SYS@+ASM>show parameter disk NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ asm_diskgroups string BBXDG , DATADG, BBXDG3, BBXDG2, BBXL7, BBXL7REDO, BBXL7ARCHIV E , DATA2, BBXL7512, BBXL74096, BBXSTORAGE, L7BBX4096, BBXL7S B, BBXL7RAWDG, TEST, BBX7ONLIN ELOG, BBX7CONTROLFILE, BBXL7RA WASM , DATA3, BBXL7ASM512, BBX_ SP03, BBX_FDB asm_diskstring string /dev/raw/raw* Change spfile only to the mounted diskgroups SYS@+ASM> alter system set asm_diskgroups='DATA2','DATA3','DATADG'; Sys...

Hi, I found a great code for extracting data guard commands (reverse engineering) I adjusted it to oracle 12.1 with far sync and it working fine. Just remember to run on Primary & Far Sync instances the following command: 1. SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_n='' scope=both sid='*'; (where n is 2 or above) 2. DGMGRL> disable configuration; 3. DGMGRL> remove configuration; and then run the script: displayconfig.sql SET SERVEROUTPUT ON SET LINESIZE 300 SET FEEDBACK OFF DECLARE rid INTEGER; indoc VARCHAR2 ( 4000 ); outdoc VARCHAR2 ( 4000 ); p INTEGER; z XMLTYPE; y CLOB; v_xml XMLTYPE; tout VARCHAR2 ( 4000 ); db_type VARCHAR2 ( 10 ); db_headers_commands clob; db_commands clob; db_commands_RedoRoutes clob; BEGIN indoc := ' '; y := NULL; rid := dbms_drs.do_control ( indoc ); outdoc := NULL; p := 1; WHILE ( outdoc IS NULL ) LOOP outdoc := dbms_drs.get_respons...

Send redo data from Primary database if MOUNT is possible. ALTER SYSTEM FLUSH REDO TO target_db_name; At Standby, if Flashback Database is not already enabled: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days ALTER DATABASE FLASHBACK ON; Create a restore point BEFORE_FAILOVER, The restore point will be used later to restore the database again to the same state before FAILOVER scenario. set lines 300 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; CREATE RESTORE POINT before_failover GUARANTEE FLASHBACK DATABASE; SELECT scn, guarantee_flashback_database, name FROM v$restore_point; SCN GUARANTEE_FLASHBACK_DATABASE NAME ------- ---------------------------- ---------------- 2619073 YES BEFORE_FAILOVER Testing FAILOVER on physical standby database STBY. SELECT name, db_unique_name, log_mode, protection_mode, database_role FROM v$database; NAME DB_UNIQUE_...

Hi Since Oracle 12c we can recover standby only by accessing the standby database/site, no need to copy nor transfer any file Short and easy - just copy and paste enjoy :) Standby Database Name: STBY Primary Database Name: PRIM On Standby sqlplus / as sysdba ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SHUTDOWN IMMEDIATE; STARTUP NOMOUNT; ALTER DATABASE MOUNT STANDBY DATABASE; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; rman target / RECOVER DATABASE FROM SERVICE PRIM USING COMPRESSED BACKUPSET NOREDO SECTION SIZE 120M; sqlplus / as sysdba SHUTDOWN IMMEDIATE; STARTUP NOMOUNT; rman target / RESTORE STANDBY CONTROLFILE FROM SERVICE PRIM; sqlplus / as sysdba ALTER DATABASE MOUNT STANDBY DATABASE; rman target / CATALOG START WITH '+DATA/ STBY /DATAFILE/'; SWITCH DATABASE TO COPY; sqlplus / as sysdba set pages 0 verify off feed off term off echo off spool /tmp/clear.sql select distinct 'ALTER DATABASE CLEAR LOGFILE...

Wanting to count how many rows in emp table SQL> SELECT COUNT (*) FROM emp; COUNT(*) ---------- 100 Cool... now let me see how much is 5 percent of emp SQL> SELECT COUNT (*) FROM emp 2 FETCH FIRST 5 PERCENT ROWS ONLY; COUNT(*) ---------- 100 Mmmmm.... it can't be also 100 ... somthing is wierd .. Let's try other way SQL> SELECT COUNT (*) 2 FROM ( SELECT empno 3 FROM emp 4 FETCH FIRST 5 PERCENT ROWS ONLY); COUNT(*) ---------- 5 Cool, now it is working :) Now let's try in PL/SQL SQL> DECLARE 2 l_Percent_to_fetch PLS_INTEGER; 3 l_cnt PLS_INTEGER; 4 BEGIN 5 SELECT COUNT (*) 6 INTO l_cnt 7 FROM ( SELECT empno 8 FROM emp 9 FETCH FIRST l_Percent_to_fetch PERCENT ROWS ONLY); 10 END; 11 / DECLARE * ERROR at line 1: ORA-03113: end-of-file on communication channel ...

CREATE OR REPLACE PACKAGE maintenance AS    PROCEDURE export_myschema;    PROCEDURE stop_job (job_name VARCHAR2, schema_name VARCHAR2);    PROCEDURE stop_all_jobs;    PROCEDURE import_schema (file_name VARCHAR2); END maintenance; /  CREATE OR REPLACE PACKAGE BODY maintenance AS    PROCEDURE export_myschema    AS       handle        NUMBER;       file_name     VARCHAR2 (200);       log_name      VARCHAR2 (200);       JOBNAME       VARCHAR2 (200);       file_prefix   VARCHAR2 (30);       dir_name      VARCHAR2 (4000);       l_fexists BOOLEAN;   ...

Usually I take care for backups, but the first time I tempted to neglect this habit - I just have met Morphy's law :( Fortunately there is a solution: yum install testdisk OR apt-get install testdisk To recover files simply type: photorec and follow the instructions So simple :)

I am used to relay on tools for formatting and indenting SQL or PL/SQL code. Looking for the same for linux scripts (especially bash) I found the following code fmt.script #!/usr/bin/env perl # fmt.script - format scripts in awk, csh, ksh, perl, sh # # we do: # standardize indentation (an indent is one tab by default) # strip trailing whitespace # change ${var} to $var where possible # change ">x" to "> x" for shell scripts # change "[ ... ]" to "test ..." for Bourne shell scripts # # we may do someday, but these are harder: # convert $VAR to $var unless a setenv or default environment variable # possibly prepending stuff from template.sh # "if ... \nthen", for ... in\ndo", "while/until ... \ndo", "fn()\n{" # # to have fmt.script reformat itself (a fair test, yes?) try: # fmt.script fmt.script fmt.script.new # use tabs for indents # fmt.script -s4 fmt.script fmt.script.new # in...

You should have an outgoing SMTP server IP to configure sending mail from the database. I already put it in the database server /etc/hosts as mailhost sqlplus / as sysdba @?/rdbms/admin/utlmail.sql @?/rdbms/admin/prvtmail.plb grant execute on UTL_MAIL to public; ALTER SYSTEM SET smtp_out_server = 'mailhost' scope=both; a simple example BEGIN UTL_MAIL.send(sender => 'Yossi@NixonIT.com', recipients => 'you@address.com', subject => 'Test Mail', message => 'Hello World', mime_type => 'text; charset=us-ascii'); END; / For further advanced options such as attachments see in this wiki

Data Pump import as sysdba and several indexes, the unix way .... impdp "'sys/dba as sysdba'" schemas=SCOTT INCLUDE=INDEX:\"in \(\'PK_DEPT\',\'EMPIDX\'\)\" directory=TMP_DIR dumpfile=scott.dmp job_name=importing_scott.log

ORA-600/ORA-7445 are generic internal error numbers for Oracle program exceptions. Sometimes these errors are unique for your specific problem and cannot be found via search engines. Using "ORA-600 lookup tool" may point your specific problem, faster and accurate. The tool can be found in ORA-600 lookup tool - Metalink Document ID 153788.1 The flowing video will guide you the usage of the LookUp Tool (11:12) - Metalink Document ID 1082674.1

Looking for installed patches on the database I have always used opatch lsinventory (since 9.2 and up) From CPUJan2006 onwards you can just query select * from registry$history; See Metalink Note:352783.1 for more information.

installing or upgrading on Ubuntu is not supported but can be done thanks to other bloggers it is well documented here https://lostinmac.com/2012/01/29/installtion-doracle-11gr2-sur-ubunutu-11-10/ thanks

before oracle 11.2 we should have do this steps: cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk dv_off cd $ORACLE_HOME/bin relink all from now this is simpler and shorter cd %ORACLE_HOME%/bin chopt disable dv All options are written in the usage syntax: usage: chopt [enable|disable] {option} options: dm = Oracle Data Mining RDBMS Files dv = Oracle Database Vault option lbac = Oracle Label Security olap = Oracle OLAP partitioning = Oracle Partitioning rat = Oracle Real Application Testing

ADRCI & alert file In Oracle version 11, no need to look for the location of alert log file, just use: adrci exec="set home orcl ;show alert -tail -f" detailed practical usage can be found here

The purpose of this article is to simulate a block level corruption using BBED utility (block browser and editor) and recover using RMAN. In this situation the data file remains online throughout the recovery operation and hence other segments within the tablespace remain accessible. Since BBED exists from Oracle7 to Oracle10g, we will have to copy some files from earlier version and compile it Cp $ORA10g_HOME/rdbms/lib/ssbbded.o $ORA11g_HOME/rdbms/lib Cp $ORA10g_HOME/rdbms/lib/sbbdpt.o $ORA11g_HOME/rdbms/lib Message files (list may differ): Cp $ORA10g_HOME/rdbms/mesg/bbedus.msb $ORA11g_HOME/rdbms/mesg Cp $ORA10g_HOME/rdbms/mesg/bbedus.msg $ORA11g_HOME/rdbms/mesg Cp $ORA10g_HOME/rdbms/mesg/bbedar.msb $ORA11g_HOME/rdbms/mesg Issue the following command: make -f $ORA11g_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed $ORA11g_HOME/bin/bbed password: blockedit SQL> Set pages 0 SQL> set feedback off SQL> spool fileunix.log SQL> select fi...

On linux redhat 5 I checked the command ps -ef in a loop and found out that it is not reliable. checked this way: while [ `ps -ef |grep tnslsnr | grep -v grep | wc -l` -eq 1 ]; do printf . ; done after about of 2 minutes the loop finished since it didn't find the process. looking for something more trusted I found the command pgrep checked this way: while [ `pgrep tnslsnr 1>/dev/null; echo $?` -ne 1 ]; do printf . ; done and it is still running in a loop .... ;) and no need to use awk or grep -v here are some commands and the behavior of pgrep : # pgrep smon # pgrep -f smon 2396 2533 # pgrep -fl smon 2396 ora_smon_orcl 2533 ora_smon_mydb # pgrep -fl ora_smon_orcl 2396 ora_smon_orcl # echo $? 0 # pgrep not_exist_process # echo $? 1 There are some more parameters, check: man pgrep