Oracle DBA Place

Block Recovery using RMAN – on Oracle 11g

noreply@blogger.com (Yossi Nixon) — Mon, 11 Apr 2011 08:31:00 +0000

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 file#||' '||name||' '||bytes from v$datafile;
SQL> spool off
$ cat bbed.par
blocksize=8192
listfile=/tmp/bbed/fileunix.log
mode=edit

Corrupt a block
Let us take the EMP table and find out the segment header of the same.

SQL> select header_file,header_block from dba_segments where segment_name='EMP';
HEADER_FILE HEADER_BLOCK
----------- ------------
5 1472
SQL> exit

$ bbed parfile=bbed.par
Password: blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 3 14:38:48 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 5,10
DBA 0x0140000a (20971530 5,10)

BBED> copy to block 1475
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /u01/data/app/oracle/oradata/orcl/EMP_TS2.DBF (5)

Block: 7011 Offsets: 0 to 511 Dba:0x01401b63
------------------------------------------------------------------------
1ea20000 0a004001 db690200 00000104 bfc10000 05000000 80806c00 00000000
00000000 00f80000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>
BBED> exit

SQL> select * from EMP;
select * from EMP
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 5681)
ORA-01110: data file 5: '/u01/data/app/oracle/oradata/orcl/EMP_TS2.DBF

Recover the block
We now run a validate database command which
- Checks datafiles for physical and logical corruption
- Confirms that all database files exist and are in the correct locations
- insert the list of corrupted blocks to v$database_block_corruption

RMAN> backup validate check logical database;
SQL> select * from v$database_block_corruption;

recovering with RMAN using the list in v$database_block_corruption

RMAN> blockrecover corruption list;

you can still do it manually:

RMAN> run {blockrecover datafile 4 block 24165;}

reliable replacement for "ps -ef"

noreply@blogger.com (Yossi Nixon) — Mon, 11 Apr 2011 07:53:00 +0000

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

Filename validation Using Regular Expression

noreply@blogger.com (Yossi Nixon) — Mon, 11 Apr 2011 07:41:00 +0000

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}$');

Valid values for init.ora parameters

noreply@blogger.com (Yossi Nixon) — Thu, 17 Mar 2011 09:17:00 +0000

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/

Israeli Identity Card Valiadation

noreply@blogger.com (Yossi Nixon) — Mon, 24 Jan 2011 14:50:00 +0000

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 fixed_number := LPAD (id_number, 9, '0'); 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

impdp appending data with query

noreply@blogger.com (Yossi Nixon) — Sun, 02 Jan 2011 09:31:00 +0000

Trying to retrieve lost records from a datapump backup directly to the production database using impdp syntax:

impdp User/password directory=my_directory dumpfile=my_full_backup.dmp logfile=imp_lost_records.log QUERY=MY_TABLE:\"where code=1 and recid in $2,5,8$ \" job_name=imp_lost_records INCLUDE=TABLE:\"=\'MY_TABLE\'\" CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND

Methods for viewing SQL Execution Plans

noreply@blogger.com (Yossi Nixon) — Mon, 27 Sep 2010 11:54:00 +0000

Using Autotrace

SQL> set autotrace traceonly explain
SQL> select ename from emp where sal > 500;

Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------

Using DBMS_XPLAN Package

SQL> explain plan for
select ename from emp where sal > 500;
Explained.

SQL> select * from TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------
Plan hash value: 2872589290
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------

Using HINT 10g and Above Only

SQL> alter session set STATISTICS_LEVEL=all;
SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ ename from emp where sal > 500;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------
SQL_ID 0vxgkhh65pf8k, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from emp where sal > 500

Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
--------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| EMP | 1 | 1 | 14 |00:00:00.01 |
--------------------------------------------------------------------------

Using Dynamic Performance View

SQL> alter session set STATISTICS_LEVEL=all;

SQL> select hash_value,sql_id,operation,id,starts,
cardinality as "E-Rows",last_output_rows as "A-Rows",cost
from v$sql_plan_statistics_all
where sql_id=' 0vxgkhh65pf8k';

HASH_VALUE SQL_ID OPERATION ID STARTS E-Rows A-Rows COST
---------- ------------- -------------- --- ------ ------ ------ ----
207272210 0vxgkhh65pf8k TABLE ACCESS 1 1 1 14 2

