Tuesday, July 31, 2012

Use RMAN to Manage Oracle Files / DataFiles

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/app/oracle/oradata/ORCL/system01.dbf
2    1150     SYSAUX               ***     /u01/app/oracle/oradata/ORCL/sysaux01.dbf
3    444      UNDOTBS1             ***     /u01/app/oracle/oradata/ORCL/undotbs01.dbf
4    120      USERS                ***     /u01/app/oracle/oradata/ORCL/users01.dbf
5    345      EXAMPLE              ***     /u01/app/oracle/oradata/ORCL/example01.dbf
8    3277     SOE                  ***     /u01/app/oracle/product/11.2.0.2/db_1/dbs/soe.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    370      TEMP                 32767       /u01/app/oracle/oradata/ORCL/temp01.dbf

RMAN>

Copy the file(s) to the new location.

RMAN> COPY DATAFILE 8 TO '/u01/app/oracle/oradata/ORCL/soe.dbf';
Turn the tablespace to offline. We could have turned the tablespace offline before the copy, removing the need for a recovery, but the tablespace would have been offline longer using that method.

RMAN> SQL 'ALTER TABLESPACE osama OFFLINE';
Switch to the new datafile copy(s) and recover the tablespace.

RMAN> SWITCH DATAFILE 8 TO COPY;
RMAN> RECOVER TABLESPACE osama;
Remove the old datafile(s).


Done .
Osama Mustafa

Manage Oracle Files / DataFiles Part 3

This will be the last part for manage Oracle Database files we will mention another way using RMAN

SQL> SELECT name FROM v$datafile;

NAME
---------------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\USERS01.DBF

4 rows selected.

SQL>

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

You can rename Datafiles , Move it now .

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL> ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\USERS01.DBF' -
>  TO 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_USERS01.DBF';

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>

Done

Enjoy
Osama mustafa

Friday, July 27, 2012

ORA-12557: TNS:protocol adapter not loadable

this problem On windows Platforms .

and it must be related to Windows Environment or Oracle Home PATH because sqlplus command works smoothly when I execute it inside ORACLE_HOME\bin.

RUN: SYSDM.CPL to open Windows System Properties
Click on Advanced Tab > Environment Variables…

Click the Path variable under System Variable, then click  Edit…

change the order between Oracle Client Home and Oracle DB Home:

From: D:\oracle\product\10.2.0\client_1\bin;D:\oracle\product\10.2.0\db_1\bin;

To: D:\oracle\product\10.2.0\db_1\bin;D:\oracle\product\10.2.0\client_1\bin;

in other words, put the Oracle DB Home in front of the other path.

Or i Don't love this way , Since its not actual solution but its solve problem sometimes :
1-Remove ORACLE_HOME From environment Variable .


2- Restart PC 
Done

Osama mustafa 


ORA-39152: Table exists

Error :
ORA-39152: Table exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append.


Cause :

Using APPEND to import the existing tables, as to not overrite them gives the following error:





Solution :

Truncating the table preserves the structure of the table for future use, so you are seeing this error message because there is a constraint or index in place.

To get around this you can use the following DataPump import parameters:
CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND EXCLUDE=INDEX,CONSTRAINT


Thank you
Osama mustafa

Thursday, July 26, 2012

Cursor in EXECUTE IMMEDIATE

Sometimes you want to use cursor without Open Cursor,you can use ref cursor .

syntax :

declare
   SQL_Text varchar2(32760) := 'qurey'; --your query goes here
   cur sys_refcursor;
begin
   open cur for SQL_Text;
end;
 

example :
V_query := 'Cursor statement' ;

declare
  rc sys_refcursor;
begin
  open rc for ;
  loop
    fetch rc into variable;
    exit when rc%notfound;
    <do your process.>
  end loop;
  close rc;
end;
Link Useful :
1-Blog
2-Cursor Loop Example 

Enjoy

Osama mustafa

Wednesday, July 25, 2012

Tablespace growth

Sometimes you need to know how much your tablespace grow this month find below some scripts to do this :

Script-1 :

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;

Script-2: 

