Thursday, 24 July 2014

startup failed: ORA-01078: failure in processing system parameters, LRM-00109: could not open parameter file


OS: OEL 5.7
DB: 11.2.0.3.0
64 Bit

RMAN> startup nomount ;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u02/oracle/ERP/db/tech_st/11.2.0/dbs/initERP.ora'

starting Oracle instance without parameter file for retrieval of spfile
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 07/24/2014 11:08:06
RMAN-04014: startup failed: ORA-04031: unable to allocate 1048608 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","row cache")




During rman restore and recovery testing i was trying to restore spfile from backup. But issuing startup nomount with RMAN threw "ora-10078".

I remember reading somewhere, rman will start database without  spfile, using a dummy parameter file. Seeing the error i decided to crosscheck with rman documentaion and found it should start using dummy parameter file if one is not available

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmadvre.htm#BRADV363
When the server parameter file is not available, RMAN starts the instance with a dummy parameter file. For example, enter the following command:




I could see that its trying to open init file and as its not availabe its gives the error.

Just create a temporary parameter file with only db_name=[db_name] entry





Now connected to rman and issued startup nomount
That was enough for rman to start database in nomount mode.


Monday, 27 January 2014

Configuring standby R12 application/database server using RMAN

Oracle Application R12.1.3
Database 11.2.0.3
Os OEL 5.7


vmprod-primary - Primary Database
vmprod-standby - Standby Database

Installation base (Both Application and Database)
/u02/oracle/ERPDG

1. Prepare Primary Database Server

Check if force logging is enabled

SYS@ERPDG*vmprod-primar$ select force_logging from v$database;


If not enabled enable it with following command

SYS@ERPDG*vmprod-primar$ alter database force logging ;


Create Oracle Net service names and Listener

Modification required to for net connfiguration to setup standby database will be done using ifiles


As autoconfig overwrites changes made to tnsnames and listener files, configuration changes required for net configuration files to setup standby database will be done using ifiles.

Create/Modify ifiles

Go to TNS_DMIN
cd $TNS_ADMIN

Ifile names
listener = listener_ifile.ora
tsnames = ERPDG_vmprod-primary_ifile.ora

Note: Check the last line of listener.ora and tnsnames.ora files to see the ifile names, create If these files are not existing

Modify the tnsnames ifile

ERPDG_vmprod-primary_ifile.ora

--This will point to standby database
-- primary uses this service name to send redo to the standby database
--when primary becomes standby on switch over, modify host parameter to point to standby db --of that time

ERPDGS=
       (DESCRIPTION=
       (ADDRESS=(PROTOCOL=tcp)
       (HOST=  vmprod-standby.hamra.com)
       (PORT= 1532)
       )
       (CONNECT_DATA=(SID=ERPDG)
       )
       )         


--FAL Service - to request missing redo incase primary is not able to send


ERPDGF=
       (DESCRIPTION=
       (ADDRESS=(PROTOCOL=tcp)
       (HOST= vmprod-standby.hamra.com)
       (PORT= 1531) )
       (CONNECT_DATA=(SID=ERPDG) ) )



Modify the listener ifile

This listener will be used when this primary server becomes standby on switchover

listener_ifile.ora
ERPDGS =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)
(HOST= vmprod-primary.hamra.com)
(PORT= 1532)
)
)

SID_LIST_ERPDGS=
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /u02/oracle/ERPDG/db/tech_st/11.2.0)
(SID_NAME=ERPDG)
)
)

STARTUP_WAIT_TIME_ERPDGS = 0
CONNECT_TIMEOUT_ERPDGS = 10 TRACE_LEVEL_ERPDGS = OFF

LOG_DIRECTORY_ERPDGS = /u02/oracle/ERPDG/db/tech_st/11.2.0/network/admin
LOG_FILE_ERPDGS = ERPDGS
TRACE_DIRECTORY_ERPDGS = /u02/oracle/ERPDG/db/tech_st/11.2.0/network/admin
TRACE_FILE_ERPDGS = ERPDGS
ADMIN RESTRICTIONS_ERPDGS = OFF