Contributed by my colleague y.b.m

Loggon Trigger for Tracing

noreply@blogger.com (Yossi Nixon) — Mon, 27 Sep 2010 11:28:00 +0000

Using the same technique I mentioned at "Tracing Commands" here is a code in a form of logging trigger, easier when you want to capture just a specific schema from the very first transaction.

CREATE OR REPLACE TRIGGER SYS.LOGON_TRACE_CRYSTAL_TRG
AFTER LOGON
ON CRYSTAL.SCHEMA
DECLARE
cmd VARCHAR2(100);
BEGIN
cmd := 'ALTER SESSION SET max_dump_file_size = unlimited';
EXECUTE IMMEDIATE cmd;
cmd := 'ALTER SESSION SET tracefile_identifier = ''10046''';
EXECUTE IMMEDIATE cmd;
cmd := 'ALTER SESSION SET statistics_level = ALL';
EXECUTE IMMEDIATE cmd;
cmd := 'ALTER SESSION SET events ''10046 trace name context forever, level 12''';
EXECUTE IMMEDIATE cmd;
EXCEPTION
WHEN OTHERS THEN
--NULL;
RAISE;
END ;
/

optional:

CREATE OR REPLACE SYS.TRIGGER LOGOFF_TRACE_CRYSTAL_TRG
BEFORE LOGOFF
ON CRYSTAL.SCHEMA
DECLARE
cmd VARCHAR2(100);
BEGIN
cmd := 'ALTER SESSION SET EVENTS ''10046 trace name context off''';
EXECUTE IMMEDIATE cmd;
EXCEPTION
WHEN OTHERS THEN
--NULL;
RAISE;
END ;
/

Format Shell Scripts

noreply@blogger.com (Yossi Nixon) — Wed, 28 Jul 2010 12:13:00 +0000

Cool and simple code to format any kind of script in Unix environment

awk.info » Format Shell Scripts

Recommended

Moving Control files and redo logfiles to a different filesystem

noreply@blogger.com (Yossi Nixon) — Tue, 06 Jul 2010 11:03:00 +0000

Background:
Dynamicaly moving control files and redo log files from filesystem /dbdata1/ to /dbdata3/ and from /dbdata2/ to /dbdata4/
1. run as sysdba

export ORACLE_SID=orcl
sqlplus / as sysdba
create pfile='pfile_backup_orcl.ora' from spfile;

SET pagesize 0
SET feedback off

spool OS_command_orcl.sh

SELECT 'cp ' || NAME || ' '
||
CASE
WHEN REGEXP_SUBSTR(NAME, '^\/[^/]+\/') = '/dbdata1/' THEN REPLACE (NAME, '/dbdata1/', '/dbdata3/')
WHEN REGEXP_SUBSTR(NAME, '^\/[^/]+\/') = '/dbdata2/' THEN REPLACE (NAME, '/dbdata2/', '/dbdata4/')
END OS_command
FROM v$controlfile
UNION ALL
SELECT
'cp ' || MEMBER || ' '
||CASE
WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata1/'
THEN
REPLACE (MEMBER, '/dbdata1/', '/dbdata3/')
WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata2/'
THEN
REPLACE (MEMBER, '/dbdata2/', '/dbdata4/')
END
OS_command
FROM v$logfile;

spool OFF

spool DB_command_orcl.sql

SELECT 'alter database rename file ''' || MEMBER || ''' to '''
||
CASE
WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata1/' THEN REPLACE (MEMBER, '/dbdata1/', '/dbdata3/')
WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata2/' THEN REPLACE (MEMBER, '/dbdata2/', '/dbdata4/')
END || ''';'
DB_command
FROM v$logfile
UNION ALL
SELECT
'alter system set control_files='
FROM
DUAL
UNION ALL
SELECT
''''
||CASE
WHEN REGEXP_SUBSTR(VALUE, '^\/[^/]+\/') = '/dbdata1/'
THEN
REPLACE (VALUE, '/dbdata1/', '/dbdata3/')
WHEN REGEXP_SUBSTR(VALUE, '^\/[^/]+\/') = '/dbdata2/'
THEN
REPLACE (VALUE, '/dbdata2/', '/dbdata4/')
END
|| CASE
WHEN ROWNUM = (
SELECT
COUNT(*)
FROM
v$spparameter
WHERE
NAME = 'control_files'
) THEN ''''
ELSE ''','
END
FROM v$spparameter
WHERE NAME = 'control_files'
UNION ALL
SELECT
' SCOPE=SPFILE;'
FROM
DUAL;