column “Percent of Total Disk Usage” justify right format 999.99
column “Space Used (MB)” justify right format 9,999,999.99
column “Total Object Size (MB)” justify right format 9,999,999.99
set linesize 150
set pages 80
set feedback off
set line 5000
column “SEGMENT_NAME” justify left format A30
column “TABLESPACE_NAME” justify left format A30
select * from (select c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, ‘MM/DD/YY’) mydate, sum(space_used_delta) / 1024 / 1024 “Space used (MB)”, avg(c.bytes) / 1024 / 1024 “Total Object Size (MB)”,
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) “Percent of Total Disk Usage”
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) – 10
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = ‘S_PARTY’
group by c.TABLESPACE_NAME,c.segment_name,to_char(end_interval_time, ‘MM/DD/YY’)
order by c.TABLESPACE_NAME,c.segment_name,to_date(mydate, ‘MM/DD/YY’));

Script-3:
set pages 80
set feedback off
column “OBJECT_NAME” justify left format A30
column “SUBOBJECT_NAME” justify left format A30
column “OBJECT_TYPE” justify left format A30
column “Tablespace Name” justify left format A30
set line 5000
SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
t.NAME “Tablespace Name”, s.growth/(1024*1024) “Growth in MB”,
(SELECT sum(bytes)/(1024*1024)
FROM dba_segments
WHERE segment_name=o.object_name) “Total Size(MB)”
FROM DBA_OBJECTS o,
( SELECT TS#,OBJ#,
SUM(SPACE_USED_DELTA) growth
FROM DBA_HIST_SEG_STAT
GROUP BY TS#,OBJ#
HAVING SUM(SPACE_USED_DELTA) > 0
ORDER BY 2 DESC ) s,
v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS#
AND o.OWNER=’SIEBEL’
ORDER BY 6 DESC
/
Script-4:

set feedback on
select * from (select c.TABLESPACE_NAME,c.segment_name “Object Name”,b.object_type,
sum(space_used_delta) / 1024 / 1024 “Growth (MB)”
from dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) – &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner =’SIEBEL’
group by c.TABLESPACE_NAME,c.segment_name,b.object_type)
order by 3 asc;


Thank you 

Tuesday, July 24, 2012

UTL_MAIL Or send email from Oracle Database


-->
Steps to enable Mailing from Database:
1. sqlplus ‘/ as sysdba’
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/utlsmtp.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb
  SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
 
4. Set smtp_server information in init.ora or spfile.ora like the following you have to change with right configuration for yourself :
alter system set smtp_out_server = ‘SMTP_SERVER_IP_ADDRESS:SMTP_PORT’ scope=both;
Note : 25 = Default SMTP Port
If instance had been started with spfile
eg: alter system set smtp_out_server = ’172.25.90.165:25′ scope=both;
Thats It, your database is configured to send emails ….


How to send an email

1. sqlplus ‘/ as sysdba’
2. exec utl_mail.send((sender => ‘omustafa@savvytek.com’, recipients => ‘omustafa@savvytek.com’, subject => ‘database alert’, message => ‘database is corrputed’);
3. Check the inbox of the email id, to verify the email receipt.
To enable other DB users to use this functionality, grant execute permission on UTL_MAIL package.
eg: grant execute on utl_mail to omustafa;


Enjoy 
osama mustafa





Table Locks

query to get the locked tables in oracle :
SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||’.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,’NONE’,
1,’NULL’,
2,’ROW SHARE’,
3,’ROW EXCLUSIVE’,
4,’SHARE’,
5,’SHARE ROW EXCLUSIVE’,
6,’EXCLUSIVE’,
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id  ;

Or you can do the below :
 select * From v$locked_object;
select * From v$session where SID = '<>';
select * from dba_objects where object_id = '<>';

 Locked objects :

Select object_name, owner, object_type from dba_objects
Where object_id in (select object_id from v$locked_object);

KILL SESSION COMMAND
ALTER SYSTEM KILL SESSION 'SID,Serial#' IMMEDIATE; 


Osama Mustafa

Monday, July 23, 2012

drop all tables in a tablespace

set echo off
prompt
prompt this script is used to generate SQL file for deleting all tables in a tablespace.
prompt
prompt specify tablespace name 1:
define tsname=&1
prompt Add purge clause, y for yes, n for no 2:
define prg=&2
prompt enter output sql file name 3:
define filename=&3
set heading off
set verify off
set feedback off
start del_ts_tb.sql
set verify on
set heading on
set echo on
set feedback on

spool &filename
select 'drop table ' || owner || '.' || table_name || decode(upper('&prg'),'Y',' purge','') || ';' DropState
from dba_tables
where tablespace_name = upper('&tsname');
spool off 





Osama mustafa

knowing the transaction status

**All tranasactions/sid/username

select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec
from v$transaction t, v$session s
where t.ses_addr=s.saddr;

**All tranasactions/sid/username/first 64 bytes of SQL:


select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec,sql.sql_text
from v$transaction t, v$session s, v$sql sql
where t.ses_addr=s.saddr
and s.sql_address=sql.address and s.sql_hash_value=sql.hash_value;

Enjoy

Osama mustafa

Use TABLE_EXISTS_ACTION in Impdp

Those who are familiar with oracle data pump may know very well about TABLE_EXISTS_ACTION import (impdp) parameter.

TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}