make sure listener can be started.

lsnrctl start ERPDGS



Create password file

$ORACLE_HOME/dbs - password file location

orapwd file=orapwERPDG ignorecase=y


Set initialization parameters required for primary role


set primary database initialization parameters , if pfile file is used this has to be done using an ifile.

alter system set db_unique_name = ERPDG  scope =spfile;

alter system set log_archive_dest_1 = 'LOCATION=/u01/oracle/ERPDG/arch' ;

alter system set log_archive_dest_2 = 'SERVICE=ERPDGS LGWR ASYNC=20480 DB_UNIQUE_NAME=ERPDGS OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30';


alter system set log_archive_config='dg_config=(ERPDG,ERPDGS)' ;

alter system set  log_archive_dest_state_2 = defer;

alter system set log_archive_min_succeed_dest=1;

alter system set standby_file_management = AUTO;


alter system set Remote_login_passwordfile = exclusive scope=spfile ;

alter system set log_archive_format = 'ERPDG_%t_%s_%r.arc' scope=spfile ;

# db_file_name_convert: do not need; same directory structure

alter system set log_file_name_convert ='/u01/oracle/ERPDG/arch','/u01/oracle/ERPDG/arch' scope=spfile ;


alter system set fal_server = ERPDGF

Restart the database

Shutdown immediate

startup mount

Check if database is in archivelog mode

archive log list;

if disabled enable archiving

alter database archivelog ;


Add standby redo


Add standby redo as many as your redo log files. These files are used when this server becomes standby



alter database add standby logfile group 15 '/u03/erpdgdata/sredolog15.log' size 104857600;

alter database add standby logfile group 16 '/u03/erpdgdata/sredolog16.log' size 104857600;

alter database add standby logfile group 17 '/u03/erpdgdata/sredolog17.log' size 104857600;

alter database add standby logfile group 18 '/u03/erpdgdata/sredolog18.log' size 104857600;

alter database add standby logfile group 19 '/u03/erpdgdata/sredolog19.log' size 104857600;

alter database add standby logfile group 20 '/u03/erpdgdata/sredolog20.log' size 104857600;

alter database add standby logfile group 21 '/u03/erpdgdata/sredolog21.log' size 104857600;

alter database add standby logfile group 22 '/u03/erpdgdata/sredolog22.log' size 104857600;


Prepare primary for cloning

$ cd $RDBMS_ORACLE_HOME/appsutil/scripts/context_name/
$ perl adpreclone.pl dbTier
$ cd $INST_TOP/admin/scripts/
$ perl adpreclone.pl appsTier

copy database(not dbf files) and application tier files from primary to standby


copy apps and db from base /u02/oracle/ERPGD to standby server


2. Prepare Standby Database Server

Server Name : vmprod-standby


As this is copied from primary tns_admin location and related files has to be renamed to reflect standby server name

on standby server go to TNS_ADMIN

cd $ORACLE_HOME/network/admin

change theTNS_ADMIN folder name to reflect new contextname

cd /u02/oracle/ERPDG/db/tech_st/11.2.0/network/admin/

mv ERPDG_vmprod-primary ERPDG_vmprod-standby



Modify the listener.ora file

The ifile location will be pointing to the primary db location, change it to reflect standby location

Change
IFILE=/u02/oracle/ERPDG/db/tech_st/11.2.0/network/admin/ERPDG_vmprod-primary/listener_ifile.ora

To

IFILE=/u02/oracle/ERPDG/db/tech_st/11.2.0/network/admin/ERPDG_vmprod-standby/listener_ifile.ora

Edit the listener_ifile to change the hostname to standby name

##standby listener to be started when this server becomes standby
ERPDGS =
       (ADDRESS_LIST =
       (ADDRESS=(PROTOCOL=TCP)
       (HOST= vmprod-standby.hamra.com)
       (PORT= 1532)
       )
       )

