Tag: osama oracle

Useful Linux Commands For DBA

Posted on by Osama Mustafa in Operating system 
Show Routing Table :
 
netstat -r

Check ORA errors in the Logs:

grep ^ORA- *log |cut -f2 -d"-"|cut -f1 -d:|awk '{print "ORA-" $1}'|sort -u
Inzip CPIO Files :
 
cpio -idmv <

 Sort Files By Size :

ls -l |sort -k 5

Find Command archive and move to another Folder :

find ./ -name "*.arch" -mtime +1 -exec mv {} /u01/;

Find Command archive and Remove it:

find ./ -name "*.ARC" -mtime +1 -exec rm {} \;

Find Command with Zip :

find ./ -name "*.ARC" -mtime +1 -exec gzip {} \;

 Find Command With List :





 find ./ -name “*.ARC” -mtime +1 -ls  


Thank you
Osama Mustafa



	

	

	




	
	

		
Check Default Tablespace For User and Change it
				

					Posted on  by Osama Mustafa in Database				


				

	

		
Check the Below Demo how you can check tablespace for specific User and how can  you change it


Create New User Without Determine which Tablespace: 


SQL > Create User test Identified by test ;


Check TableSpace for Test User:


 SQL> select default_tablespace from dba_users where username=’TEST’;


DEFAULT_TABLESPACE
——————————
USERS


Change TableSpace For Test User :


Solution One : Change Default TableSpace For Database By :


 SQL> alter database default tablespace ;

Solution two : define Tablespace When Creating User


 SQL > create user Test identified by Test default tablespace USER


Thank you
Osama Mustafa

	

	

	




	
	

		
ORA-01151: use media recovery to recover block, restore backup if needed
				

					Posted on  by Osama Mustafa in Database				


				

	

		


SQL> startup
ORACLE instance started.


Total System Global Area  599785472 bytes
Fixed Size                  1288820 bytes
Variable Size             192939404 bytes
Database Buffers          402653184 bytes
Redo Buffers                2904064 bytes
Database mounted.
ORA-01172: recovery of thread 1 stuck at block 4545 of file 1
ORA-01151: use media recovery to recover block, restore backup if needed
SQL>


Cause :
   There is block corruption in tablespace.


Solution :


 SQL> shutdown immediate;
 SQL> startup mount;
 SQL> recover database;
 SQL> alter database open;


 

	

	

	




	
	

		
ORA-20222: Workload was not captured in the current database
				

					Posted on  by Osama Mustafa in Database				


				

	

		

 
Happy New Year For All , And Wish you All Best  




ORA-20222 “Given capture_id \”%s\” is invalid or the corresponding capture failed”






ORA-20222: Workload was not captured in the current database. Cannot export!
ORA-06512: at “SYS.DBMS_WORKLOAD_CAPTURE”, line 1588
ORA-06512: at line 2 


 Regarding to Https://support.oracle.com Note :

 


DATABASE CAPTURE AND REPLAY COMMON ERRORS AND REASONS [ID 463263.1]




This Error 




is a generic error message that can occur due other underlying error during Capture. 
Check the error accompanying with ORA-20222.


Ensure correct directory name is used including check the case-sensitivity of the directory name.
The directory name is case sensitive. Ensure READ and WRITE privilege on the DIRECTORY object to the which is the capture is written. 






Thank you 


Osama mustafa
















Happy New Year Again .  













	

	

	




	
	

		
Oracle Physical Standby Modes
				

					Posted on  by Osama Mustafa in Database				


				

	

		



Regarding to Oracle Documents That Describe Standby Modes, There’s Are Three Types you can follow the documentation.








Check Standby Modes :




SQL> select PROTECTION_MODE from v$database ;





PROTECTION_MODE


——————–


MAXIMUM PERFORMANCE




Primary Database: prim


Standby Database: stdby 






Note: the below Commands Should Apply on Primary Database on Mount Mode Then Open database.






Convert Between Modes (Switch Modes)


 




MAXIMIZE AVAILABILITY 