This parameter is used when you import a table which is already exists in import schema. The default value is 'SKIP', so if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.

Now you may interested about rest of the three values-

APPEND - The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).

TRUNCATE - If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.

REPLACE - This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.

Sunday, July 22, 2012

Shell Script For Cold Backup

This shell script selects the datafiles, logfiles and control files,
 tars and gzips them and then sends them to a remote host via rsh.


Download Scripts : Cold_backup.sh

I have to upload the script since its contain codes can't be appeared on Blog .


Enjoy
osama mustafa

Manage Oracle Files / Rename Or Move Logfiles Part-2

In First Topic Part 1 we talked about how to move/Rename Control File Today the same topic but for different file LogFile Let start 


SQL> SELECT member FROM v$logfile;
MEMBER
-------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO03.LOG
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO02.LOG
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO01.LOG

3 rows selected.

SQL> 

To move or rename a logfile do the following.
  • Shutdown the database.
  • Rename the physical file on the OS.
  • Start the database in mount mode.
  • Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.
  • Open the database.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
 
Rename/Move logfile to what you want/where you want 
 
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
 
 ALTER DATABASE RENAME FILE 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\REDO01.LOG' -
>  TO 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_REDO01.LOG'; 

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL>
 
 
 
The Last topic will be About DataFiles . 
Enjoy 
 
Osama mustafa 

Thursday, July 19, 2012

ORA-1122 :database file %s failed verification check

After trying to make Partation on tables i received the following Error :

ORA-1122 :database file %s failed verification check
ORA-1110 : datafile :
ORA-1207: ORA 1207 file is more recent than control file

Solution

1.Mount the database
SQL> Startup mount


2. Save the information from the control file:
SQL> Alter database backup controlfile to trace;


3. Create a control file creation script from the tracefile generated in user_dump_dest.
Use the Noresetlogs option


4. Shutdown the database and start it in NOMOUNT mode

SQL> shutdown abort
SQL> startup nomount


5. Create the control file
6.Recover the database
SQL> recover database;

7. Open the database
SQL> Alter database open;


Thank you
Osama mustafa

Wednesday, July 18, 2012

Rename/Move Oracle Files / Control FIle Part 1

In this Topics i will post how to move control file to another detestation , i will post other ORACLE FILES :

SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL01.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL

3 rows selected.

SQL>
 
OR
 
SQL> show parameter control_files
 
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
control_files                        string                           C:\ORACLE\ORADATA\DB10G\CONTRO
                                                                      L01.CTL, C:\ORACLE\ORADATA\ORCL
                                                                      \CONTROL02.CTL, C:\ORACLE\OR
                                                                      ADATA\ORCL\CONTROL03.CTL
SQL> 


To move or rename a controlfile do the following.
  • Alter the control_files parameter using the ALTER SYSTEM comamnd.
  • Shutdown the database.
  • Rename the physical file on the OS.
  • Start the database.
SQL> ALTER SYSTEM SET control_files='C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_CONTROL01.CTL', -
> 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL', -
> 'C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL' SCOPE=SPFILE;
 
 
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
 
RE-NAME CONTROL FILE TO THE SAME WE DID ON ALTER STATMENT .
 
SQL> STARTUP
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                   787968 bytes
Variable Size              61864448 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
SQL>
 
SQL> select name from v$controlfile;

NAME
-------------------------------------------------------------
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\RENAME_CONTROL01.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL02.CTL
C:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\CONTROL03.CTL

3 rows selected.

SQL> 

the same steps for move control file .
W

We will continue 
 
Enjoy 

osama mustafa 
 
 

Gather Schema Statistics How to Use it !!!!

Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

