Oracle DBA Place

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

Extract the filename from a full file path unix select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) FROM dba_data_files; windows select substr(file_name,(instr(file_name,'\',-1,1)+1),length(file_name)) FROM dba_data_files; Validates a long filename using Windows' rules: select file_name from table_of_files WHERE not REGEXP_LIKE(file_name,'^[^\\\./:\*\?\" \|]{1}[^\\/:\*\?\" \|]{0,254}$'); combinning these two SQLs: WITH files AS ( select substr(file_name,(instr(file_name,'/',-1,1)+1),length(file_name)) base_filename FROM dba_data_files) select base_filename from files WHERE REGEXP_LIKE(base_filename,'^[^\\\./:\*\?\" \|]{1}[^\\/:\*\?\" \|]{0,254}$');

A new option started from 11.1 for listing Valid Values in init.ora at the site of Jonathan Lewis http://jonathanlewis.wordpress.com/2011/03/08/valid-values/

Needed to check the validity of an Israeli identity card number I created this simple function CREATE OR REPLACE FUNCTION checkid (id_number IN VARCHAR2) RETURN VARCHAR2 IS fixed_number VARCHAR2 (10); digit NUMBER := 0; sum_digits NUMBER := 0; BEGIN CASE WHEN LENGTH (id_number) 9 THEN RETURN 'Too Long'; ELSE fixed_number := id_number; END CASE; FOR i IN 1 .. 9 LOOP digit := TO_NUMBER (SUBSTR (fixed_number, i, 1)) * (CASE WHEN MOD (i, 2) = 0 THEN 2 ELSE 1 END); IF LENGTH (digit) > 1 THEN digit := SUBSTR (digit, 1, 1) + SUBSTR (digit, 2, 1); END IF; DBMS_OUTPUT.put_line (i || '#'); sum_digits := sum_digits + digit; IF MOD (sum_digits, 10) = 0 THEN RETURN 'OK'; ELSE RETURN 'BAD'; END IF; END LOOP; RETURN TO_CHAR (sum_digits); END; / references: http://goo.gl/z2roI http://goo.gl/dCbS0