SQL> alter system set log_archive_dest_2=’SERVICE=stdby AFFIRM SYNC VALID_FOR=(online_logfiles,primary_role) db_unique_name=stdby’;
System altered.



 SQL> alter database set standby Database to MAXIMIZE AVAILABILITY ;
Database altered.




MAXIMIZE PERFORMANCE






 SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby’;


 System Altered  




SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;


Database Altered




MAXIMIZE Proctection






SQL> alter system set log_archive_dest_2=’SERVICE=stdby AFFIRM SYNC VALID_FOR=(online_logfiles,primary_role) db_unique_name=stdby’;


System altered.




shutdown immediate ; 


Startup mount ;


Alter database set standby database to maximize Protection ;


Alter database open ; 




Some Useful Sites:
1-Convert Modes Using dgmgrl






Thank you




Osama Mustafa













	

	

	




	
	

		
DBMS_METADATA Is Missing During expdp
				

					Posted on  by Osama Mustafa in Database				


				

	

		
The Error Appear During The expdp Database , And Looks


ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CONFIGURE_METADATA_UNLOAD []
ORA-04067: not executed, package body “SYS.DBMS_METADATA” does not exist
ORA-06508: PL/SQL: could not find program unit being called: “SYS.DBMS_METADATA”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 62
ORA-06512: at “SYS.KUPW$WORKER”, line 6226


Cause:


 SYS.DBMS_METADATA Is Missing .


Solution :


Check if the above object exists


SELECT owner, object_name, object_type, status FROM   dba_objects WHERE   object_name=’DBMS_METADATA’;


Not Found :


$ORACLE_HOME/rdbms/admin/prvtmeta.plb
$ORACLE_HOME/rdbms/admin/utlrp.sql 



	

	

	




	
	

		
Startup/Shutdown Logical Standby Database
				

					Posted on  by Osama Mustafa in Database				


				

	

		
Simple Steps to do that



Shutdown Steps :


On Primary do the following :


SQL > alter system switch logfile ;
SQL > alter system archive log current ; 


Go to Standby :


SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY; 
SQL> shutdown immediate; 


Primary Database :


SQL> shutdown immediate; 


Startup Steps : 

Primary Database


SQL>startup;


 Standby Database:


SQL > startup
SQL > alter database start logical standby apply immediate ;


Thank you 
Osama Mustafa

	

	

	




	
	

		
Bulid Logical Database
				

					Posted on  by Osama Mustafa in Database				


				

	

		
I Post Earlier What is the Different Between Logical Database and Standby Database , Both Are Type For Data Guard .


Today i will take about how to configure Logical Database , to do this you must first create Physical Standby Which i mention it before , And you can Follow the link .


These steps for 10g,  Now After create physical Standby Database , And Make sure its working Without any problem , You Have to follow the below steps to Create Oracle Logical Standby.



Steps to convert Standby Database to Logical Standby Database : 


Step #1 : 


in this step logminer Dictionary Must be Built into redo data. ( Build LogMiner Tables for Dictionary) 


On Primary 


SQL> begin 
  2  dbms_logstdby.build;
  3  end ;
  4  /


OR


 SQL> execute dbms_logstdby.build;


Step #2:


Now we have stop Managed recovery between primary and standby :


On Standby :


SQL> alter database recover managed standby database cancel ;


Database altered.


Step #3 : 


Convert Physical Standby to Logical Standby .


On Standby :




SQL> alter database recover to logical standby stdby ;


Database altered.


Notices : If this command hang and take to much Time then Back to step #1 and did it again . 

Step #4:


On Standby ( New Logical)  we open it in Resetlog mode .


On New Logical ( Standby) :


SQL> shutdown immediate ;
ORA-01507: database not mounted


ORACLE instance shut down.


SQL> startup mount ;
ORACLE instance started.


Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             532678648 bytes
Database Buffers          301989888 bytes
Redo Buffers                2396160 bytes
Database mounted.


SQL> 
SQL> 
SQL> alter database open resetlogs ;


Database altered.


Step #5: 


Start Apply Service On logical




