Tag: oracle

Oracle Application server 10g unable to start HTTP

Posted on by Osama Mustafa in Application
Hi all

Today When I was Supporting for some customer , i faces issue in application server 10gthe solution is so simple


Let describe the problem :


when we use

./opmnctl startall


all service is up expect Http_server is down

Check Logs nothing , Check Application server logs nothing

And when you try to shutdown IAS again it will give you :

RCV : Permission denied

For first i was thought its permission issue but who can change the permission .




the solution like the following :


On Os


ps -ef | grep oracle


you must find 2 process
Oracle PID $PATH/opmn.d


kill them both
Kill PID PID
or
Kill -9 PID
kill -9 PID


After this
./opmnctl startall

Usage of OS_AUTHENT_PREFIX

Posted on by Osama Mustafa in Database

1. Overview

1) OS_AUTHENT_PREFIX is an important initialization parameter to configure Oracle External User environment.
2) External User is a kind of Oracle External Authentication solution.
3) With Oracle External User, the user authentication process is accomplished by operating system automatically.
4) Authorized OS user can access Oracle database directly without providing username and password other than logon with prompting these information.

2. Official Reference

OS_AUTHENT_PREFIX
Parameter type
String
Syntax
OS_AUTHENT_PREFIX = authentication_prefix
Default value
OPS$
Modifiable
No
OS_AUTHENT_PREFIX specifies a prefix that Oracle uses to authenticate users attempting to connect to the server. Oracle concatenates the value of this parameter to the beginning of the user’s operating system account name and password. When a connection request is attempted, Oracle compares the prefixed username with Oracle usernames in the database.
The default value of this parameter is OPS$ for backward compatibility with previous versions. However, you might prefer to set the prefix value to “” (a null string), thereby eliminating the addition of any prefix to operating system account names
Note:
The text of the OS_AUTHENT_PREFIX parameter is case sensitive on some operating systems.
See Also:
l Your operating system-specific Oracle documentation for the default value
Create Oracle database user with OS authentication solution.

3.1 Prerequisite Checking

1) OS Environment
C:\Documents and Settings\ZYY> sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.1.0 – Production on 星期四 6月 11 17:02:49 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production
SQL> SELECT OSUSER, USERNAME, PROGRAM FROM V$SESSION;
OSUSER USERNAME PROGRAM
——————– ——————– ——————–
SYSTEM ORACLE.EXE (q001)
SYSTEM ORACLE.EXE (QMNC)
SYSTEM ORACLE.EXE (q000)
YYBB\ZYY YYBB\ZYY sqlplus.exe
SYSTEM ORACLE.EXE (MMNL)
SYSTEM ORACLE.EXE (MMON)
SYSTEM ORACLE.EXE (CJQ0)
SYSTEM ORACLE.EXE (RECO)
SYSTEM ORACLE.EXE (SMON)
SYSTEM ORACLE.EXE (CKPT)
SYSTEM ORACLE.EXE (LGWR)
SYSTEM ORACLE.EXE (DBW0)
SYSTEM ORACLE.EXE (MMAN)
SYSTEM ORACLE.EXE (PSP0)
SYSTEM ORACLE.EXE (PMON)
Note:
n This practice is intended to be taken on Microsoft Windows platform.
n “YYBB” is the Computer Name on which runs the Windows OS.
n “ZYY” is the User Name which be used to logon to Windows OS.
2) Database Environment
SQL> SHOW PARAMETER OS_AUTHENT
NAME TYPE VALUE
———————————— ———————- ————–
os_authent_prefix string
remote_os_authent boolean FALSE
Note:
n The value of OS_AUTHENT_PREFIX parameter is set to “” (a null string).

3.2 Create External User

