Category: Database

Avoid Backup Database with corrupted Archivelog

Posted on by Osama Mustafa in Database

Corrupt block seq: 34229 blocknum=1.
Bad header found during deleting archived log
Data in bad block – seq:0. bno:0. time:0
beg:0 cks:0
calculated check value: 0

No need for Oracle DBA to tell you there’s corruption , usually this error indicate that archivelog is corrupted in our case Archivelog number 34229.

To Solve this problem :

1- Check Validate for this archivelog

 RMAN> validate archvielog sequence 34229;

 2 – Now to avoid this error , you should delete archivelog on Os level.
3 –

RMAN > crosscheck archivelog all;
DELETE EXPIRED ARCHIVELOG sequence 34229;

Then Backup database.

 Thank you
Osama Mustafa

Limit The Access To The Database

Posted on by Osama Mustafa in Database
In this Article, i explain how to limit access to database for only one user per schema which mean one concurrent user per schema.

Resource_limit should set to True

SQL> show parameter resource

NAME                                 TYPE        VALUE
———————————— ———– ——————————
resource_limit                       boolean     TURE
resource_manager_plan                string

After change this parameter Bounce database.

Connect to database using sysdba privileges 

sqlplus / as sysdba

create profile Only_one_user limit sessions_per_user 1;

Create New User/modify old one depend on what you want:

create user test identified by test profile Only_one_user;
grant connect to test;

Now Try to connect to this user using more than one terminal, if you did you will receive error

ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

Thank you
Osama Mustafa

Most Common Dbcontrol issue

Posted on by Osama Mustafa in Database
After while on Different oracle Forums, I notice that people Facing lot of issues with dbcontrol/dbconsole, Today i will post the most common Issue about dbcontrol and how to solve it.

Notice that sometime the Solution will be different Regarding to DB version and OS Version I will Post Some MOS notes that could help you. Sometimes you need to apply Patch and on other hand there’s Workaround.

Error #1 : 

Exception in thread “main” oracle.sysman.emcp.exception.DatabaseUnavailableException: Database instance unavailable

Solution :

  1. -Make Sure you Database is Up and Running.
  2. -Set/export ORACLE_SID.
  3. -Check if Database registered with Listener.

You can get back to this MOS note :
EMCA fails with “SEVERE: Database instance unavailable” [ID 750697.1]

EMCA fails with Database Instance is unavailable. Fix the ORA error thrown and run EM Configuration Assistant again. [ID 1511262.1]

Creating dbconsole 11.2 fails with “Exception in thread “main” oracle.sysman.emcp.exception.DatabaseUnavailableException: Database instance unavailable” on Windows [ID 1332546.1]

Error #2:

Running EMCA fails with “SEVERE: Invalid username/password”

Solution :

  1. -Make Sure You Insert Correct Password.
  2. -In Some Version you can use “Password” 
  3. -Recreate Password File.

You can Get back to this MOS note :
Problem: Running EMCA fails with “SEVERE: Invalid username/password” [ID 744176.1]

EMCA fails with SEVERE: Invalid username/password or database/scan listener not up or database service is not registered with scan listener. [ID 1330272.1]

Error  #3:

WARNING: ORA-01031: insufficient privileges

Solution

You need to check the logs, different error could be appear.

Failed to unlock all EM-related accounts

Alter User Identified By

Also Check MOS Notes :
Troubleshooting the ‘ORA-01031: insufficient privileges’ error when using EMCA to Create or Drop DBconsole [ID 358201.1]

Error #4:

SEVERE: Listener is not up or database service is not registered with it. Start the Listener and register database service and run EM Configuration Assistant again .

Solution :

  1. – set/export ORACLE_SID
  2. -Check your Listener.
  3. -Check if Database Registered.

Check the below MOS note :

Troubleshooting the EMCA Error “ORA-12514” “Listener is not up. Start the Listener and run EM Configuration Assistant again” When Creating or Dropping DBConsole [ID 368591.1]

EMCA Fails With Error “ORA-12541: TNS:no listener” and “Listener is not up. Start the Listener and run EM Configuration Assistant again” [ID 975024.1]

DBMS_METADATA

Posted on by Osama Mustafa in Database
The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

TableSpace

select dbms_metadata.get_ddl(‘TABLESPACE’,tablespace_name) from dba_tablespaces;

Get All Tablespace Script :

set head off echo off
select ‘select dbms_metadata.get_ddl(”TABLESPACE”,”’
  ||  tablespace_name || ”’) from dual;’ from dba_tablespaces


Table

 select dbms_metadata.get_ddl(‘TABLE’,’DEPT’,’SCOTT’) from dual;

User

SELECT dbms_metadata.get_ddl(‘USER’,’SCOTT’) FROM dual;

Role

SELECT DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SCOTT’) from dual;
 SELECT DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,’SCOTT’) from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,’SCOTT’) from dual;
SELECT dbms_metadata.get_ddl(‘ROLE’,’RESOURCE’) from dual;

This is the Most common Usage for this package more information read Oracle Documentation
 
Thank you
Osama mustafa

What is ORA-00600

Posted on by Osama Mustafa in Database
The ORA-600 error is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition.

ORA 600 “internal error code, arguments: [%s], [%s],[%s], [%s], [%s]”

and you should it’s different Oracle errors , Because when you see this error then it’s indicating for bugs, the above is general description for the error the first characterset / Number is is used with database version to identify the problem by oracle support. and maybe you will find related document on https://support.oracle.com.

When you face this error you should check the below document searching for some Notes/Patch could help you :

 Note 600.1 ORA-600/ORA-7445 Lookup tool

