Posts

Showing posts from July, 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       /u…

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 a…

ORA-12557: TNS:protocol adapter not loadable

Image
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

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;beginopen 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

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”…

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 …

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

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 l…

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…

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

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>ORSQL> show parameter control_filesNAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ control_files string C:\ORACLE\ORADATA\DB10G\CONTRO L01.CTL, C:\ORACLE\ORADATA\ORCL \CONTROL02.CTL, C:\ORACLE\OR ADATA\ORCL\CON…

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 :

GATHER_INDEX_STATS, Index statisticsGATHER_TABLE_STATS, Table, column, and index statisticsGATHER_SCHEMA_STATS,Statistics for all objects in a schemaGATHER_DICTIONARY_STATS,Statistics for all dictionary objectsGATHER_DATABASE_STATS,Statistics for all objects in a database Example :

begin dbms_stats.gather_schema_stats (ownname => 'Scott', estimate_percent => dbms_stats.auto_samp…

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…

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 13Logout of sqlplus and run: loadjava -u / -r -v -f -s -grant public -genmissing dbwsclientws.jar dbwsclientdb102.jarEnjoy osama mustafa

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 su…

Agent Configuration Assistant Failed/Grid Control

Image
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:

cd $AGENT_HOME/bin ./emctl stop agent ./emctl unsecure agent ./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.
./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:
A function to be used by the policy (function policy) created in next step. Use dbms_rls package to create the policy. 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 …

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.logAnd 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.logOpen 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$…

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 …

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?").

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, ALLfor all authentication methods.

Authentication Methods Available with Oracle Advanced Security:
kerberos5 for Kerberos authenticationcybersafe for Cybersafe authenticationradius for RADIUS authenticationdcegssapi 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

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…

Tracking Alert Log file by x$dbgalertext

Image
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

WFMLRSVCApp.ear not found

Image
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

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 fil…

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.

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

Active Sessions in Oracle Database

Just Save This Sql As Script And Run it :




SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status=‘ACTIVE’AND UserName ISNOTNULL;




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 spfileNAME TYPE VALUE--------------------------------------------spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.oraSQL> 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 spfileFile created.SQL> create spfile='+DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora';File created.SQL>exit2. 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…