1) Create User in Oracle Database
SQL> CREATE USER “YYBB\ZYY” IDENTIFIED EXTERNALLY;
Note:
n In the above demo case, the value of OS_AUTHENT_PREFIX parameter is set to “” (a null string), the corresponding username in Oracle database should be YYBB\ZYY.
n If the value of OS_AUTHENT_PREFIX parameter is set to “OPS$”, the corresponding username in Oracle database should be OPS$YYBB\ZYY.
n Do NOT forget the Double Quotation Marks(“”) embrace the username YYBB\ZYY, otherwise, you will get an ORA-00911 warning against the special character \.
n Be aware of the keyword “EXTERNALLY”.
n Do NOT be reckless with “IDENTIFIED” which should NOT be followed by “BY”.
2) Grant Privileges to Oracle User
SQL> GRANT RESOURCE, CONNECT TO “YYBB\ZYY”;
Note:
n Grant appropriate and Minimum privileges to this Oracle user according to your system security acquirement.
n Oracle user name might be Case Sensitive on some platforms.

3.3 Test External User

1) Logon Oracle Database with External User
C:\Documents and Settings\ZYY>sqlplus /
SQL*Plus: Release 10.2.0.1.0 – Production on 星期四 6月 11 17:26:51 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production
SQL>
Note:
n You do NOT need to provide username and password to logon to Oracle database through sqlplus.
2) Check the Current Logon User
SQL> SHOW USER
USER 为 “YYBB\ZYY“
SQL> SELECT USERNAME FROM DBA_USERS;
USERNAME
————————————————————
YYBB\ZYY
SYS
SYSTEM
MDSYS
OUTLN
XDB
Note:
n Current logon user is “YYBB\ZYY”.
n You can now access Oracle database via your OS user account directly.

Change Oracle EnterPrise manager

Posted on by Osama Mustafa in Database
DBA who never heard in this product from oracle ?

Some times for security issue we need to change the password , but as we all know nothing goes easy but all you have to do follow the below steps :

Stop Oracle Enterprise Manager
emctl stop dbconsole
emctl status dbconsole
Connect to database as sysdba and change system password
sqlplus / as sysdba

alter user sys identified by NewPassword ;
alter user system identified by NewPassword ;
alter user dbsnmp identified by NewPassword ;
alter user sysman identified by NewPassword ;

after this
emctl set password
and put your newpassword
restart your enterprise maybe you will not need to the below steps .

cd $ORACLE_HOME/sysman/config/emoms.properties :
oracle.sysman.eml.mntr.emdRepUser=SYSMAN
oracle.sysman.eml.mntr.emdRepPwd=d0355495a68cd5ae
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
TO
oracle.sysman.eml.mntr.emdRepUser=SYSMAN
oracle.sysman.eml.mntr.emdRepPwd=Newpassword
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE
“in some database version you will not see all the parameters change what you have”

Thank you all .

Auditing Vs Performance on Oracle Database

Posted on by Osama Mustafa in Database

You show this (part of a) AWR report to the DBA and he proudly concludes: disable auditing, it is killing the performance! And thus, quite often Oracle database auditing is not enabled. And here are the 3 main reasons why auditing is not turned on:

– DBAs, developers, etc. are not familiar with this feature: For those who are not familiar with auditing.

– Security is not considered important and necessary: For those who do not consider auditing important, I wish them luck. They are anyway not interested in what I have to say..

– Performance is being hit by enabling auditing: For the ones having issues with performance when auditing is enabled, here is something.

There are 3 major reasons why performance suffers when auditing is enabled: too much is being audited, AUD$ still hangs in the SYSTEM tablespace and surprise, surprise: the Oracle bugs.

1. Too much is being audited. If it is a new database, spend some time with all parties involved on what to audit. The reality however is something like that: go-live day is getting closer, oh do we have auditing enabled? How do you enable it, can you give me the command please. And it should not go like that. You first decide on the value of audit_trail and then audit what is really needed, do not audit repetitive commands that generate too many inserts into the AUD$ table for it can grow very fast indeed.

If it is an existing database, check first what is being audited. To find out system audited stuff run the following:

select * from DBA_PRIV_AUDIT_OPTS
union all
select * from DBA_STMT_AUDIT_OPTS;

Note that the difference between the two views above is very small and I have not found yet a place with explanation about the difference. The documentation says that DBA_STMT_AUDIT_OPTS describes the current system auditing options across the system and by user while DBA_PRIV_AUDIT_OPTS describes the current system privileges being audited across the system and by user. Puzzled? Me too.

For example, AUDIT SYSTEM belongs only to DBA_PRIV_AUDIT_OPTS while PROFILE, PUBLIC SYNONYM, DATABASE LINK, SYSTEM AUDIT, SYSTEM GRANT and ROLE belong only to DBA_STMT_AUDIT_OPTS.

On the other hand, CREATE PUBLIC DATABASE LINK, EXEMPT ACCESS POLICY, CREATE EXTERNAL JOB, DROP USER and ALTER DATABASE belong to both views, get it :-)

For the auditing options on all objects, check DBA_OBJ_AUDIT_OPTS.

2. AUD$ still hangs in the SYSTEM tablespace. The system tablespace might be fragmented. Starting 11gR2, Oracle supports moving the AUD$ table out of the SYSTEM tablespace. But first, noaudit your policy or stop the auditing.

If still running 11.1.0 or a below, here is how to do it:

create tablespace AUDIT_DATA datafile ...;
create table AUDX tablespace AUDIT_DATA as select * from AUD$;
rename AUD$ to AUD$$;
rename AUDX to AUD$;
create index i_aud2 on AUD$(sessionid, ses$tid) tablespace AUDIT_DATA;

Remember to purge the records on regular basis. Do not just delete them but move them to a centralized auditing repository. Use the new DBMS_AUDIT_MGMT package. C In urgent cases, it is safe to run truncate table AUD$;

If you use FGA, remember to move also FGA_LOG$ away from the SYSTEM tablespace:

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUDIT_DATA');
END;
/

3. Oracle bugs. If you enable auditing you might get several bugs for free, most old ones should be fixed in 11.2.0.2, don’t know about the new ones :-)

Oracle Database Block corruption

Posted on by Osama Mustafa in Database

Oracle Database Block corruption

“Block corruption is rare but it does happen. As databases get larger and larger – the probability of it happening at some point nears 100%.”–Mr. Tom Kyte Oracle Expert

Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committed

ORA-01578:
ORACLE data block corrupted (file # string, block # string)
Whenever we encounter above error message mean we have BLOCK CORRUPTION.

NOTE: We can find detail information about block corruption in alert.log file

Two types of block corruption can happens

– Physical corruption (media corrupt)
– Logical corruption (soft corrupt)

Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk;

Logical corruption can among other reasons be caused by an attempt to recover through a NOLOGGING action.

Difference between logical and physical corruption

Logical corruption is header – footer – that is one of the checks, yes (it is looking for fractured blocks and when it hits one, it’ll re-read it, that is why there is no need for “alter tablespace begin backup” with rman)

Physical corruption is “we cannot read the block from disk, something is physically preventing us from doing so”.

How to detect block corruption?
1. DBVERIFY utility

DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored.

http://dbataj.blogspot.com/2007/04/offline-database-verification-utility.html

2. Block checking parameters

There are two initialization parameters for dealing with block corruption:- DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time)causes 1-2% performance overhead- DB_BLOCK_CHECKING (serverprocess checks block for internal consistency after every DML)causes 1-10% performance overhead

Note: In10g db_block_checksum value TYPICAL is implying TRUE and db_block_checking value FULL implying TRUE.

DB_BLOCK_CHECKING Initialization Parameter
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3176

3. ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE SQL statement

Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

ANALYZE: Reporting Corruption
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3173

4. RMAN BACKUP command with THE VALIDATE option

You can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct locations, and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When performing a BACKUP… VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.

Detection of Logical Block Corruption