As a general rule, run Gather Schema Statistics under the following circumstances:
1.  After there has been a significant change in data in either content or volume.
2.  After importing data.
3.  Any time end-users notice deterioration in performance in routine day-to-day business transactions or when running concurrent programs.
 4.  Run on a regular basis (weekly at a minimum) and anytime after application of patch, conversion, etc.

Type :

  1. GATHER_INDEX_STATS, Index statistics
  2. GATHER_TABLE_STATS, Table, column, and index statistics
  3. GATHER_SCHEMA_STATS,Statistics for all objects in a schema
  4. GATHER_DICTIONARY_STATS,Statistics for all dictionary objects
  5. GATHER_DATABASE_STATS,Statistics for all objects in a database
Example :

begin
 dbms_stats.gather_schema_stats
 (ownname => 'Scott', 
 estimate_percent => dbms_stats.auto_sample_size E "sample_size",
 options => 'GATHER EMPTY'
 );
end;
 
 
 
The above call starts the gather_schema_stats for a schema called SCOTT with estimate_percent at default sample size and options gather empty which gathers statistics on objects which currently have no statistics.


You can disable automated statistics collection job using the code below :
EXEC dbms_scheduler.disable(’GATHER_STATS_JOB’);
 
 To re-enable the job:
 
 
EXEC dbms_scheduler.enable(’GATHER_STATS_JOB’);
 To check if it job running or not 

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
 
 
Thank you 
Osama mustafa 
 
 

Sunday, July 15, 2012

Script to Generate Grant in Database

Sometime when you want to create test environment you need to sure it will Identical and make sure grant are the same .


the below scripts include all grants made by user.
P.S :
  • For non-Windows platforms, change the second-last line, "host notepad", to call your favorite text editor instead. 
  • You will need to input a list of users.
  • If you don't know the passwords of some of the users, wait until they're not logged on, copy their encrypted passwords from dba_users, reset their passwords to a temporary value, run the generated script, and then reset their passwords to their original values using alter user ... identified by values '...'
  • Before you run the generated script, you need to know the passwords of all the users who will be making grants. Search the generated script for "connect" to figure out who this is. 

I upload the scripts as grant.sql


Enjoy

Osama Mustafa

prct-1011 failed to run getcrshome

INFO: Done parsing command line arguments.
INFO: PRCT-1011 : Failed to run "getcrshome"
INFO: at oracle.cluster.deployment.ClusterwareInfo.getConfiguredCRSHome(ClusterwareInfo.java:423)
INFO: at oracle.sysman.assistants.util.hasi.HAUtils.getCRSHome(HAUtils.java:546)
INFO: at oracle.sysman.assistants.util.hasi.HAUtils.getCRSHome(HAUtils.java:517)
INFO: at oracle.net.ca.InitialSetup.configureOPS(NetCA.java:4511)
INFO: at oracle.net.ca.InitialSetup.(NetCA.java:4024)
INFO: at oracle.net.ca.NetCA.main(NetCA.java:405)
INFO: Caused by: PRCT-1011 : Failed to run "getcrshome"


Cause

The netca is looking to get information for CRS Home even if the CRS Home was not installed. Probably a CRS home was installed earlier and was not completely removed.

 Regarding to metalink  the solution for this problem like the following :

Rename the file /var/opt/oracle/ocr.loc  and then perform a fresh installation.

Try to Run Netca Again ..

You can check document on MOS under
Netca Fails To Start with error PRCT-1011: Failed to run "getcrshome" [ID 1302284.1]


Enjoy 

Osama Mustafa

Saturday, July 14, 2012

ora-29540 class oracle/jpub/runtime/dbws/dbwsproxy does not exist

ERROR at line 1:
ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist
ORA-06512: at "SYS.UTL_DBWS", line 195
ORA-06512: at "INTERFACE.GET_JOKE", line 13
 
Logout of sqlplus and run:
loadjava -u / -r -v -f -s -grant public -genmissing  dbwsclientws.jar dbwsclientdb102.jar
 
 
Enjoy 
osama mustafa  

Wednesday, July 11, 2012

Opening the database with corrupted redo log

ORA-00333: redo log read error block 9233 count 2312

Opening the database with corrupted redo log can cause a loss of committed transactions, therefore, you need to do it at your own risk

Steps :

