Tag: osama oracle

Table Locks

Posted on by Osama Mustafa in Scripts
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

knowing the transaction status

Posted on by Osama Mustafa in Scripts
**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

Posted on by Osama Mustafa in Database
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.

Manage Oracle Files / Rename Or Move Logfiles Part-2

Posted on by Osama Mustafa in Database
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

ORA-1122 :database file %s failed verification check

Posted on by Osama Mustafa in Database
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

Gather Schema Statistics How to Use it !!!!

Posted on by Osama Mustafa in Database
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 


 


 






 

	

	

	




	
	

		
prct-1011 failed to run getcrshome
				

					Posted on  by Osama Mustafa in Database				


				

	

		
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 

	

	

	




	
	

		
Opening the database with corrupted redo log
				

					Posted on  by Osama Mustafa in Database				


				

	

		
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 

	

	

	




	
	

		
Data Masking In Oracle/Column Masking
				

					Posted on  by Osama Mustafa in Database				


				

	

		
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. 

  2.  Use dbms_rls package to create the policy.
    3. 

  3.  Assign “exempt access policy” to users to be excluded from the policy. These users can see all data with no masking.
    4. 



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 

	

	

	




	
	

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

					Posted on  by Osama Mustafa in Database				


				

	

		





 


-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