SQL> alter database start logical standby apply ;


Database altered.


OR




SQL> alter database start logical standby apply immediate ;
Database altered.


Just As Check if everything Goes Fine :


SQL> select database_role , Name from v$database ;


DATABASE_ROLE    NAME
—————- ———
LOGICAL STANDBY  STDBY


Thank you
Osama Mustafa 

	

	

	




	
	

		
Some Command To Deal With Standby Database
				

					Posted on  by Osama Mustafa in Database				


				

	

		
Open Standby In Read Only :


    SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
    SQL> ALTER DATABASE OPEN READ ONLY;


Back to Mount Point ( Same as Before Open in Read Only ) :


    SQL > shutdown;
    SQL > startup nomount
    SQL > alter database mount standby database
    SQL > alter database recover managed standby database disconnect from session; 


Switch Over Primary As Standby , And Standby As Primary


Primary Database : Prim 
Standby Database : Stdby 


On Prim :


SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
SQL> SHUTDOWN NORMAL;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;




On standby:


SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> SHUTDOWN;
SQL> STARTUP;



On Prim :


    SQL> ALTER DATABASE RECOVDR MANAGED STANDBY DATABASE;


On standby:


SQL> ALTER SYSTEM ARCHIVE LOG START;
SQL> ALTER SYSTEM SWITCH LOGFILE




Thank you 
Osama Mustafa 

	

	

	




	
	

		
Step By Step to Create Phyiscal Standby Dastabase 10g
				

					Posted on  by Osama Mustafa in Articles, Database				


				

	

		
I Post Earlier Some Topics About Dealing with Data Guard , Today i will start Mention Steps How To Create Data Guard Standby Database Manually . Its Long East Steps .Just Follow Them :



primary Information :
Instance Name : prim
Hostname : PrimDB10g

Standby Information :
Instance Name : Stdby
Hostname : StdbyDB10g

Some Note :
1-On Standby Hostname Just install Database Software without Create Any Database.
2-Primary Database and Standby Database should be able to ping .
3-Primary and Standby Should be The Same .


Step #1 : On primary Database 


-Primary Database Should be in Archive log mode.


SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Current log sequence           1


SQL > Shutdown immediate ;
SQL > Startup mount ;
SQL > Alter database archivelog ;
SQL > Alter database open ;


– Enable Force Logging to make sure  takes precedence and all operations are logged
  into the redo logs.


  SQL> ALTER DATABASE FORCE LOGGING; 


-Create Password Since password file should be the same on Both , you can create it on Primary Database and Copy to Standby with Change name .


[oracle@PrimDB10g]$ orapwd file=@ORACLE_HOME/db/orapwprim password=oracle force=y 


Step #2 :Adding Standby Logfile .


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4  ‘/u01/app/oracle/oradata/prim/stby04.log’ size 50m;


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5   ‘/u01/app/oracle/oradata/prim/stby04.log’ size 50m; 


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5   ‘/u01/app/oracle/oradata/white/stby04.log’ size 50m;


 Step #3:


On Primary Database Should Create Pfile , to modify it and add parameters .


SQL> CREATE PFILE FROM SPFILE; 
OR
SQL> CREATE PFILE=’/initSID’ from spfile;


prim.__db_cache_size=121634816
prim.__java_pool_size=41943040
prim.__large_pool_size=4194304
prim.__shared_pool_size=109051904
prim.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/prim/adump’
*.background_dump_dest=’/u01/app/oracle/admin/prim/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oracle/oradata/prim/control01.ctl’,’/u01/app/oracle/oradata/prim/control02.ctl’,’/u01/app/oracle/oradata/prim/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/prim/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’prim’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=283115520
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/prim/udump’
db_unique_name=’prim’
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prim,stdby)’
LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/oradata/prim/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim’
LOG_ARCHIVE_DEST_2=’SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=stdby’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=stdby
FAL_CLIENT=prim
standby_file_management=auto
db_file_name_convert=’/u01/app/oracle/oradata/stdby/’,’/u01/app/oracle/oradata/prim/’
log_file_name_convert=’/u01/app/oracle/oradata/stdby/’,’/u01/app/oracle/oradata/prim/’


