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

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

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

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.

 

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')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this
column
 
 
SQL> select sql_text,returncode from dba_audit_trail
2 where owner='OSAMA' and obj_name='TEST';
 
SQL_TEXT
----------------------------------------------------------------------
RETURNCODE
----------
insert into scott.dept values(2000,'Osama','Osama')
1438

 Hint #3 :

Enable Tracing Level 1438

SQL > conn osama/osama ;
SQL> create table test as select * from scott.dept ;
 
SQL> select * from test ;
 
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
 
 
SQL> alter system set events='1438 trace name Errorstack forever,level 10';
 
 
SQL> insert into test values (100000000000000000,'osama','JOR');
insert into test values (100000000000000000,'osama','JOR')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
 
In Trace File :
 
ORA-01438: value larger than specified precision allowed for this column
Current SQL statement for this session:
insert into test values (100000000000000000,'osama','JOR')

Thank you
Osama Mustafa