Besides testing for media corruption, the database can also test data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log. If CHECK LOGICAL was used, the block is also logged in the server session trace file. By default, error checking for logical corruption is disabled.
For BACKUP commands the MAXCORRUPT parameter sets the total number of physical and logical corruptions permitted in a file. If the sum of physical and logical corruptions for a file is less than its MAXCORRUPT setting, the RMAN command completes successfully. If MAXCORRUPT is exceeded, the command terminates and RMAN does not read the rest of the file. V$DATABASE_BLOCK_CORRUPTION is populated with corrupt block ranges if the command succeeds. Otherwise, you must set MAXCORRUPT higher and re-run the backup to find out the corrupt block ranges.
RMAN found any block corruption in database then following Data Dictionary view populated.

V$COPY_CORRUPTION
V$BACKUP_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION

Using RMAN to Validate Database Files
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkup005.htm#i1006673

5. EXPORT/IMPORT command line utility

Full database EXPORT/IMPORT show=y is another method.

. about to export SCOTT’s tables via Conventional Path …
. . exporting table BONUS
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 4, block # 43)
ORA-01110: data file 4: ‘C:\ORA10GHOME\ORADATA\ORCL10G\USERS01.DBF’

6. DBMS_REPAIR package

dbms_repair is a utility that can detect and repair block corruption within Oracle. It is provided by Oracle as part of the standard database installation.

http://www.oracleutilities.com/Packages/dbms_repair.html

How to Repair & Fix block corruption?

We can recover everything but we have valid database backup.
Whenever we found block corruption then first need to find out which type of block corruption occurred because block corruption recovery depends on block corruption type.

Like Corrupted block related to TABLE segment, INDEX segment, TABLE
PARTITION segment, INDEX PARTITION segment, ROLLBACK segment, LOB segment.

Through below query we can find out corrupted block type

select segment_type,owner’.’segment_name
from dba_extents
where file_id = [&file_id] and [&block] between block_id and block_id+blocks -1;

Below is example with RMAN BLOCK MEDIA RECOVERY.

SQL> conn scott/tiger
Connected.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 11)
ORA-01110: data file 5: ‘C:\INDEXDATA01.DBF’

First check which type of block corruption happened through above mentioned query.

RMAN> blockrecover datafile 5 block 11;
Starting recover at 29-APR-08using channel
ORA_DISK_1 starting media recoverymedia recovery complete,
elapsed time: 00:00:00
Finished recover at 29-APR-08

If you are not using rman then applying below procedure
– if it is index then drop and recreate index
– if it is table and you have backup of that table then restore backup on another database and exp/imp the table.

How to corrupt database block for practice purpose?
On Unix:
Use dd command
$man dd

On Windows:
Use Editor and open datafile write some junk character at middle of file and save it.

i would thank taj for this amazing artical .

crsctl start crs does not work in 10gR2

Posted on by Osama Mustafa in Database

How to start crs in oracle 10g , its simple first we need to check if its really exists or not :

1-./crsctl start crs

Attempting to start CRS stack
The CRS stack will be started shortly

2- ./crsctl check crs

Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM

3- ./srvctl status nodeapps -n 

PRKH-1010 : Unable to communicate with CRS services.
[Communications Error(Native: prsr_initCLSS:[3])]


4- ./srvctl start nodeapps -n 

PRKH-1010 : Unable to communicate with CRS services.
[Communications Error(Native: prsr_initCLSS:[3])]

5- ./srvctl status nodeapps -n 

PRKH-1010 : Unable to communicate with CRS services.
[Communications Error(Native: prsr_initCLSS:[3])]

6- ./crsctl check crs

Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM

     --------------------------------------------------------------
if you see the crs is still not working (start) so what is the solution if "srvctl" not working .

What can i do ????
its so simple just one command to fix this issue : 
./crsctl start resources

Steps To Shutdown/Start Oracle RAC

Posted on by Osama Mustafa in Database

Hi Again

