Oracle DBA Tips and Techniques

By Arjun Raja

Archive for the ‘Oracle 11g’ Category

INS-07003 Unexpected error occured while accessing the bean store

Posted by Arjun Raja on February 27, 2011

Installation of 11g rel2 ( 11.2.0.2) on AIX throws the following error – [ INS-07003] Unexpected error occured while accessing the bean store- this is the fix to avoid this error.

./runInstaller …

(Embedded image moved to file: pic32494.gif)

You can continue and the installation does go through but to avoid this warning, this is the workaround -.

First of all make a copy of the unix login users .profile.

cp .profile .profile.bkup

Then edit the .profile.

The $PS1 variable and other variables like the ones below must be hashed out before commencing the installation ….

# Command Prompt Setup
#Red=”33[31m” # PROD/PROD DR
#Yellow=”33[33m” # OAT/OAT DR
#Blue=”33[34m” # UAT
#Cyan=”33[36m” # TRN
#Green=”33[32m” # DEV/SIT/TEST
#NC=”33[0m” # No Colour
#BoldON=”33[1m”
#BoldOFF=”33[22m”
# Change two colour variables below to match LPAR env
#MESSAGE=`echo “$Green$BoldON”`
#NOMESSAGE=`echo “$Green$BoldOFF$NC”`
#export MESSAGE NOMESSAGE
#export PS1=”$MESSAGE${ORACLE_SID}$NOMESSAGE:$PWD> ”

Save the .profile and open a new session –

echo $PS1 – you must receive a blank response.

Now start the installer again

./runInstaller …….next screen appears without any warnings…

(Embedded image moved to file: pic07596.gif)

Once complete, restore the original .profile so that future sessions are back to normal.

mv .profile.bkup .profile

Posted in Oracle 11g | Tagged: , | Leave a 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 »

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 »

USING FLASHBACK DATABASE TO ROLLBACK FROM 11g to 10g.

Posted by Arjun Raja on July 2, 2010

Using flashback database to rollback a failed upgrade to 11g works.

This method can probably be used as a first line of defence to avoid a time consuming restore incase a 11g upgrade fails.

However it goes without saying that a proper backup should also be taken prior to attempting any upgrade.

In 10g database –

Flashback should be turned on prior to upgrade –

Create a guaranteed restore point .

sql > create restore point arjun guarantee flashback database ;

Make sure atleast one more flashback log is created in the 10g environment after the restore point is created –

If not and the existing flashback log is appended after the database is upgraded to 11g , the following error will appear later on while trying to flashback the database.

“ORA-38792: encountered unknown flashback record from release 11.0.0.0.0″

Now Upgrade database to 11.2.0.1 –

Once complete –

Still in 11g environment –

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production

SQL> shutdown immediate;

Log back into the 10g environment –

cd $ORACLE_HOME/dbs

/u01/oracle/product/10.2/ofsad2/dbs> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jul 2 07:48:00 2010

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

Connected to an idle instance.

SQL> startup mount;

Database mounted.

SQL> flashback database to restore point arjun;

ALERT LOG –

flashback database to restore point arjun
Flashback Restore Start
Fri Jul 2 07:49:37 2010
Flashback Restore Complete
Flashback Media Recovery Start
parallel recovery started with 3 processes
Flashback Media Recovery Log /u03/oradata/flash_recovery_area/DB10G/archivelog/2010_07_01/o1_mf_1_120_62r31dt6_.arc
Fri Jul 2 07:49:37 2010
Incomplete Recovery applied until change 5868883
Flashback Media Recovery Complete
Completed: flashback database to restore point arjun

sql > alter database open resetlogs;

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Productio
NLSRTL Version 10.2.0.4.0 – Production

Get rid of the restore point created.

SQL> select * from v$restore_point order by scn;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
———- ——————— — ————
TIME
—————————————————————————
NAME
——————————————————————————–
5868880 2 YES 646053888
01/JUL/10 11:31:27.000000000 AM
ARJUN

SQL> drop restore point arjun;

Restore point dropped.

SQL> select * from v$restore_point order by scn;

no rows selected

SQL>

Posted in Oracle 11g | Tagged: , | 2 Comments »

11g RELEASE 2 GRIDINFRASTRUCTURE INSTALLATION

Posted by Arjun Raja on February 19, 2010

11G RELEASE 2 GRIDINFRASTRUCTURE -ASM – WITH ASM INSTANCE UPGRADE.

ASM RELEASE 2 INSTALLATION

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

11g RELEASE 2 ADDING DISKGROUP TO ASM

Posted by Arjun Raja on February 19, 2010

11G RELEASE 2 ADDING ASM DISK GROUPS

Adding asm disk groups

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

11G RELEASE 2 DATABASE CREATION USING ASM DISKS

Posted by Arjun Raja on February 19, 2010

11G Release 2 database creation using DBCA – and assigning asm disks.

Create database using ASM disks

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

11g RELEASE 2 DATABASE SOFTWARE INSTALLATION

Posted by Arjun Raja on February 19, 2010

After installing the 11g rel2 gridinfrastructure for asm/rac install the oracle database software in a separate oracle home –

11g_rel2_only_Database_software_installation

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

11G RELEASE 2 GRIDINFRASTRUCTURE INSTALLATION

Posted by Arjun Raja on February 19, 2010

This document shows how to install the new gridinfrastructure software needed for configuring asm and rac in 11g release 2. In this example, we are installing and creating an asm instance only on a standalone server – no rac

11g_rel2_gridinfra_including_asm

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

EXPORT ( EXPDP ) WITH COMPRESSION OF DUMPS IN 11g

Posted by Arjun Raja on January 21, 2010

Datapump in 11g has a good feature to reduce size of exports and resources used on machines and tapes by compressing the dumps as and when the export happens..

In version 10g, datapump did not have the compress option for data – only the default option to compress METADATA only.

Example in 10g –

TEST schema using datapump –

nohup expdp system/xxxxxx schemas=test directory=data_pump_dir dumpfile=test.dmp logfile=test.log &

Export took 2 hours 10 minutes.

Export dump size was 91 gb.

The same data was exported in 11g –

Only difference was adding the following bit to the expdp command in 11g – > compression=all

nohup expdp system/xxxx compression=all schemas=test
directory=data_pump_dir dumpfile=test.dmp logfile=test.log &

Export took 1 hour 15 minutes.

Export dump size – 13 gb

In 11g it took just over half the time and saved 7 times the space which will tremendously reduce the amount of data moved off to tape on a daily basis especially in some databases where exports are taken daily and moved to tape.

Also there is no need to use unix pipes or uncompress dumps before commencing the import as Oracle takes care of that.

Posted in Oracle 11g | Tagged: , | 2 Comments »