SQL> Startup Mount;
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 779000 bytes
Variable Size 229383432 bytes
Database Buffers 50331648 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;

ORA-00280: change 101350984923848 for thread 1 is in sequence #2333


Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/oracle/oradata/rsdb/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;

SQL>shutdown immediate;
SQL>Startup;


You can Do this Steps When Data Its Not Important ,  Such As Test Evn , ....

Enjoy

Osama Mustafa

Agent Configuration Assistant Failed/Grid Control

Troubleshooting the error oracle.sysman.emcp.agent.AgentPlugIn raised by the Agent Configuration Assistant (AgentCA)  [740628.1]

Troubleshooting the 'oracle.sysman.emcp.agent.AgentPlugIn has failed' error
[
734981.1]


the agent failed because I entered an incorrect agent password during the install.   Unfortunately there is no way to re-enter the password within OUI at this point so login to your server and  follow the steps below:

  1. cd $AGENT_HOME/bin
  2. ./emctl stop agent
  3. ./emctl unsecure agent
  4. ./emctl secure agent 
    • At this point you will be prompted for the agent registration password.  If you input the incorrect password it will fail.   Run this again until you enter the correct password.

  5. ./emctl start agent
Now you should be able to login to your Grid Control console and see this target.

Enjoy

Osama mustafa 

Data Masking In Oracle/Column Masking

Or We Can Call it VPD : Virtual Private Database

What is Data Masking Mean ? 

simple way to hide you valuable data from certain users without having to apply encrypt/decrypt techniques and increase the column width to accommodate the new string like the old times. Through some simple configuration you can create policies to show your important columns as null without rewriting a single line of code on your application side.



There are 3 steps for accomplish column masking:
  1. A function to be used by the policy (function policy) created in next step.
  2. Use dbms_rls package to create the policy.
  3. Assign “exempt access policy” to users to be excluded from the policy. These users can see all data with no masking.
Step1 : Create Function Policy 

CREATE OR REPLACE
FUNCTION vpd_function (obj_owner IN VARCHAR2, obj_name IN VARCHAR2)
RETURN VARCHAR2
AS
BEGIN
RETURN 'rowid = ''0''';
END vpd_function;
/

The Above Function is Used for Column Masking , If you set this function to True All User will be able to see the correct Data , But the above function Is to False (rowid=0).

Step2: Create Policy

BEGIN
DBMS_RLS.ADD_POLICY(object_schema=> 'SCOTT',
object_name=> 'EMP',
policy_name=> 'scott_emp_policy',
function_schema=> 'SYSTEM',
policy_function=> 'vpd_function',
sec_relevant_cols=> 'JOB',
policy_type => DBMS_RLS.SHARED_STATIC,
sec_relevant_cols_opt=> dbms_rls.ALL_ROWS);

END;
/
exempt access policy : Use to Exclude Some Users to See All the Correct Data .

Important Views :

dba_policies
v$vpd_policy

Enjoy with Security

Osama Mustafa

Tuesday, July 10, 2012

dbstart script/ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener


 
-bash-3.2$ dbstart
 
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart ORACLE_HOME
Processing Database instance "orcl": log file /u01/app/oracle/product/11.2.0/dbhome_1/startup.log
 
 
And i try to do the following 
 
-bash-3.2$ export ORACLE_HOME_LISTNER=$ORACLE_HOME
-bash-3.2$ dbstart
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbstart ORACLE_HOME
Processing Database instance "orcl": log file /u01/app/oracle/product/11.2.0/dbhome_1/startup.log
 
Open dbstart and check the Scripts 
 
# First argument is used to bring up Oracle Net Listener
ORACLE_HOME_LISTNER=$1
if [ ! $ORACLE_HOME_LISTNER ] ; then
  echo "ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener"
  echo "Usage: $0 ORACLE_HOME"
 
 
Try the following Solution , over ride value inside dbstart with the below command 
 
-bash-3.2$ dbstart $ORACLE_HOME
 
Enjoy 
 
Osama mustafa
 
 

Monday, July 9, 2012

Dealing With Oracle Jobs/DBMS_JOB

scheduled_dbms_jobs.sql

set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60

select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;
running_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;
session_jobs.sql

set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;

Bringing Down a DBMS_JOB

1. Find the Job You Want to Bring Down
2. Mark the DBMS_JOB as Broken
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);