spool off;

spool delete_after_week_orcl.sh

SELECT 'rm ' || NAME OS_command
FROM v$controlfile
UNION ALL
SELECT
'rm ' || MEMBER
OS_command
FROM v$logfile;

spool off;

2. Shutdown the database.

shutdown immediate;
startup restrict;
shutdown normal;

3. Copy (not move) the redo log files and control files from the old location to the new location using OS command.

# sh OS_command_orcl.sh

4. Startup & mount the database but do not open.

startup mount

5. Rename

@DB_command_orcl.sql

6. Now open the database

alter database open;

7. after a while

@delete_after_week_orcl.sh;

Send files by mail from unix command line

noreply@blogger.com (Yossi Nixon) — Mon, 14 Dec 2009 21:52:00 +0000

I find it usefull sending log files from linux/unix by mail, some times it is just for saving clicks instead of using ftp, and often as part of a script.
Of course that the server should be configured for that, usually it is enabled by default.

Sending attached file

uuencode original_file_name new_file_name | mailx -s "Subject" My.Mail@Mail.COM

Sending a file as text in the email body

mailx -s "Subject" My.Mail@Mail.COM < file_name

(if mailx does not exists -> just use mail)

SQL Tuning Advisor - The commands

noreply@blogger.com (Yossi Nixon) — Sat, 28 Nov 2009 14:02:00 +0000

Using Enterprise Manager or dbconsole is the convenient way for using SQL Advisor, the problem is that these options not always exists, and there are some scenarios that you are not authorized to activate dbconsole.
So we are left with the PL/SQL option which appears to be not so complicated.
In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege:

CONN sys/password AS SYSDBA
GRANT ADVISOR TO my_user;
CONN my_user/my_password

Creating tuning task

DECLARE
l_sql VARCHAR2(3200);
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql := 'SELECT COUNT (*) FROM MY_TABLE';

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 360,
task_name => 'Yossi_Nixon_tuning_task1',
description => 'Tuning task for sql');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Running the tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'Yossi_Nixon_tuning_task1');

Tracking after the tuning task

SELECT * FROM dba_advisor_log
WHERE task_name ='Yossi_Nixon_tuning_task1';

Checking the recommendations of the advisor after the task is completed;

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('Yossi_Nixon_tuning_task1') AS recommendations FROM dual;

When You done, you can delete the task

BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => 'Yossi_Nixon_tuning_task1');
END;
/

Other ways to add new tuning task
1. Tuning task created for specific a statement from the AWR.

SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 764,
end_snap => 938,
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_AWR_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

2. Tuning task created for specific a statement from the cursor cache.

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_tuning_task',
description => 'Tuning task for statement 19v5guvsgcd1v.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

3. Tuning task created from an SQL tuning set.

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => 'test_sql_tuning_set',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'sqlset_tuning_task',
description => 'Tuning task for an SQL tuning set.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

If the TASK_NAME parameter is specified, its value is returned as the SQL tune task identifier. If omitted, a system generated name such as "TASK_1478" is returned. If the SCOPE parameter is set to scope_limited the SQL profiling analysis is omitted. The TIME_LIMIT parameter simply restricts the time the optimizer can spend compiling the recommendations.
During the execution phase, you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:

Interrupt and resume a tuning task.

EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');

Cancel a tuning task.

EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');

Reset a tuning task allowing it to be re-executed.

EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');

The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view:

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';

TASK_NAME STATUS
------------------------------ -----------
emp_dept_tuning_task COMPLETED

1 row selected.

Silent Installation

noreply@blogger.com (Yossi Nixon) — Thu, 05 Nov 2009 14:06:00 +0000