You need to Choose database version ( 4 digit ) and First argument in the error, also for more information how to use this tool :

 Note 1082674.1 : A Video To Demonstrate The Usage Of The ORA-600/ORA-7445 Lookup Tool [Video]

Notice when you check the alert log and see this error, a trace file is generated and an entry is written to the alert.log with details of the trace file location, trace file provide you with more information about the error that could help you to solve it. you can check the below document how to use trace file :

Note 453125.1 11g Diagnosability Frequently Asked Questions
Note 443529.1 11g Quick Steps to Package and Send Critical Error Diagnostic Information to Support[Video]

Most Common Reason for ORA-00600 File-Corruption, Failure in Hardware, I/O , or memory, to solve this error you need to do some steps before Open Services Request :

  1. Check Alert Log .
  2. Don’t forget to look at the Ora-00600 Tools.
  3. If you find any Notes related to your problem  use it and read it carefully.
  4. The last option you could help is contact Oracle Support by open Services Request but provide the full information such as:
      1. alertlog for database.
      2. Traces.
      3. If any change happened lately included with SR.
      4. I post About RDA it’s useful to use it when you open SR.

Thank you
Osama Mustafa

ORA-01031: insufficient privileges When Start Windows Services

Posted on by Osama Mustafa in Database
this common error in windows, and you can be occur for more than one reason and you need to check them all to make sure you did everything is right, the error prevent the oracle services to start automatically and if you check

ORADIM.LOG….
**************************
C:\Oracle\Ora11g\bin\oradim.exe -startup -sid ptdb -usrpwd * -log oradim.log -nocheck 0
Thu Nov 15 15:16:15 2012
ORA-01031: insufficient privileges

You have to check :

1- startup type for the services ( if it’s set automatically )
2- Group to user ( the user should be in ORA_DBA Group )
3 – you can check log on tab in the service properties and set the username and password who responsible about this services and can do it by:

Go to Control Panel -> Administrative Tools -> Services .

Choose oracle services and then
click “properties”. Select the “Log On” tab and note the account that is set to run the service.

Thank you
Osama mustafa

Remote Diagnostic Agent

Posted on by Osama Mustafa in Database
RDA : It’s utility collect diagnostics information about an Oracle environment, And could be downloaded from https://support.oracle.com for every Operating system there’s specific version for RDA.

RDA is Command Line diagnostic tool that is executed by an engine written in the Perl programming language,The data captured provides Oracle Support with a comprehensive picture of the customer’s environment which aids in problem diagnosis, You can check the below MOS Note :
Remote Diagnostic Agent (RDA) 4 – FAQ [ID 330363.1]

Oracle Support encourages the use of RDA because it greatly reduces service request resolution time by minimizing the number of requests from Oracle Support for more information.RDA Support Most Of Operating system.and also it’s supported for Most Oracle Products.

But why should i use RDA, as i mention before oracle support encourages to use RDA to collect information and also there’s another reasons to use it :

  •  Oracle Fusion Middleware issues
  • Oracle Collaboration products.
  • Oracle Application issues.
  • Installation/configuration issues
  • Performance issues
  • ORA-600, ORA-7445, ORA-3113.
  • Upgrade and migration.
  • Developer issues
  • Oracle Database issues

The Simplest way to review RDA Output is using Html , Web browsing since after run rda.sh the output will generate on the same RDA Folder. (RDA_Output).

After review MOS note :
RDA 4 – Health Check / Validation Engine Guide [ID 250262.1]

after finishing from  a prerequisite system you need to need 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.

./rda.sh -T hcve

The sample output could be like the below :

Test Results
~~~~~~~~~~~~
ID NAME RESULT VALUE
===== ====================

20 User in /etc/passwd? PASSED userOK
30 Group in /etc/group? PASSED GroupOK

 Thank you
Osama Mustafa

 

Use Order By With Delete Statement

Posted on by Osama Mustafa in Database
if you are using order by in sub query with delete statement like the below :

 delete from tAccountScoring where riskscoring_id=1 and id in
(select * from tAccountScoring where last_update <= sysdate-3 AND rownum < 3  AND ACC_ID = 251 over (order by last_update desc))

You will have an error :

Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 –  “missing right parenthesis”
*Cause:   
*Action:

The workaround to solve this issue is simple like the below :

delete from tAccountScoring
where riskscoring_id=1 and id in
(select ID  from (select * from tAccountScoring
where last_update <= sysdate-3 AND rownum < 3  AND ACC_ID = 251 order by last_update))

Thank you
Osama Mustafa

AWR Vs StatPack

Posted on by Osama Mustafa in Database
When you face performance issue in database the first thing coming to your mind is Automatic Workload Repository (AWR) or STATPACK reports but what is the difference between them, in this article i will try to mention as much as i can the difference between them.

1-you should be aware that AWR not exists in database 9i so you forced to use statepack, include to that steps to generate AWR much easier than STATPACK.

2-AWR hold all the information and statistics that exists in STATPACK, include to that AWR hold Additional Information.

3- in AWR you will find information called Active Session History ( ASH ) which is not exists in STATPACK.

4- To generate STATPACK you should Run Procedure to enable snapshot, you can use DBMS_JOB or schedule it using crontab.

5- AWR snapshots provide a persistent view of database statistics. A snapshot is a collection of performance statistics that are captured at a specific point in time,AWR snapshots are scheduled every 60 minutes by default.

 6- Statspack snapshot purges must be scheduled manually otherwise AWR snapshots are purged automatically using MMON.

Thank you
Osama Mustafa