All this command does is mark the job so that if we get it to stop, it won't start again. Let's make one thing perfectly clear, after executing this command the job is still running.

3. Kill the Oracle Session
ALTER SYSTEM KILL SESSION 'sid,serial#';
 4. Kill the O/S Process

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill '9 spid
5. Check if the Job is Still Running

If No , Then you are Done , But if Job Still Running Go To Step 6.

6. Determine the Current Number of Job Queue Processes

SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes'; 

7. Alter the Job Queue to Zero
SQL> ALTER SYSTEM SET job_queue_processes = 0;

 8. Validate that No Processes are Using the Job Queue
9. Mark the DBMS_JOB as Not Broken
SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):
10. Alter the Job Queue to Original Value

ALTER SYSTEM SET job_queue_processes = original_value;
11. Validate that DBMS_JOB Is Running

Enjoy

Osama mustafa




Recursive Calls In Oracle

you will find Recursive Call in AWR at most , and you have to understand what we mean in this , why we use it , Simple way to discuss this . its below .

WHAT ARE RECURSIVE CALLS?

In Oracle, a recursive call is a call, for example, that must be completed before the user's SQL can be completed.

Say you wanted to order a pizza for delivery. You make one call, place your order, and wait for your pizza.

You do NOT call the kitchen staff directly and tell them how to make your pizza , call the cashier to give him your credit card number, call the delivery man and tell him to pick up your pizza and bring it to your house.


Nope, you make one call, not three. The person you place your order with makes those "recursive" calls to the kitchen and delivery staff to make sure your order is complete.

The kitchen staff may make additional "recursive" calls ("Where are the mushrooms?") as may the delivery man ("Where is that street located?").

You don't care about these details, you just want your pizza.

WHAT KINDS OF RECURSIVE CALLS DOES ORACLE MAKE?

As you can tell from our pizza example, Oracle will make a recursive call whenever it has to.

Some examples of when it "has to" include:

you want to insert data into a DMT, but all extents are full and news ones need to be allocated to hold your data

you're doing something on a table that causes a trigger to fire (e.g. - don't allow inserts if it's Saturday)

performing DDL

data dictionary cache needs info about objects during parsing .

you have PL/SQL units that include SQL statements (like a stored procedure that inserts into a table, or a function that selects from a table)


TUNING RECURSIVE CALLS???

I hope you can see from this brief discussion that the concept of tuning recursive calls is not as easy as configuring the Recursive Program Interface (RPI).

Tuning depends on several factors, the two biggest being what are you trying to do and what version are you on?

Sometimes recursive calls indicate a problem, sometimes they don't. Can you see why?

I hear and am asked about the notion that recursive calls should always be less than "real" calls. I ask "Why?"

If I have a db app that only accesses the db via stored procedures and each of my procedures issues an average of 10 DML statements within it, then why would a 10/1 ratio be bad?

But that's not to say that analysing recursive calls is not an important tuning step. You need to know what your database is doing and why. Recursive calls could be a hidden problem.


Thank you
osama mustafa

Sunday, July 8, 2012

ORA-00604: error occurred at recursive SQL level 1

Example :


SQL> alter user u1 identified by u1
  2  / 
alter user u1 identified by u1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 6
 
Solution :

SQL> alter system set "_system_trig_enabled"=FALSE;
 
System altered.
 
 
Enjoy 
 
Osama Mustafa  

Oracle Secuirty Tips / SQLNET.ORA Part 2

Hi All ,

I post before about sqlnet.ora with parameter called invited_list , Exclude_list , assume  that i want to prevent sysdba to access database without password Simple Way .


SQLNET.AUTHENTICATION_SERVICES=NONE 


Setting "SQLNET.AUTHENTICATION_SERVICES" parameter to "NONE" in sqlnet.ora file will make it not possible to connect to the database without a password as sysdba. (sqlplus / as sysdba)

This parameter may also have the values : NTS for Windows NT native authentication, ALL for all authentication methods.

Authentication Methods Available with Oracle Advanced Security:
  • kerberos5 for Kerberos authentication
  • cybersafe for Cybersafe authentication
  • radius for RADIUS authentication
  • dcegssapi for DCE GSSAPI authentication
If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.


Enjoy

Thank you
Osama Mustafa

Script to Restart the OEM Agent When It Has Failed

Gaurav Batta has published this nice script to restart the OEM agent when it has failed:

$ cat agent_check.sh
#!/usr/bin/ksh
#
agent_status=`ps -ef|awk '{print $8}'|grep agent10g/bin/emagent`

if [[ -z $agent_status ]]
then
$AGENT_HOME/bin/emctl start agent
echo Agent started on server `hostname` at `date`
else
echo Agent is running fine on server `hostname` at `date`
fi


It is running every 30min.
crontab –l

00,30 * * * * /home/oemagent/agent_check.sh >> /home/oemagent/agent_status.log





Enjoy 


Thank you
Osama Mustafa

Friday, July 6, 2012

Audit failed logon attempts

it is difficult to audit failed sign-on attempts because the user never gets connected to Oracle but you can try this solution below :


Step 1 :
sqlplus / as sysdba
Connected.
SQL> alter system set audit_trail=DB scope=spfile ;
OR
audit_trail=true

Step 2 : 
 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Step 3 : 

sqlplus / as sysdba
 SQL> audit session whenever not successful ;

Step 4 :

Test auditing if it works 

sqlplus scott/osama ;

ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Step 5:

sqlplus / as sysdba

Use the below Script :

select    os_username,   username,   terminal,   to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') "TIME" from   dba_audit_trail;
 OR

select os_username,username,userhost,to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,returncode from dba_audit_session where action_name = 'LOGON' and returncode > 0
order by timestamp ;

OS_USERNAME     USERNAME        TERMINAL        TIME
--------------- --------------- --------------- -------------------
oracle                               SCOTT              pst/1        07-06-201216:21:13

Enjoy 

Osama ...

Tracking Alert Log file by x$dbgalertext

First i would thank CKPT for this amazing article that post in his blog .

Instead of reviewing whole alert log file for any Particular information, We can search using that key word from X$DBGALERTEXT Table.

Grep The word “Starting Up” of Alert log file from Instance.


col ORIGINATING_TIMESTAMP for a40
col MESSAGE_TEXT for a80
set linesize 500
SELECT
originating_timestamp,
message_text
FROM
sys.x$dbgalertext
WHERE
message_text LIKE '%Starting up%';



Grep The word “Instance shutdown complete” of Alert log file from Instance.


SELECT
originating_timestamp,
message_text
FROM
sys.x$dbgalertext
WHERE
message_text LIKE '%Instance shutdown complete%';



again thank you CKPT for this article since its useful for all DBA's , you can check CKPT Blog From the above link


Thank you
Osama mustafa
 

Thursday, July 5, 2012

WFMLRSVCApp.ear not found

I installed Oracle 11g and the installation didn’t find WFMLRSVCApp.ear file. I extracted two files (win64_11gR2_database_1of2.zip and win64_11gR2_database_2of2.zip) in two different folder disk1 and disk2. 


I solved the file not found issue by coping all the folders under disk2databasestageComponents to disk1databasestageComponents.


After restarting the installation it went like a charm and without any issues.

Or Maybe the error will be appear like this :


The same : ou should extract each of them and place win32_11gR2_database_2of2 content into win32_11gR2_database_1of2 folder before running installer.

Enjoy
 

Thank You 
Osama Mustafa

Wednesday, July 4, 2012

Flash_Recovery_Area

Mointer Space in Flash_recovery_are

1- sqlplus / as sysdba
2- Run the Below Query 

SELECT 
    NAME, 
    TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
    TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999')
       AS SPACE_AVAILABLE,
    ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
       AS PERCENT_FULL
    FROM V$RECOVERY_FILE_DEST;
 There's many scripts you can use to monitor FRA Or Arvhive I will post Few Of them :

Select file_type, percent_space_used from v$flash_recovery_area_usage;


SELECT NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;
 
 

 Query the V$RECOVERY_FILE_DEST view to find out the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the Flash Recovery Area. For example :

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME                        SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
D:\Oracle\flash_recovery_area 838860800  292490752                 0              44



Query the V$FLASH_RECOVERY_AREA_USAGE view to find out the percentage of the total disk quota used by different types of files. Also, you can determine how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape. For example :

SELECT * FROM   V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     2                         0              22
ARCHIVELOG                 4.05                      2.01              31
BACKUPPIECE                3.94                      3.86               8
IMAGECOPY                 15.64                     10.43              66
FLASHBACKLOG                .08                         0               1
 




You Can check 
Flash Recovery area - Space management Warning & Alerts [ID 305812.1]


 thank you
