Posts

Showing posts from December, 2012

Oracle Physical Standby Modes

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…

ORA-01210: data file header is media corrupt

ORA-00283: recovery session canceled due to errors
ORA-01110: data file 24: '/ora/data/gamesds/system02.DBF'
ORA-01122: database file 24 failed verification check
ORA-01110: data file 24: '/ora/data/gamesds/system02.DBF'
ORA-01210: data file header is media corrupt


Error Cause: The file header block is internally inconsistent. The beginning of the block has a header with a checksum and other data for insuring the consistancy of the block. It is possible that the last disk write did not operate correctly. The most likely problem is that this is not a datafile for any database.  Solution :
Have operating system make correct file available to database. If the trace file dump indicates that only the checksum is wrong, restore from a backup and do media recovery.

 Or The Below Solution , You Must Be Sure that you have Valid Backup to restore it .

SQL> shutdown immediate SQL > Startup mount SQL> ALTER DATABASE DATAFILE '/ora/data/gamesds/system02.DBF' O…

Oracle Penetration Testing

Image
More Information Click Here

DBMS_METADATA Is Missing During expdp

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

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

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

ora-00845 memory_target not supported on this system

The Error Will be Appear like the following :
SQL> startup nomount; ORA-00845: MEMORY_TARGET not supported on this system Cause :

This error comes up because you tried to use the Automatic Memory Management (AMM) feature of Oracle 11g R2. Well done, but it seems that your shared memory filesystem (shmfs) is not big enough. So let’s look at the steps necessary to enlarge your shared memory filesystem to avoid the error above.

Solution

mount -t tmpfs shmfs -o size= /dev/shm
Thank you
Osama Mustafa

Logical Standby Vs Physical Standby

What is the difference between these two Data Guard configuration , When To Use them :

Physical Standby:

1- Physical Standby Database Its exactly same As Primary Database.
2-  In Physical Data Guard The archivelog Applied directly after transfer from primary database (FTP)

Properties of Physical Standby

1- Maintain Is Easy.
2- Creation is Easy .
3- Copy Of your Primary Database ( Disaster Recovery Solution ).

Usage :

High availability solutions Or disaster recovery Solution.

Logical Standby

1 - Opposite Of standby Database , Which is not Match primary Database .
2 - This Kind Of Configuration can be Opened in Read Only Mode .
3 - can have additional materialized views and indexes added for faster performance
4 - LogMiner Techniques to transfer Archivelog.

Properties Of Logical Database :

1 - Open In Read only Mode .
2 - Sometimes its used as RollBack Solution In Upgrade ,

Usage:

1 - reporting Database to avoid overhead in primary database.
2 - Query Database .


How they Works :

Regarding…

Some Command To Deal With Standby 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

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

Bounce Standby Database

Shutdown Standby Database :


sqlplus /as sysdba alter database recover managed standby database cancel; shutdown;
Startup Standby Database :


sqlplus /as sysdba startup nomount alter database mount standby database alter database recover managed standby database disconnect from session;

Thank you
Osama Mustafa

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed



Cause:
Missing Space Issue

Solution 
log_archive_dest_1 = 'LOCATION=/u01/app/oracle/prim/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=prim'

Should be 

log_archive_dest_1 = 'LOCATION=/u01/app/oracle/prim/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'




Thank you
Osama Mustafa

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

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


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

Total System Global Area  285212672 bytes
Fixed Size                  2095704 bytes
Variable Size             155190696 bytes
Database Buffers          121634816 bytes
Redo Buffers                6291456 bytes
Database mounted.
SQL> alter database archivelog ;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


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


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

Total System Global Area  285212672 bytes
Fixed Size                  2095704 bytes
Variable Size             155190696 bytes
Database Buffers          121634816 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.
SQL>

SQL> SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut dow…

Check FRA Size

The Below query To check FRA Used Space and Free Space its very Useful Query :

set lines 100 col name format a60 select name , floor(space_limit / 1024 / 1024) "Size MB" , ceil(space_used / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name /

Thank you
Osama mustafa

Check Database Features

Small topics but Useful to know which Feature you have on your database Using View :

9i ---> V$OPTION
10g and above --->  DBA_FEATURE_USAGE_STATISTICS


 Thank you
Osama Mustafa

AWR vs ADDM vs ASH

AWR : automatic workload repository

The AWR is used to collect performance statistics including:
Wait events used to identify performance problems.Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.Object usage statistics.Resource intensive SQL statements. I will not get into Details how to generate AWR since i mention it before on my Blog .


 ADDM : automatic database diagnostic monitor


analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks.and we use it for the following cases :

     CPU bottlenecks     Undersized memory structures     I/O capacity issues     High load SQL statements     RAC specific issues     Database configuration issues     Also provides recommendations on hardware changes, database con…

Encrypt Your PL/SQL Code Using Wrap Command

Today I will make demonstration how to hide your code , Function , Procedure and anything Related to PL/SQL Code , to do this you have to know wrap utility in oracle .

Step #1:

You Have to Write Sample Code to Make test on it .

Sample Code :

SQL> CREATE OR REPLACE PROCEDURE testproc
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Wrap Me!');
 END;
/
Procedure created.
Ensure Procedure Run Successfully :

SQL> exec testproc

PL/SQL procedure successfully completed.
The above Steps Just to make sure our Procedure will run suceesfully without any error , now i will delete it again and start using wrap to encrypt.

SQL> conn osama/osama;
Connected.
SQL>
SQL> drop procedure testproc ;

Procedure dropped.Step #2:

Using Wrap to Create plb File .

-Save Above Procedure in File Called TestProc.sql under /home/oracle .

[oracle@localhost ~]$ wrap iname=Testproc.sql

PL/SQL Wrapper: Release 10.2.0.5.0- Production on Mon Dec 03 22:42:14 2012

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing …

ORA-01438 Which Column

ORA-01438: value larger than specified precision allowed for this column
You will receive this error while trying to insert big Value In Column with Specific Range , But How Could You know Which Column is it i will some hint that could help you to do that :

Hint #1 :

1-ORA-01438  : For Numeric Value
2-ORA-12899  : For Varchar2 Value

Hint #2 :

Enable Audit On know which One of these Column caused the error

SQL > Create table test as select * from scott.dept SQL> desc dept Name Null? Type ----------------------------------- -------- ------------------------ DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)   SQL> audit insert on Osama.test whenever not successful;   Audit succeeded.   SQL> insert into osama.test values(2000,'Osama','Osama'); insert into scott.dept values(2000,'osama','Osama&…

Spool File With Date/Time Name

Amazing way to spool  automatically :

SET TERMOUT OFF COLUMN today_ddmmyyyy_col NEW_VALUE today_ddmmyyyy SELECT TO_CHAR ( SYSDATE, 'DDMMYYYY') AS today_ddmmyyyy_col FROM dual; SET TERMOUT ON SPOOL log_&today_ddmmyyyy..log
Thank you
Osama Mustafa