Month: July 2012

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.

Shell Script For Cold Backup

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

<eofsql 0="" 120="" <eofsql eofsql

Enjoy
osama mustafa

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

Rename/Move Oracle Files / Control FIle Part 1

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

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 


 


 






 

	

	

	




	
	

		
Script to Generate Grant in Database
				

					Posted on  by Osama Mustafa in Scripts				


				

	

		
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
				

					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 

	

	

	




	
	

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

					Posted on  by Osama Mustafa in Database				


				

	

		


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  



	

	

	




	
	

		
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