SID_LIST_ERPDGS=
       (SID_LIST =
       (SID_DESC =
       (ORACLE_HOME= /u02/oracle/ERPDG/db/tech_st/11.2.0)
       (SID_NAME=ERPDG)
       )
       )

STARTUP_WAIT_TIME_ERPDGS = 0
CONNECT_TIMEOUT_ERPDGS = 10 TRACE_LEVEL_ERPDGS = OFF

LOG_DIRECTORY_ERPDGS = /u02/oracle/ERPDG/db/tech_st/11.2.0/network/admin
LOG_FILE_ERPDGS = ERPDGS
TRACE_DIRECTORY_ERPDGS = /u02/oracle/ERPDG/db/tech_st/11.2.0/network/admin
TRACE_FILE_ERPDGS = ERPDGS
ADMIN RESTRICTIONS_ERPDGS = OFF


tnsnames file copied from primary will  still be having primary related location and names.

Edit the tnsnames file to change the ifile location and ifile name

IFILE=/u02/oracle/ERPDG/db/tech_st/11.2.0/network/admin/ERPDG_vmprod-primary/ERPDG_vmprod-primary_ifile.ora
to
IFILE=/u02/oracle/ERPDG/db/tech_st/11.2.0/network/admin/ERPDG_vmprod-standby/ERPDG_vmprod-standby_ifile.ora

Rename the tnsnames ifile to standby context name

mv ERPDG_vmprod-primary_ifile.ora ERPDG_vmprod-standby_ifile.ora


Edit the tnsnames ifile to modify the fal service



ERPDGS=
       (DESCRIPTION=
       (ADDRESS=(PROTOCOL=tcp)
       (HOST=  vmprod-standby.hamra.com)
       (PORT= 1532)
       )
       (CONNECT_DATA=(SID=ERPDG)
       )
       )         


##FAL service

ERPDGF=
       (DESCRIPTION=
       (ADDRESS=(PROTOCOL=tcp)
       (HOST= vmprod-primary.hamra.com)
       (PORT= 1531) )
       (CONNECT_DATA=(SID=ERPDG) ) )




Modify initfile

Modify the init file to reflect the standby server related changes
go to $ORACLE_HOME/dbs

if pfile does not exist go to primary database and issue “Create pfile from spfile” to create pfile and copy to standby server

Modfify/comment local_listener parameter , diagnostic_dest location and any other relevant parameters to reflect the changes
Make sure diagnostic_dest is accessible ,if it doesn’t exist create it

startup the database in nomount mode with pfile

set environment on standby, as environment file is not configured set these manually

export ORACLE_SID=ERPDG
export ORACLE_HOME=/u02/oracle/ERPDG/db/tech_st/11.2.0
export PATH=$PATH:$ORACLE_HOME/bin
export  TNS_ADMIN=$ORACLE_HOME/network/admin/ERPDG_vmprod-standby

connect as sysdba and issue

startup nomount pfile=/u02/oracle/ERPDG/db/tech_st/11.2.0/dbs/initERPDG.ora



Test connectivity between two servers

Both servers should be connectable with service names.

From standby to Primary

export ORACLE_SID=ERPDG
export ORACLE_HOME=/u02/oracle/ERPDG/db/tech_st/11.2.0
export PATH=$PATH:$ORACLE_HOME/bin
export  TNS_ADMIN=$ORACLE_HOME/network/admin/ERPDG_vmprod-standby


Make sure standby listener is running, if its not start it.


lsnrctl start ERPDGS


Connecting to standby server using service name 'ERPDGS'

-bash-3.2$ tnsping ERPDGS

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 23-JAN-2014 20:11:52

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/u02/oracle/STGD/db/tech_st/11.2.0/network/admin/STGD_vmprod-standby/sqlnet_ifile.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (HOST= vmprod-standby.hamra.com) (PORT= 1532)) (CONNECT_DATA=(SID=ERPDG)))
OK (0 msec)

Connecting to primary server using "ERPDGF" service name

