Tag: osama mustafa oracle

ora-00845 memory_target not supported on this system

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

Posted on by Osama Mustafa in Database
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 to Oracle documentation  :

LNS (log-write network-server) and ARCH (archiver) processes running on the primary database select archived redo logs and send them to the standby database, where the RFS (remote file server) background process within the Oracle instance performs the task of receiving archived redo-logs originating from the primary database.

Alternatively, a supplementary mechanism may transfer the archived redo logs. On the standby database a Fetch Archive Log (FAL) client monitors for gaps in the sequence of received logs. If it finds a gap, it may invoke one or more Fetch Archive Log (FAL) servers to run on the primary database to forward the missing item(s).

Once the archived redo logs have arrived, other processes (such as an ARCH (Archiver process), an MRP (Managed Recovery Process), and/or an LSP (Logical Standby Process)) may set about applying the log contents to the standby database.

 Conclusion :

The difference between physical and logical standby is in the way the changes from the primary are applied. Both created as an exact image of the primary database. Both receive redo logs from the primary database.

 Refernce :
 Oracle DataGuard architecture 

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

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

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

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

Database altered.

Check FRA Size

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

AWR vs ADDM vs ASH

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

Generate ADDM  :

  • Login to SQL
  • @$ORACLE_HOME/rdbms/admin/addmrpt.sql
  •  enter system password when you asked for .
  • Specify a begin_snap from the list and press Enter.
  • Specify the end_snap from the list and press Enter.
  •  Report Name

ASH : Active Session History 

statistics from the in-memory performance monitoring tables also used to track session activity and simplify performance tuning.

ASH reports Give the following information :

  • Top User Events (frequent wait events)
  • Details to the wait events
  • Top Queries
  • Top Sessions
  • Top Blocking Sessions
  • Top DB Object.
  • Activity Over Time

 Generate ASH reports :

The Best way to do that using OEM.  (Enterprise manager).

Thank you
Osama Mustafa

Encrypt Your PL/SQL Code Using Wrap Command

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

 OR

If i want to get my procedure script wrapped and no one can read my file :

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

PL/SQL Wrapper: Release 10.2.0.5.0- Production on Mon Dec 03 22:46:12 2012

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

Processing Testproc.sql to wrapped.sql

Open Wrapped.sql 

[oracle@localhost ~]$ more wrapped.sql
CREATE OR REPLACE PROCEDURE testproc wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
49 85
SOwY8JEJe1MROnuBTksxRTO9iJ4wg5nnm7+fMr2ywFwWoWLRPhaXroubdIvAwDL+0oYJjwlp
uFKbskr+KLK957KzHQYwLK4k6rKBpVHb4USaw+kyLvYOxeokH/Y5pkT0tnU=

/
 

Step #3:

Now I want to create procedure in my database :

[oracle@localhost ~]$ sqlplus osama/osama

SQL*Plus: Release 10.2.0.5.0 – Production on Mon Dec 3 22:48:03 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @Testproc.plb

Procedure created.

SQL>
SQL>
SQL> exec Testproc

PL/SQL procedure successfully completed.

 

Spool File With Date/Time Name

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