Tuesday, October 15, 2013

Oracle 8i:Disaster recovery solution -Standby Database for Oracle version 8.1.7.0.0

Oracle 8i:Standby Database for Disaster recovery solution.






Here is the simple steps for creating standby database in oracle 8i.

This is testing purpose,So do the all steps from creating primary to real time sync of standby database.

1.Create/Configure Primary Database.

a)Create primary database pfile.

db_name = "primary"
instance_name = primary
service_names = primary
control_files = ("/oracle/primary/stdby1.ctl","/oracle/primary/stdby2.ctl")
control_file_record_keep_time=30
open_cursors = 100
max_enabled_roles = 30
db_block_buffers = 40000
db_block_lru_latches=4
cursor_space_for_time=true
session_cached_cursors=40
shared_pool_size = 71680000
shared_pool_reserved_size=9216000
large_pool_size = 10485760
java_pool_size = 10485760
log_checkpoint_interval = 1048576
processes = 300
log_buffer = 163840
log_archive_start = true
log_archive_dest_1 = "location=/oracle/primary/arc/"
log_archive_format = meta_arch_%t_%s.arc
LOG_ARCHIVE_DEST_2 = 'SERVICE=standby MANDATORY REOPEN=60'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
background_dump_dest = /oracle/primary/bdump
core_dump_dest = /oracle/primary/cdump
user_dump_dest = /oracle/primary/udump
db_block_size = 4096
remote_login_passwordfile = exclusive
os_authent_prefix = ""
java_soft_sessionspace_limit=83000000
java_max_sessionspace_size=1000000000
compatible = "8.1.0"
sort_area_size = 5242880
sort_area_retained_size = 3145728
utl_file_dir=/oracle2
hash_multiblock_io_count = 8

b) Create Password files
$orapwd file=/oracle/dbs/orapwprimary  password=oracle entries=5

c).Create Database .
sql>startup nomount pfile=PATH
sql>create database primary
    controlfile reuse
    logfile '/oracle/primary/redo01.log' size 10M reuse,
            '/oracle/primary/redo02.log' size 10M reuse,
            '/oracle/primary/redo03.log' size 10M reuse
                datafile '/oracle/primary/system01.dbf' size 100M reuse
autoextend on
next 10M maxsize 200M
character set WE8ISO8859P1;

d)Execute script for create dictionary views and tables.
SQL>@?/rdbms/admin/catalog,sql
SQL>@?/rdbms/admin/catproc.sql

2.Put Database in archive log mode.
SQL>shut immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;

3.Create standby control file.
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/stprimary.ctl'

4.Set Log Archive dest for standby
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby MANDATORY REOPEN=60';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

in pfile

LOG_ARCHIVE_DEST_2 = 'SERVICE=standby MANDATORY REOPEN=60'
LOG_ARCHIVE_DEST_STATE_2 = ENABLE

5.Take Hot/Cold backup of Database,pfile,passwordfiles.

6.Create/Configure standby Database

a) Copy the primary database backup,pfile,passowrd files to same location to standby database.

b) Create pfile for standby database from copied pfiles.
 
db_name = "primary"
instance_name = standby1
service_names = primary
control_files = ("/oracle/primary/stdby1.ctl","/oracle/primary/stdby2.ctl")
control_file_record_keep_time=30
open_cursors = 100
max_enabled_roles = 30
db_block_buffers = 40000
db_block_lru_latches=4
cursor_space_for_time=true
session_cached_cursors=40
shared_pool_size = 71680000
shared_pool_reserved_size=9216000
large_pool_size = 10485760
java_pool_size = 10485760
log_checkpoint_interval = 1048576
processes = 300
log_buffer = 163840
log_archive_start = true
log_archive_dest_1 = "location=/oracle/primary/arc/"
log_archive_format = meta_arch_%t_%s.arc
LOG_ARCHIVE_DEST_2 = 'SERVICE=primary MANDATORY REOPEN=60'
standby_archive_dest=/oracle/primary/arc/
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
background_dump_dest = /oracle/primary/bdump
core_dump_dest = /oracle/primary/cdump
user_dump_dest = /oracle/primary/udump
db_block_size = 4096
remote_login_passwordfile = exclusive
os_authent_prefix = ""
java_soft_sessionspace_limit=83000000
java_max_sessionspace_size=1000000000
compatible = "8.1.0"
sort_area_size = 5242880
sort_area_retained_size = 3145728
utl_file_dir=/oracle2
hash_multiblock_io_count = 8


 c) Start standby database in nomount mode.