Once you Create and modify pfile you should startup database with it .


SQL > Shutdown immediate  
SQL > Startup nomount pfile=’/u01/initPrim.ora’;
SQL > Alter database mount ;
SQL > alter database open ;
SQL > Create Spfile from pfile = ‘/u01/initPrim.ora’;


Bounce Database again .



Step#4 : Backup Primary Database Using RMAN 



RMAN> backup full database format ‘/u01/rman/%d_%U.bkp’ plus archivelog format ‘/u01/rman/%d_%U.bkp’;


Next, create a standby controlfile backup via RMAN:
RMAN> configure channel device type disk format ‘/u01/rman/%U’; 


RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;


Move The backupset to Standby Using SCP


scp -r oracle@Primary-Database-ip :/u01/rman oracle@standby-database-ip:/u01/ 


The primary and standby database location for backup folder must be same.

Steps#5 :  Start Working On Standby Database .


 you should Create standby folders , to make sure No error will be appear .


mkdir -p /u01/app/oracle/oradata/stdby
mkdir -p /u01/app/oracle/oradata/stdby/arch
mkdir -p /u01/app/oracle/admin/stdby
mkdir -p /u01/app/oracle/admin/stdby/adump
mkdir -p /u01/app/oracle/admin/stdby/bdump
mkdir -p /u01/app/oracle/admin/stdby/udump
mkdir -p /u01/app/oracle/flash_recovery_area/PRIM
mkdir -p /u01/app/oracle/flash_recovery_area/PRIM/onlinelog 



Step#6: Parameter file for Standby 


stdby.__db_cache_size=121634816
stdby.__java_pool_size=41943040
stdby.__large_pool_size=4194304
stdby.__shared_pool_size=109051904
stdby.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stdby/adump’
*.background_dump_dest=’/u01/app/oracle/admin/stdby/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u01/app/oracle/oradata/stdby/control01.ctl’,’/u01/app/oracle/oradata/stdby/control02.ctl’,’/u01/app/oracle/oradata/stdby/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/stdby/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’prim’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdbyXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=283115520
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/stdby/udump’
db_unique_name=’stdby’
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prim,stdby)’
LOG_ARCHIVE_DEST_1=’LOCATION=/u01/app/oracle/oradata/stdby/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby’
LOG_ARCHIVE_DEST_2=’SERVICE=prim LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=prim’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=’prim’
FAL_CLIENT=’stdby’
standby_file_management=auto
db_file_name_convert=’/u01/app/oracle/oradata/prim/’,’/u01/app/oracle/oradata/stdby/’
log_file_name_convert=’/u01/app/oracle/oradata/prim/’,’/u01/app/oracle/oradata/stdby/’



Step#7:Configure Listener and tnsnames.ora On Both :

On Primary Database :

tnsnames.ora


STDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stdby)
    )
  )
PRIM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prim)
    )
  )


Same On Standby , On Standby Databsase Use netca to create listener . and make sure its up
test the connection between both nodes via tnsping m and its should be able to Connect .

Step#8: 


on Standby Database


echo “stdby:/u01/app/oracle/product/10.2.0/db_1:N” >> /etc/oratab


export ORACLE_SID=stdby
sqlplus / as sysdba
SQL > startup nomount pfile=’/u01/initSTDBY.ora’;
 SQL > Create Spfile from pfile = ‘/u01/initSTDBY.ora’;


Bounce Database.
SQL > Startup nomount ;
SQL > exit;


export ORACLE_SID=stdby ;
rman target=sys/oracle@prim auxiliary=/ 


Connection should be like the following :
connected to target database: PRIM (DBID=3603586489) –Prim
connected to auxiliary database: PRIM (not mounted) –Standby


RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;


Now We Done Creating Standby Database ,  we will Start Sync :


SQL> alter database recover managed standby database disconnect from session;


 You can Check If everything is up to date with standby , use archive log list on both node to check archive log number .


Thank you
Osama Mustafa