Oracle DBA Tips and Techniques

By Arjun Raja

Archive for August, 2010

GRID CONTROL ENVIRONMENT RESTART.

Posted by Arjun Raja on August 30, 2010

GRID CONTROL RESTART

Posted in EM Grid Control | 1 Comment »

DATABASE RESOURCE MANAGER SETUP

Posted by Arjun Raja on August 29, 2010

ORACLE DATABASE RESOURCE MANAGER

Posted in Administration, Oracle 11g | Tagged: | Leave a Comment »

NFS MOUNTED DISKS – ORA27054

Posted by Arjun Raja on August 26, 2010

During RMAN clones , the foll error might appear despite all backup files having 777 permissions if you are using NFS mounted disks mounted on another box.

ORA-19505: failed to identify file
“/ora_export_2/backup//df_714731184_147403_1″
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

Workaround –

alter system set event=”10298 trace name context forever, level 32” scope=spfile;

Restart instance and try a restore.

Once restore is complete , remove the event set and restart cloned instance.

It this does not work try getting the UNIX admin to mount the disks with the following options –

rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfserver=3,tcp

Posted in Administration | Tagged: | 1 Comment »

STOP REPORTS SERVER FROM STARTING AUTOMATICALLY

Posted by Arjun Raja on August 4, 2010

STOP REPORTS SERVER FROM STARTING UP AUTOMATICALLY.

Following needs to be done to stop an auto restart of the reports server on AIX for 10g application server.

Login as owner of 10g application server.

cd $ORACLE_HOME/bin

./opmnctl stopall

cd $ORACLE_HOME/reports/conf

cp rwservlet.properties rwservlet.properties.backup

1. Edit $ORACLE_HOME/reports/conf/rwservlet.properties

Change

SERVER_IN_PROCESS=YES

To:

SERVER_IN_PROCESS=NO

cd $ORACLE_HOME/opmn/conf

cp opmn.xml opmn.xml.backup.

b. Edit $ORACLE_HOME/opmn/conf/opmn.xml.

Change
category id=”urlping-parameters”
data id=”/reports/rwservlet/pingserver?start=auto” value=”200″/

To:
category id=”urlping-parameters”
data id=”/reports/rwservlet/” value=”200″/

cd $ORACLE_HOME/bin

./opmnctl startall

This will stop the report server from starting automatically.

Posted in 10g Application Server | Tagged: , | Leave a Comment »

CREATION OF STANDBY DATABASE 11g Rel 2 ON SAME HOST.

Posted by Arjun Raja on August 3, 2010

Create a standby database on the same host, using RMAN with a backup from an active database .

PRIMARY DATABASE – ORCL ON TEST010 BOX

STANDBY DATABASE – STBY ON TEST010 BOX.

In PRIMARY – orcl database >

export ORACLE_SID=orcl

export ORACLE_HOME=/u01/oracle/product/11.2

sql > Alter database force logging;

Create password file for the primary and standby database.

cd $ORACLE_HOME/dbs

orapwd file=orapworcl entries=10 password=temp force=y

cp orapworcl orapwstby – Do not create separate password file for standby – use cp .

Try a test connection –

$ sqlplus sys/temp@orcl as sysdba – should connect to primary.

ON PRIMARY – ORCL database.

alter system set LOG_ARCHIVE_DEST_1=’location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL’;

alter system set LOG_ARCHIVE_DEST_2=’SERVICE=STBY LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY’;

alter system set FAL_SERVER=STBY;

alter system set FAL_CLIENT=ORCL;

alter system set DB_UNIQUE_NAME=’ORCL’ scope=spfile;

alter system set log_archive_config=’dg_config=(orcl,stby)’;

STANDBY DATABASE ON SAME BOX.

Copy initorcl.ora and edit to reflect all stby entries.

vi initstby.ora

stby.__db_cache_size=155189248
stby.__java_pool_size=4194304
stby.__large_pool_size=4194304
stby.__oracle_base=’/u01/ofsad2’#ORACLE_BASE set from environment
stby.__pga_aggregate_target=159383552
stby.__sga_target=423624704
stby.__shared_io_pool_size=0
stby.__shared_pool_size=243269632
stby.__streams_pool_size=4194304
*.audit_file_dest=’/u01/ofsad2/admin/stby/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u02/oradata/stby/control01.ctl’,’/u02/oradata/stby/control02.ctl’
*.db_block_size=8192
*.db_file_name_convert=’/u02/oradata/orcl/’,’/u02/oradata/stby/’
*.db_name=’orcl’
*.db_recovery_file_dest=’/u02/oradata/flash_recovery_area’
*.db_recovery_file_dest_size=7340032000
*.db_unique_name=’stby’
*.diagnostic_dest=’/u01/ofsad2′
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)’
*.fal_client=’STBY’
*.fal_server=’ORCL’
*.log_archive_config=’dg_config=(orcl,stby)’
*.LOG_ARCHIVE_DEST_1=’location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STBY’
*.log_archive_dest_2=’SERVICE=orcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl’
*.log_archive_dest_state_2=’ENABLE’
*.log_file_name_convert=’/u02/oradata/orcl/’,’/u02/oradata/stby/’
*.open_cursors=300
*.pga_aggregate_target=157286400
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=423624704
*.standby_file_management=’auto’
*.undo_tablespace=’UNDOTBS1′