Osama Mustafa

Tuesday, July 3, 2012

Oracle General Ledge/Posting Single Ledger program takes too long

After suffering about 6 hours of tuning EBS R12 on Customer Side , Since Posting not not working , but status on Concurrent Is Running and hang in some where .


Cause

This is Bug 9707317.

Posting Performance issue caused by Rule Hint being hard coded in the glsasi.lpc file.

The program uses the "/*+ rule */" hint, but it should be using "/*+ CHOOSE */" or none, which is shown in the tkprof'd trace file explain plan.

Also the wrong index is being used, it is using GL_ACCOUNT_HIERARCHIES_01 when it should be using the GL.GL_ACCOUNT_HIERARCHIES_U2 index.



Solution

To implement the solution, please execute the following steps:

1. Download and review the readme and pre-requisites for the relevant patch.

For 12.0.x patch 9707317:R12.GL.A
For 12.1.x patch 9707317:R12.GL.B

Note: If a password is required to download the patch, please open a service request to get the proper password.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:

For 12.0 Patch 9707317:R12.GL.A

src/post glpmai.opc 120.21.12000000.2
src/summary glsasi.lpc 120.3.12000000.2

For 12.1 Patch 9707317:R12.GL.B

src/post glpmai.opc 120.24.12010000.1
src/summary glsasi.lpc 120.3.12010000.1
You can use the commands like the following:
strings -a $XX_TOP/filename |grep '$Header'

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

Enjoy

thank you
Osama mustafa

Monday, July 2, 2012

Unable to login to Database Because Archivelog

Regarding to heavy duty On Database , Database start Generates Archive log with time size for flash recovery become full , so what i have to do :

first you will not be able login for database or rman


Solution :

Step1 :

-Go to archivelog detestation and delete archivelog (old first) , if you have space on your server you can move them from direcotry to another .

-Flash Recovery On ASM , Follow the below steps
export ORACLE_SID=+ASM1
asmcmd 
now you are inside ASM head to Archivelog destation and delete archive log manually (old first )

 Step 2 :

Now you will be able to login to rman :

Run
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT '/u01/'; 
backup archivelog all delete input ;
release c1 ; } 

 Now you clean Archivelog .


THis solution for Both Cluster and Single Node Database

Enjoy

Thank you
Osama mustafa

Sunday, July 1, 2012

Active Sessions in Oracle Database

Just Save This Sql As Script And Run it :




  1. SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
  2. FROM V$Session
  3. WHERE
  4. Status=‘ACTIVE’ AND
  5. UserName IS NOT NULL;





Enjoy


Thank you
Osama mustafa


Moving SPfile From ASM to File System

its Easy Steps

1-

SQL>Show parameter Spfile ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/orcl/spfileorcl.ora
2-

Create pfile='/u01/new.pfile' from spfile ;

3-

Create spfile='/u01/app/oracle/product/10.2.0/db_1/dbs/newspfile.ora' from
pfile='/u01/new.pfile';


4-

Shutdown immediate ;
startup ;

5-
Check new Location For Spfile ;


Thank you
Osama Mustafa

Moving Spfile From File-System To ASM

1. Create spfile in ASM "+DATA" disk group :
 
SQL> connect / as sysdba

SQL> show parameter spfile
 
NAME TYPE VALUE
------- ------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora
 
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora';

SQL> create pfile from spfile
File created.
 
SQL> create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora';
File created.

SQL> exit
 
2. Modify initracdb11.ora on rac1 and initracdb12.ora on rac2 files to point to location in ASM
 
[oracle@rac1 dbs]$ echo "SPFILE='+RACDB_DATA/racdb1/spfileracdb1.ora'" > /u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora
[oracle@rac1 dbs]$ ssh rac2 "echo \"SPFILE='+RACDB_DATA/racdb1/spfileracdb1.ora'\" > /u01/app/oracle/product/11.2.0/db_1/dbs/initracdb12.ora"
 
3. Update OCR with new SPFILE location
 
[oracle@rac1 dbs]$ srvctl modify database -d racdb1 -p +RACDB_DATA/racdb1/spfileracdb1.ora 

4. Rename any existing spfiles in $ORACLE_HOME/dbs
5. Restart all instances to switch to new SPFILE 
6. Check New Location For Spfile .
 
 
Thank you 
Osama mustafa