Some times there are problems using installation with GUI, the reason can be:
1. Firewall
2. Unix security
3. Client software (missing or not working(
and maybe some more reasons
If your environment is cooperative helping you solve this - it is simple - go for it.
But if you are on your own and can't reach "the right guys" - do it yourself "Silently"
Look for the proper response file fits for your needs in /database/response/

Edit it and run the following command:

$ ./runInstaller -force -invPtrLoc /oraInst.loc -silent -noconfig -ignoreSysPrereqs -responseFile /database/response/enterprise.rsp

You can also run the installer without a response file as mentioned in Metalink Note 782918.1

$ ./runInstaller -silent -force -debug \
FROM_LOCATION="/mount/dvd/database/stage/products.xml" \
ORACLE_HOME="/u01/app/oracle/product/11.1.0/db_1" \
ORACLE_HOME_NAME="Ora11gDb1" ORACLE_BASE="/u01/app/oracle" \
TOPLEVEL_COMPONENT='{"oracle.server","11.1.0.6.0"}' \
INSTALL_TYPE="EE" n_configurationOption=3

Good luck

RDA - Health Check (HCVE)

noreply@blogger.com (Yossi Nixon) — Thu, 05 Nov 2009 13:29:00 +0000

I noticed that many DBAs are not aware of the need to run a prerequisite system check before installation of an oracle product.
This special check should be done in addition to the installation document and the check inside the installer.
The utility called RDA with a module for Health Check called HCVE.
It is well documented for RAC but can be used for simple installation.
RDA is maintained frequently and adjusted to the OS version against the oracle software.
You can read about it in Metalink Doc ID: 250262.1
"RDA - Health Check / Validation Engine Guide"

Sample

./rda.sh -T hcve

Processing HCVE tests ...
Available Pre-Installation Rule Sets:
1. Oracle Database 10g R1 (10.1.0) PreInstall (AIX)
2. Oracle Database 10g R2 (10.2.0) PreInstall (AIX)
3. Oracle Database 11g R1 (11.1.0) PreInstall (AIX)
4. Oracle Application Server 10g (9.0.4) PreInstall (AIX)
5. Oracle Fusion Middleware 11g R1 (11.1.1) PreInstall (AIX)
6. Oracle Portal PreInstall (Generic)
Available Post-Installation Rule Sets:
7. Oracle Portal PostInstall (generic)
8. Data Guard PostInstall (Generic)
.
.
.

Test Results
~~~~~~~~~~~~

ID NAME RESULT VALUE
====== ==================== ======= ==========================================
A00010 OS Certified? PASSED Certified with Oracle Database 11g R1
A00020 User in /etc/passwd? PASSED userOK
A00040 Group in /etc/group? PASSED GroupOK
A00050 Input ORACLE_HOME RECORD /products/oracle/version/ora11g1
A00060 ORACLE_HOME Valid? PASSED OHexists
A00070 O_H Permissions OK? PASSED CorrectPerms
A00080 oraInventory Permiss PASSED oraInventoryOK
A00090 Got ld,nm,ar,make? FAILED MakeFoundInPath
A00100 Umask Set to 022? FAILED UmaskNotOK
A00110 ulimits OK? FAILED DataTooSmall StackTooSmall
A00140 LDLIBRARYPATH Unset? FAILED IsSet
A00160 LIBPATH Unset? FAILED IsSet
A00190 JDK Home RECORD /products/oracle/version/ora11g1/jdk
A00200 JDK Version FAILED JDK home is missing
A00210 Other O_Hs in PATH? FAILED OratabEntryInPath
A00220 Other OUI Up? PASSED NoOtherOUI
A00230 /tmp Adequate? PASSED TempSpaceOK
A00240 Disk Space OK? PASSED DiskSpaceOK
A00250 Swap (in MB) RECORD 24448
A00260 RAM (in MB) PASSED 32768
A00270 SwapToRAM OK? FAILED SwapLessThanRAM
A00290 IP Address RECORD ***.**.*.**
A00300 Domain Name RECORD *****.*****.co.il
A00310 DNS Lookup PASSED Host correctly registered in DNS
A00320 /etc/hosts Format FAILED Missing host.domain
A00340 AIXTHREAD_SCOPE=S? FAILED AIXTHREADNotSet
A00350 LINK_CNTRL is Unset? PASSED LINK_CNTRLunset
A00410 Got OS Patches? PASSED PatchesFound
A00430 Got OS Packages? PASSED All required OS packages are installed
Result file: /products/oracle/version/INSTALL11/rda/output/RDA_HCVE_A200DB11R1_aix_res.htm

Importing just 1 view via impdp

noreply@blogger.com (Yossi Nixon) — Mon, 24 Aug 2009 09:09:00 +0000

Hi,
Struggling the right syntax in Unix environment for importing a lost view, here is the proper way:

impdp user/password@database directory=dir_dump dumpfile=dump.dmp logfile=imp_MY_VIEW.log INCLUDE=VIEW:\"= \'MY_VIEW\'\" job_name=imp_MY_VIEW

RMAN-06059: expected archived log not found

noreply@blogger.com (Yossi Nixon) — Tue, 12 May 2009 15:05:00 +0000

while running archivelog backup and the file is missing:

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

trying to fix it by crosscheck:

run {
allocate channel c1 type disk ;
crosscheck archivelog all ;
release channel c1 ;
}

validation succeeded for archived log
archive log filename=D:REDOARCHARCH_1038.DBF recid=1017 stamp=611103638

still need to run:

allocate channel for maintenance type disk;
crosscheck archivelog all;
release channel;

ASM instance: ORA-15032 and ORA-15063 errors occur after restart of the Host

noreply@blogger.com (Yossi Nixon) — Sun, 05 Apr 2009 13:34:00 +0000

We already had the same symptom of ORA-15032 & ORA-15063 mentioned in my last post. Trying to follow these instructions didn't help this time.

ls -l /dev/oracleasm/disks/*
rpm -qa | grep oracleasm
kfod asm_diskstring='ORCL:*' disks=all
/etc/sysconfig/oracleasm conatin ORACLEASM_SCANORDER=dm
spfile has: asm_diskstring = /dev/dm*

All of these command worked fine. Beacause of the dm* parameter I checked the filesystem:

$ ls -l /dev/dm-*
/bin/ls: /dev/dm-*: No such file or directory

It seems that the dm* devices are missing and must be there for ASM by Note 602952.1 :

NOTE: When scanning, only the device names known by the kernel are scanned. With device-mapper, the kernel sees the devices as /dev/dm-XX. The /dev/mapper/XXX names are created by udev for human readability. Any configuration of ORACLEASM_SCANORDER or ORACLEASM_SCANEXCLUDE must use the dm prefix.

Since this Host is RedHat 5, we found the source of the problem by Note 558596.1:

Cause: The oracleasm scans /proc/partitions and expects devices to be available in /dev. If the device doesn't exist in /dev it will fail to open the device. RHEL/OEL5 does not create dm-xx devices in /dev by default.
Solution: Please comment out the following line in file /etc/udev/rules.d/50-udev.rules
#KERNEL=="dm-[0-9]*", ACTION=="add", OPTIONS+="ignore_device"
reboot and the problem should be gone.
Note: Newer versions of EL/RH 5 the udev rule has been moved to "/etc/udev/rules.d/90-dm.rules".

Recovering cloned database while datafile was added

noreply@blogger.com (Yossi Nixon) — Mon, 23 Mar 2009 09:46:00 +0000

We are cloning our primary production database to a test system periodically. Sometimes it happens that we are adding a datafile to the primary database while the copy is in progress. After the copy we are creating a new control file based on the production ( with the new datafile ), trying to recover the cloned database we are getting the following error (from the alert.log):

SQL> alter database recover automatic until time '2009-03-15 16:00:00' using backup controlfile;
File #114 added to control file as 'UNNAMED00114'. Originally created as:
'/bzq1/oracalls_2009_indx1/q109_icalls_ts_05.dbf'
Errors with log /bilpre/oraarch/bzq1_1_0000048990_561428818.arc
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Media Recovery failed with error 1244
ORA-283 signalled during: alter database recover automatic until time '2009-03-15 16:00:00' using backup controlfile...

It seems that in the archive log there is data regarding to the new datafile, so we need to create it also in the cloned database. To find out what is the current full location that is written in the cloned database:

SQL> select name from v$datafile where name like '%UNNAMED00114%';
NAME
-------------------------------
/software/oracle/BILPRE10gR2/dbs/UNNAMED00114

Now we have the information for the following command :

SQL> alter database create datafile '/software/oracle/BILPRE10gR2/dbs/UNNAMED00114' as '/bilpre/oracalls_2009_indx1/q109_icalls_ts_05.dbf'
Database altered.
SQL> alter database recover automatic until time '2009-03-15 16:00:00' using backup controlfile;
Media Recovery Complete (bilpre)
SQL> alter database open resetlogs;

The Database is working, and there is no need to copy it again

glogin.sql,in 10g

noreply@blogger.com (Yossi Nixon) — Tue, 20 Jan 2009 14:39:00 +0000

In Oracle 10g, the glogin.sql and login.sql are run whenever you connect to a new user.

This is the glogin.sql additions that I am using in 10g:

set verify off termout off head off feed off

col login_prompt new_value welcome

SELECT upper(SYS_CONTEXT('USERENV','SERVER_HOST')
||' '
|| SYS_CONTEXT('USERENV','CURRENT_USER')
||'@'
|| SYS_CONTEXT('USERENV','DB_NAME')) login_prompt
FROM DUAL
;

set sqlprompt "_user'@'_connect_identifier> "

set verify on termout on head on feed on
prompt ************************************
prompt WELCOME TO &&welcome
prompt ************************************
prompt
set echo off serveroutput on size 100000 line 100 trims on

Connecting Oracle Server using JDBC

noreply@blogger.com (Yossi Nixon) — Tue, 06 Jan 2009 15:48:00 +0000

There is a small difference of the connection string when the target is SID or SERVICE

jdbc:oracle:driver_type:[username/password]@//host_name:port_number:SID_NAME

jdbc:oracle:driver_type:[username/password]@//host_name:port_number/SERVICE_NAME

Colon means SID
Slash means Service name

tar Over ssh

noreply@blogger.com (Yossi Nixon) — Fri, 27 Jun 2008 06:03:00 +0000

On regular basis, we encounter the need to copy a whole directory between two hosts. Usually we do it when we clone oracle application software.
Each time I am looking for the right syntax … :( NO MORE !!!

tar cvf‎ – source_dir/ | ssh target_host "cd `pwd`;tar xvf‎ -"

This one will copy source_dir to‎ target_host
For slow connection‎:

tar cvf‎ – source_dir/ | gzip -c‎ | ssh target_host "cd `pwd`;gunzip -c|tar xvf‎ -"

You will enter password once‎ (probably‎)

switching database to archivelog mode

noreply@blogger.com (Yossi Nixon) — Tue, 27 May 2008 12:29:00 +0000

switch the database to archivelog mode.

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

switch database to noarchivelog mode.

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;

trcsess utility

noreply@blogger.com (Yossi Nixon) — Thu, 28 Feb 2008 14:07:00 +0000

The trcsess utility consolidates trace output from selected trace files based on several criteria:

trcsess  [output=output_file_name]
         [session=session_id]
         [clientid=client_id]
         [service=service_name]
         [action=action_name]
         [module=module_name]
         [trace_files]

trcsess output=main.trc service=psdwh *trc

After the consolidate trace file had been generated you can execute tkprof on it.
For example:

tkprof main.trc main.prf sys=no sort=exeela
TKPROF: Release 10.2.0.3.0 - Production on Thu Feb 28 13:38:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.

for more information:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm

Disk Structure Command for ASM - asmdisks

noreply@blogger.com (Yossi Nixon) — Mon, 11 Feb 2008 07:33:00 +0000

In my firm we have a seperation between System guys to the DBAs, we don't have root privileges. this is the reason that they are peparing us the ASM disks.
After using "/etc/init.d/oracleasm createdisk..." I tailored a script to see all the information that I can get WITHOUT the need to connect to the ASM instance.

#!/bin/ksh

export ORACLE_HOME=`grep ASM /etc/oratab | cut -d: -f2`
export PATH=$PATH:~$user/dba/scripts/bin:$ORACLE_HOME/bin
export SID=`grep ASM /etc/oratab | cut -d: -f1`

printf "\n%-15s %-14s %-11s %s\n" "ASM disk" "based on" "Minor,Major" "Size (Mb)"
printf "%-15s %-14s %-11s %s\n" "===============" "=============" "===========" "========="
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk $i | awk '{print $2}' | sed 's/\"//g'`
v_minor=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}' | awk -F] '{print $1}' | awk '{print $1}'`
v_major=`/etc/init.d/oracleasm querydisk $i | awk -F[ '{print $2}' | awk -F] '{print $1}' | awk '{print $2}'`
v_device=`ls -la /dev | awk -v v_minor=$v_minor -v v_major=$v_major '{if ( $5==v_minor ) { if ( $6==v_major ) { print $10}}}'`
v_size=`${ORACLE_HOME}/bin/kfod asm_diskstring='ORCL:*' disks=all | grep ${v_asmdisk} | awk '{print $2}'`
Total_size=`expr $Total_size + $v_size`
printf "%-15s %-14s %-11s %s\n" $v_asmdisk "/dev/$v_device" "[$v_minor $v_major]" $v_size
done
printf "\nTotal: %43s\n\n" $Total_size

The output is:

>asmdisks

ASM disk        based on       Minor,Major Size (Mb)
=============== =============  =========== =========
MTSDB_DATA_01   /dev/dm-23     [253, 23]   98304
MTSDB_DATA_02   /dev/dm-24     [253, 24]   98304
MTSDB_DATA_03   /dev/dm-25     [253, 25]   98304
MTSDB_DATA_04   /dev/dm-26     [253, 26]   98304
MTSDB_DATA_05   /dev/dm-6      [253, 6]    98304
MTSDB_DATA_06   /dev/dm-7      [253, 7]    98304
MTSDB_DATA_07   /dev/dm-8      [253, 8]    98304
MTSDB_DATA_08   /dev/dm-9      [253, 9]    98304
MTSDB_DATA_09   /dev/dm-10     [253, 10]   98304
MTSDB_DATA_10   /dev/dm-11     [253, 11]   98304
MTSDB_DATA_11   /dev/dm-12     [253, 12]   98304
MTSDB_DATA_12   /dev/dm-13     [253, 13]   98304
MTSDB_DATA_13   /dev/dm-14     [253, 14]   98304
MTSDB_DATA_14   /dev/dm-15     [253, 15]   98304
MTSDB_DATA_15   /dev/dm-16     [253, 16]   98304
MTSDB_DATA_16   /dev/dm-17     [253, 17]   98304
MTSDB_DATA_17   /dev/dm-18     [253, 18]   98304
MTSDB_DATA_18   /dev/dm-19     [253, 19]   98304
MTSDB_DATA_19   /dev/dm-20     [253, 20]   98304
MTSDB_DATA_20   /dev/dm-21     [253, 21]   98304

Total:                                     1966080

df emulation in ASM - asmbdf

noreply@blogger.com (Yossi Nixon) — Mon, 31 Dec 2007 09:32:00 +0000

When talking ASM between DBAs and System guys I had to let them "see" the new filesystem in the way they are used to.
I parsed the output of asmcmd utility to be as close as it can be to the command in Unix/Linux, I called it asmbdf:

#!/bin/ksh

export user=oracle
export ORACLE_HOME=`grep ASM /etc/oratab | cut -d: -f2`
export PATH=$PATH:~$user/dba/scripts/bin:$ORACLE_HOME/bin
export ORACLE_SID=`grep ASM /etc/oratab | cut -d: -f1`

asmcmd lsdg | \
awk '{
if ( FNR == 1 )
{
printf "%-20s %10s %10s %10s %10s %-20s\n","Filesystem","Size","Used","Avail","Use%","Mounted on"
}
if ( FNR > 1 )
{
if ( $2 == "EXTERN" ) { REDUNDENCY=1 }
if ( $2 == "NORMAL" ) { REDUNDENCY=2 }
if ( $2 == "HIGH" ) { REDUNDENCY=3 }
printf "%-20s %10d %10d %10d %10.2f%% %-20s\n",$(NF),($8/REDUNDENCY)*1024,(($8-$9)/REDUNDENCY)*1024,($9/REDUNDENCY)*1024,($8-$9)/$8*100,$(NF)
}
}'

The output is:

>asmbdf
Filesystem                 Size       Used      Avail       Use% Mounted on
DATA/                 301989888   99937280  202052608      33.09% DATA/
FLASH_RECOVERY_AREA/   50331648    3042304   47289344       6.04% FLASH_RECOVERY_AREA/