I will mention steps to shutdown Oracle Real Application Cluster First :
You need to shutdown all the services like the following :

On Linux :
**Change to $ORACLE_HOME/crs/bin

shutdown emctl stop dbconsole .

1- ./srvctl stop database -d .

To check database name :

select DB_name from v$database ;

2-./srvctl stop asm -n

To check node name :
Hostname on terminal .

3-./srvctl stop nodeapps

4-./crsctl stop -all

these steps to stop RAC services on linux :

Windows :

** change to $ORACLE_HOME/crs/bin
1-srvctl.exe stop database -d
2-srvctl.exe stop asm -n
3-srvctl stop nodeapps
4-crsctl.exe stop -all .

Startup RAC services :

1-./srvctl start nodeapps
2-./srvctl start asm -n .
3-./srvctl start database -a .
4-./crsctl start -all .

emctl start dbconsole .

windows the same but without “./” just srvctl.exe

Useful Command in RMAN

Posted on by Osama Mustafa in Scripts

List Command :

1)RMAN> list backup;
List all your backup sets.
***************
2)RMAN>LIST BACKUPSET;
Lists only backup sets and proxy copies.
***************
3)RMAN>LIST COPY;
Lists of Image copies and Archive Logs.
***************
4)RMAN>LIST EXPIRED BACKUP;
Backups did not found after crosscheck. That is backup is manually moved or deleted from OS.
***************
5)RMAN>LIST BACKUP BY FILE;
List backup by Datafile, controlfile, spfile.
***************
6)RMAN>LIST BACKUP SUMMARY;
Lists backup sets, proxy copies, and disk copies.
***************
7)RMAN>LIST BACKUP OF DATABASE;
LIST BACKUP LIKE ‘/tmp/%’; list backup of datafile 1; LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;LIST COPY OF DATAFILE 2 COMPLETED BETWEEN ’17-MAR-2008′ AND ’22-MAR-2008′; are also available.
***************
8) RMAN>LIST INCARNATION;
LIST INCARNATION OF DATABASE; to see the incarnations of your database.

Report Command:

RMAN REPORT command analyzes the available backups and return results about while files need backup which files are obsolete etc.

1)REPORT NEED BACKUP;
Determine which database files need backup under a specific retention policy.
***************
2)REPORT UNRECOVERABLE;
Report which database files require backup because they have been affected by some NOLOGGING operation.
***************
3)REPORT SCHEMA;
Lists and displays information about the database files.
***************
4)REPORT OBSOLETE;
REPORT OBSOLETE displays the backups that are obsolete according to the current retention policy.
***************
5)REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE data01;
If you use Recovery Catalog then past data can be shown.Like, REPORT SCHEMA AT TIME ‘SYSDATE-10’;
***************

Delete Command:

Delete commands is used to delete any backup or backupsets.

1)RMAN> delete backupset all;
Delete all your backup sets
***************
2) RMAN> delete copy all;
Delete all image copies.
***************
3) RMAN>DELETE OBSOLETE;
Delete obsolete Backups.
***************

thank you all .

ORA-00060 RUNNING BACKUPS FOR 2 DATABASES USING SAME CATALOG

Posted on by Osama Mustafa in Database

hi All ,

All of us using backup techniques maybe it will be different but the aim is one , saving your data
some error could be appear while you doing backup but everything can be solved one of this errors
deadlock error

but what is this mean ?

deadlock : situation where in two or more competing actions are each waiting for the other to finish

so when you have two backup not necessary backup btw it could be session , backup-1 will wait backup-2 and vice verse …

In rman backup its Bug 6830296 accroding to meta-link ID 779095.1

Solution is :

In Rman catalog database create the following indexes :-

CREATE INDEX tfatt_i_sck on tfatt(start_ckp_key)
CREATE INDEX tfatt_i_eck on tfatt(end_ckp_key)

Or

Check for availability of one off patch using the following link Patch 6830296

thank you all