. setstby

sql > create spfile from pfile;

Configure Listener for Static service registration

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = apex4)
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = apex4)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = orcl)
)
(SID_DESC =
(ORACLE_HOME = /u01/oracle/product/11.2)
(SID_NAME = stby)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
)

Configure service name in tnsnames.ora

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxx.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stby)
)
)

STARTUP NOMOUNT STANDBY DATABASE –

sql > startup nomount;

Now run rman command to create standby database –

stby:/u01/oracle/product/11.2/dbs> rman target sys/temp@orcl auxiliary /

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Jul 27 11:55:10 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1246075258)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby dorecover from active database ;

Starting Duplicate Db at 27/JUL/10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/27/2010 12:00:53
RMAN-06217: not connected to auxiliary database with a net service name

Therefore connect to auxiliary using connect string –

rman target sys/temp@orcl auxiliary sys/temp@stby

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Jul 27 12:02:22 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1246075258)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby dorecover from active database ;

Starting Duplicate Db at 27/JUL/10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=63 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/oracle/product/11.2/dbs/orapworcl’ auxiliary format
‘/u01/oracle/product/11.2/dbs/orapwstby’ ;
}
executing Memory Script

Starting backup at 27/JUL/10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
Finished backup at 27/JUL/10

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘/u02/oradata/stby/control01.ctl’;
restore clone controlfile to ‘/u02/oradata/stby/control02.ctl’ from
‘/u02/oradata/stby/control01.ctl’;
}
executing Memory Script

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/oracle/product/11.2/dbs/snapcf_orcl.f tag=TAG20100727T121951 RECID=4 STAMP=725458793
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27/JUL/10

Starting restore at 27/JUL/10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 27/JUL/10

contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
“/u02/oradata/stby/temp01.dbf”;
switch clone tempfile all;
“/u02/oradata/stby/fla_tbs1.dbf” ;
and more …
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oradata/stby/temp01.dbf in control file

executing command: SET NEWNAME

And more…

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u02/oradata/orcl/sysaux01.dbf
And more….
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27/JUL/10

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like “/u02/oradata/flash_recovery_area/ORCL/archivelog/2010_07_27/o1_mf_1_221_64wqpxq8_.arc” auxiliary format
“/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_%u_.arc” ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script

Starting backup at 27/JUL/10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=221 RECID=180 STAMP=725458909
output file name=/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 27/JUL/10

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc

datafile 1 switched to datafile copy
And more…
input datafile copy RECID=10 STAMP=725458912 file name=/u02/oradata/stby/fla_tbs1.dbf

contents of Memory Script:
{
set until scn 5284605;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 27/JUL/10
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 221 is already on disk as file /u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc
archived log file name=/u02/oradata/flash_recovery_area/STBY/archivelog/2010_07_27/o1_mf_1_221_0mljr7uu_.arc thread=1 sequence=221
media recovery complete, elapsed time: 00:00:01
Finished recover at 27/JUL/10
Finished Duplicate Db at 27/JUL/10

RMAN>

CREATE STANDBY LOGS –

Run on both instances –

ON PRIMARY

alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo03.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo02.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo01.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/orcl/stdby_redo04.rdo’ size 52428800;

ON STANDBY

alter database add standby logfile ‘/u02/oradata/stby/stdby_redo01.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo02.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo03.log’ size 52428800;
alter database add standby logfile ‘/u02/oradata/stby/stdby_redo04.rdo’ size 52428800;

On STANDBY – START MEDIA RECOVERY.

sql > Recover managed standby database disconnect;

Tail –f alert logs in standy site to check if all is well…

Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_269_65dll3bx_.arc
Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_270_65dll45g_.arc
Media Recovery Log /u02/oradata/flash_recovery_area/STBY/archivelog/2010_08_02/o1_mf_1_271_65dll3z5_.arc
Media Recovery Waiting for thread 1 sequence 272 (in transit)

Also confirm with this sql that all scripts are in place…

ON PRIMARY –

select thread#, sequence#, archived, applied from v$archived_log where dest_id=2 and sequence# in ( select max(sequence#) from v$archived_log);

THREAD# SEQUENCE# ARC APPLIED
———- ———- — ———
1 274 YES YES
SQL>

select
LOG_ARCHIVED_ON_PRIMARY, LOG_APPLIED_ON_STANDBY, LOG_ARCHIVED_ON_PRIMARY-LOG_APPLIED_ON_STANDBY LOG_GAP
from
(select sequence# LOG_ARCHIVED_ON_PRIMARY from v$archived_log where dest_id=1 and archived=’YES’ and completion_time=(select max(completion_time) from v$archived_log where dest_id=1)) primary,
(select sequence# LOG_APPLIED_ON_STANDBY from v$archived_log where dest_id=2 and applied=’YES’ and completion_time=(select max(completion_time) from v$archived_log where dest_id=2))
/

LOG_ARCHIVED_ON_PRIMARY LOG_APPLIED_ON_STANDBY LOG_GAP

274 274 0

Posted in High Availability, Oracle 11g | Tagged: , | Leave a Comment »