d) Put Copied standby control file to same location as primary(control_files parameter in pfile)

e) Mount Standby database


SQL>alter database mount standby database;

7.Start Recovery of standby Database.
SQL>recover managed standby database;

Cancel recovery

SQL>recover managed standby database cancel;

8.Manually apply Archive GAP.
a)copy all pending archives for standby.
sql>recover automatic standby database;

Tuesday, October 8, 2013

Oracle 10g:Automate AWR reports to get in mail attachment.

Automate Automatic Workload Repository Report in a mail




Every time go to database and collect reports is old fashioned way.Now, Lets do some automated work for oracle workload reports.

This is required when number of databases and also we all lazy to avoid see reports everyday.So it is better to make one schedule who will send us yesterdays all working day awr report in one single mail where just need to see if anything problematic in off -office hours.

Simple and easy way creating one single procedure and schedule by scheduler.

lets do some coding.

1.Create directory for parameters file for awrpt input parameters.

--Report Type,
--Num days
--Begin snapshot id
--End snapshot id
--Reporrt Name

This is the mandatory report parameter needs to generate awr report.

2.Create stored procedure.

--Change directory name 

--Get host_command from one of blog post.

--set report path

CREATE OR REPLACE PROCEDURE SYSTEM.USP_AUTOMATE_AWRRPT
as

v_dbid                         varchar2(20);
v_INSTANCE_NUMBER   varchar2(20);
v_bid                           varchar2(20);
v_eid                           varchar2(20);
v_parfile                       UTL_FILE.file_type;
v_parfile_name              VARCHAR2(50);
v_commands                VARCHAR2(100);
v_location                      VARCHAR2(50);
v_directory                     VARCHAR2(50);

begin

--snapshot details
select DBID,INSTANCE_NUMBER,MIN(SNAP_ID) bid,MAX(SNAP_ID) eid
      INTO v_dbid,v_INSTANCE_NUMBER,v_bid,v_eid
 from
        DBA_HIST_SNAPSHOT
 where
        trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-1)
 Group by
          DBID,INSTANCE_NUMBER  ;
       
 --Get Directorty location 
Select
        DIRECTORY_NAME,DIRECTORY_PATH INTO v_directory,v_location
from
        dba_directories
where
        DIRECTORY_NAME='DATA_PUMP_DIR';
         
v_parfile_name:='awrrpt_parfilesql';

v_parfile := UTL_FILE.fopen(v_directory, v_parfile_name, 'w', 32767);

v_commands:='define report_type=''html''';
UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='define num_days=2';
UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='define begin_snap='||v_bid;
UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='define begin_snap='||v_eid;
UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='define report_name=''/tmp/awrrpt_'||'2'||'_'||v_bid||'_'||v_eid||'.html''';
UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='@?/rdbms/admin/awrpt.sql';

UTL_FILE.PUT_LINE(v_parfile,v_commands );

v_commands:='exit';

UTL_FILE.PUT_LINE(v_parfile,v_commands );
   
UTL_FILE.fclose(v_parfile); 


UTL_FILE.FREMOVE(v_directory,v_parfile_name);


end;
/

4.Create one shell script to execute this generated parfile.

#!/bin/sh
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0.4/db_1
export ORACLE_SID=pindb
PATH=$PATH:$ORACLE_HOME/bin

sqlplus system/manager <<EOF
execute SYSTEM.USP_AUTOMATE_AWRRPT;
@/hot-bkp/expdp/awrrpt_parfile.sql
EOF
exit