-bash-3.2$ tnsping ERPDGF

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 23-JAN-2014 20:11:57

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/u02/oracle/STGD/db/tech_st/11.2.0/network/admin/STGD_vmprod-standby/sqlnet_ifile.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (HOST= vmprod-primary.hamra.com) (PORT= 1531)) (CONNECT_DATA=(SID=ERPDG)))
OK (0 msec)

From Primary to standby

Go to primary server and test primary can also  reach standby with service name

use database environment file to set environment variables

Connecting to standby server from primary server using net service name 'ERPDGS'

-bash-3.2$ tnsping ERPDGS

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 23-JAN-2014 20:13:03

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:
/u02/oracle/ERPDG/db/tech_st/11.2.0/network/admin/ERPDG_vmprod-primary/sqlnet_ifile.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (HOST= vmprod-standby.hamra.com) (PORT= 1532)) (CONNECT_DATA=(SID=ERPDG)))
OK (0 msec)



Test connecting to primary and standby as sysdba using service names

sqlplus sys/password@erpdgf as sysdba
sqlplus sys/password@erpdgs as sysdba

Before starting active duplication do a log switch on primary , if no archive logs are available on primary active duplication fails complaining “no archive logs found”


Connect to rman for duplicating primary on standby server


rman target sys/******@erpdgf auxiliary sys/*****@erpdgs


Issue the following rman commands to duplicate the primary database


DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
dorecover
SPFILE SET "db_unique_name"="ERPDGS"
SET LOCAL_LISTENER=""
SET LOG_ARCHIVE_DEST_2="service=ERPDGS ASYNC REGISTER VALID_FOR=(online_logfile,primary_role) DB_UNIQUE_NAME=ERPDG"
SET FAL_SERVER="STGD" COMMENT "Is primary"
SET DIAGNOSTIC_DEST="/u02/oracle/ERPDG/db/tech_st/11.2.0/admin/ERPDG_vmprod-standby"  COMMENT "DIAGNOSTIC Destination on standby server"
SET UTL_FILE_DIR="/tmp"
NOFILENAMECHECK;


Once duplication is completed database will be in mount state


Update the filesystem configuration on standby server

Setup the file system configurations for the new standby environment. As the new environment is not yet configured enter ORACLE_HOME location manually

cd $ORACLE_HOME/appsutil/clone/bin

perl adcfgclone.pl dbTechStack

This will start listener for the new environment, source database environment file and stop the listener this is the listener for e-businesssuite database, start this listener when standby becomes primary server. Standby listener (ERPDGS) should be running for primary to send redo to standby


Put the standby database in 'managed recover' mode

Connect as sysdba and issue

alter database recover managed standby database disconnect from session;

Issue archive log list on both servers and compare output to confirm archivelog applying on standby

SYS@ERPDG*vmprod-primar>archive log list ;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /u01/oracle/ERPDG/arch
Oldest online log sequence     28
Next log sequence to archive   35
Current log sequence       35

SYS@ERPDG*vmprod-standb>archive log list ;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /u01/oracle/ERPDG/arch
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence       0

The above output shows both are not in sync, this is because archive destination 2 on primary server was set up as deffer.

Enable redo transport on primary

Start shipping redo data from primary to standby server

go to primary server, connect as sysdba and issue

alter system set log_archive_dest_state_2=enable


Verify redo log shipping/applying

On primary database do a log switch to confirm redo from primary is getting transported to standby and applied

alter database switch logfile ;

Now issue archive log list on both primary and standby and compare the output


Use v$archive_dest_status to check archivelog destinations and thier status

Query v$archived_log to see sent/received and applied archivelogs

Check the standby database alert log file to know recovery progress

Configure standby application tier


cd $COMMO_TOP/clone/bin
$ perl adclonectx.pl $INST_TOP/appl/admin/[primary_contextfile].xml

Manually resolve the environment variable locations as application tier is yet to be configured.

Note : If application tier directory structre is different from primary use "perl adcfgclone.pl atTechStack"

cd APPL_TOP/ad/12.0.0/bin
$ perl adconfig.pl contextfile=$INST_TOP/appl/admin/[standby_contextfile].xml run=INSTE8