5.Schedule it after 12 AM to get yesterday's record.


Friday, October 4, 2013

Oracle 10g:Silent Mode Upgrade 10.2.0.1.0 to 10.2.0.4.0 into solaris server

Oracle 10g Upgrade 10.2.0.1.0 to 10.2.0.4.0 into solaris.


1.Download file p6810189_10204_Solaris-64.zip for upgrade patch.

2.Copy file to one location and unzip.

mkdir -p /oracle1/10204

unzip p6810189_10204_Solaris-64.zip

3.Create one response.rsp file for input parameter.

Sample file,

Copyright (c) 1999, 2004 Oracle. All rights reserved.          ##
##                                                                ##
## Specify values for the variables listed below to customize     ##
## your installation.                                             ##
##                                                                ##
## Each variable is associated with a comment. The comment        ##
## identifies the variable type.                                  ##
##                                                                ##
## Please specify the values in the following format:             ##
##                                                                ##
##         Type         Example                                   ##
##         String       "Sample Value"                            ##
##         Boolean      True or False                             ##
##         Number       1000                                      ##
##         StringList   {"String value 1","String Value 2"}       ##
##                                                                ##
## The values that are given as <Value Required> need to be       ##
## specified for a silent installation to be successful.          ##
##                                                                ##
##                                                                ##
## This response file is generated by Oracle Software             ##
## Packager.                                                      ##
####################################################################


RESPONSEFILE_VERSION=2.2.1.0.0

#-------------------------------------------------------------------------------
#Name       : UNIX_GROUP_NAME
#Datatype   : String
#Description: Unix group to be set for the inventory directory. Valid only in Unix platforms.
#Example: UNIX_GROUP_NAME = "install"
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME="oinstall"

#-------------------------------------------------------------------------------
#Name       : FROM_LOCATION
#Datatype   : String
#Description: Complete path to the products.xml.
#Example: FROM_LOCATION = "../stage/products.xml"
#-------------------------------------------------------------------------------
FROM_LOCATION="/export/home/oracle/10204/Disk1/stage/products.xml"

#-------------------------------------------------------------------------------
#Name       : FROM_LOCATION_CD_LABEL
#Datatype   : String
#Description: This variable should only be used in multi-CD installations. It includes the label of the compact disk where the file "products.xml" exists. The label can be found in the file "disk.label" in the same directory as products.xml.
#Example: FROM_LOCATION_CD_LABEL = "CD Label"
#-------------------------------------------------------------------------------
FROM_LOCATION_CD_LABEL=<Value Unspecified>

#-------------------------------------------------------------------------------
#Name       : ORACLE_HOME
#Datatype   : String
#Description: Complete path of the Oracle Home.
#Example: ORACLE_HOME = "C:\OHOME1"
#-------------------------------------------------------------------------------
ORACLE_HOME="/oracle"

#-------------------------------------------------------------------------------
#Name       : ORACLE_HOME_NAME
#Datatype   : String
#Description: Oracle Home Name. Used in creating folders and services.
#Example: ORACLE_HOME_NAME = "OHOME1"
#-------------------------------------------------------------------------------
ORACLE_HOME_NAME="OraDb10g_home1"

#-------------------------------------------------------------------------------
#Name       : SHOW_WELCOME_PAGE
#Datatype   : Boolean
#Description: Set to true if the Welcome page in OUI needs to be shown.
#Example: SHOW_WELCOME_PAGE = false
#-------------------------------------------------------------------------------
SHOW_WELCOME_PAGE=true




4.Execute runInstaller to start installation.

Be careful about database and all oracle services not running.

ps -ef|grep oracle


$cd 10204/Disk1

./runInstaller -silent -responseFile /export/home/oracle/10204/Disk1/1.rsp

5.Check Status at the end of completion.If it is successful then installation completed successfully.

The installation of Oracle Database 10g Release 2 Patch Set 3 was successful.

6.Execute root.sh from root user to finally apply changes.