Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for August, 2011

DEINSTALL ORACLE GRID INFRASTRUCTURE standalone install 11.2.0.1 in Aix 6.1

Posted by Hendry chinnapparaj on August 24, 2011

 

Problem:-

If you want to install a New Grid Infra 11.2.0.2 and remove/ wipe out the old version 11.2.0.1, then follow the steps

 

Solution:-

Shutdown the grid listener

Grep for css processes and Kill the processes

Ps –ef|grep

 

xmrep:/u01/ofsad2/stage/Grid> /u01/oracle/product/11.2.0/grid/deinstall/deinstall

Checking for required files and bootstrapping …

Please wait …

Location of logs /u01/oracle/oraInventory/logs/

 

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

 

 

######################## CHECK OPERATION START ########################

Install check configuration START

 

 

Checking for existence of the Oracle home location /u01/oracle/product/11.2.0/grid

Oracle Home type selected for de-install is: SIHA

Oracle Base selected for de-install is: /u01/oracle

Checking for existence of central inventory location /u01/oracle/oraInventory

Checking for existence of the Oracle Grid Infrastructure home /u01/oracle/product/11.2.0/grid

 

Install check configuration END

 

Traces log file: /u01/oracle/oraInventory/logs//crsdc.log

 

Network Configuration check config START

 

Network de-configuration trace file location: /u01/oracle/oraInventory/logs/netdc_check6964497452072461728.log

 

Network Configuration check config END

 

Asm Check Configuration START

 

ASM de-configuration trace file location: /u01/oracle/oraInventory/logs/asmcadc_check8224494113281005570.log

 

Specify the ASM Diagnostic Destination [ ]: /u01/oracle

Specify the diskgroups that are managed by this ASM instance []: DATA

 

De-configuring ASM will drop all the diskgroups at cleanup time. Do you want deconfig tool to drop the diskgroups y|n [y]: y

 

 

######################### CHECK OPERATION END #########################

 

 

####################### CHECK OPERATION SUMMARY #######################

Oracle Grid Infrastructure Home is: /u01/oracle/product/11.2.0/grid

The cluster node(s) on which the Oracle home exists are: (Please input nodes seperated by “,”, eg: node1,node2,…)null

Oracle Home selected for de-install is: /u01/oracle/product/11.2.0/grid

Inventory Location where the Oracle home registered is: /u01/oracle/oraInventory

ASM instance will be de-configured from this Oracle home

Do you want to continue (y – yes, n – no)? [n]: y

A log of this session will be written to: ‘/u01/oracle/oraInventory/logs/deinstall_deconfig2011-08-24_04-47-52-PM.out’

Any error messages from this session will be written to: ‘/u01/oracle/oraInventory/logs/deinstall_deconfig2011-08-24_04-47-52-PM.err’

 

######################## CLEAN OPERATION START ########################

ASM de-configuration trace file location: /u01/oracle/oraInventory/logs/asmcadc_clean8762848314473084786.log

ASM Clean Configuration START

ASM deletion in progress. This operation may take few minutes.

ASM Clean Configuration END

 

Network Configuration clean config START

 

Network de-configuration trace file location: /u01/oracle/oraInventory/logs/netdc_clean3799587469246793438.log

 

De-configuring Listener configuration file…

Listener configuration file de-configured successfully.

 

De-configuring backup files…

Backup files de-configured successfully.

 

The network configuration has been cleaned up successfully.

 

Network Configuration clean config END

 

 

—————————————->

 

Run the following command as the root user or the administrator on node “devu026”.

 

/tmp/deinstall2011-08-24_04-47-09-PM/perl/bin/perl -I/tmp/deinstall2011-08-24_04-47-09-PM/perl/lib -I/tmp/deinstall2011-08-24_04-47-09-PM/crs/install /tmp/deinstall2011-08-24_04-47-09-PM/crs/install/roothas.pl -force  -delete -paramfile /tmp/deinstall2011-08-24_04-47-09-PM/response/deinstall_Ora11g_gridinfrahome1.rsp

 

Press Enter after you finish running the above commands

 

<—————————————-

Oracle Universal Installer clean START

 

Detach Oracle home ‘/u01/oracle/product/11.2.0/grid’ from the central inventory on the local node : Done

 

Delete directory ‘/u01/oracle/product/11.2.0/grid’ on the local node : Done

 

The Oracle Base directory ‘/u01/oracle’ will not be removed on local node. The directory is in use by Oracle Home ‘/u01/oracle/product/10.2/rapmd2’.

 

The Oracle Base directory ‘/u01/oracle’ will not be removed on local node. The directory is in use by central inventory.

 

Oracle Universal Installer cleanup was successful.

 

Oracle Universal Installer clean END

 

 

Oracle install clean START

 

Clean install operation removing temporary directory ‘/tmp/install’ on node ‘devu026’

 

Oracle install clean END

 

Moved default properties file /tmp/deinstall2011-08-24_04-47-09-PM/response/deinstall_Ora11g_gridinfrahome1.rsp as /tmp/deinstall2011-08-24_04-47-09-PM/response/deinstall_Ora11g_gridinfrahome1.rsp3

 

######################### CLEAN OPERATION END #########################

 

 

####################### CLEAN OPERATION SUMMARY #######################

ASM instance was de-configured successfully from the Oracle home

Oracle Restart was already stopped and de-configured on node “devu026”

Oracle Restart is stopped and de-configured successfully.

Successfully detached Oracle home ‘/u01/oracle/product/11.2.0/grid’ from the central inventory on the local node.

Successfully deleted directory ‘/u01/oracle/product/11.2.0/grid’ on the local node.

Oracle Universal Installer cleanup was successful.

 

Oracle install successfully cleaned up the temporary directories.

#######################################################################

 

 

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

 

emrep:/u01/ofsad2/stage/Grid>

 

cd /u01/oracle/product/

rm –rf 11.2.0

Shalom

Hendry

Advertisements

Posted in Oracle11gR2 GRID INFRA and ASM | Leave a Comment »

Restore Database Validate for READ ONLY in Oracle10g

Posted by Hendry chinnapparaj on August 22, 2011

Problem

The standard syntax works only for oracle11g, not for oracle10g with regard to Restore Validation of READONLY Tablespaces. It actually skips the readonly tablespaces (validates only read  write)

RESTORE DATABASE VALIDATE FORCE;

Solution

This will validate the read write and then readonly tablespaces.

Run

{

RESTORE DATABASE VALIDATE FORCE;

RESTORE DATABASE VALIDATE FORCE CHECK READONLY;

}

 

 

Posted in RMAN10G | Leave a Comment »

Uninstall and Install ORACLE CONFIGURATION MANAGER (OCM) for Oracle 11Gr2 and also for Oracle10g

Posted by Hendry chinnapparaj on August 19, 2011

OCM DeInstall Steps for AIX

—————————

# Please do this for ALL Oracle Homes that have had OCM installed previously.

 

– The only env variable needed for the below to work for each home is ORACLE_HOME. Set this as required manually (or use already configured aliases). It is also fine to be set to the agent home too.

export ORACLE_HOME=/u01/oracle/product/…   or

export ORACLE_HOME/u01/oracle/agent[10|11]g

 

– Stop OCM if running. Look for a nmz process relating to the Oracle Home as above in ps -ef output.

Ps –ef|grep nmz

$ORACLE_HOME/ccr/bin/emCCR stop

 

– If OCM database collections were previously done (presence of ORACLE_OCM user in db is enough to confirm this), FOR EACH DATABASE on the server set the env and execute the below as SYS. Otherwise goto next step.

SQL> @?/ccr/admin/scripts/dropocm.sql  <–  this drops the ORACLE_OCM user and does other things too.

 

– If OCM collections were previously done (presence of below deployPackages file is enough to confirm this), execute the below IN EACH ORACLE HOME. Otherwise goto next step.

$ORACLE_HOME/ccr/bin/deployPackages -d $ORACLE_HOME/ccr/inventory/core.jar

 

– Remove the previous OCM install folder FROM EACH ORACLE HOME.

rm -rf $ORACLE_HOME/ccr

 

————————-

OCM Install Steps for AIX

————————-

ftp the latest OCM Patch p5567658_1035_AIX64-5L.zip to the Aix server in binary mode to /u01/oracle/stage

login as oracle (or equiv) software owner

# OCM Install for all Oracle Homes

 

– Set the env for the Oracle Home and run the below to start the install.

cd /u01/oracle/stage

unzip -d $ORACLE_HOME /u01/oracle/stage/p5567658_1035_AIX64-5L.zip

 

cd $ORACLE_HOME/ccr/bin

export CCR_DISABLE_CRON_ENTRY=1

./setupCCR -s 18070588 duty.dba@xyz.com

 

NONE (this is case sensitive too)

CMDOMAU\svc-proxy-oracle-cm@sc-proxy.ytdomau.local:8080

Provide password

 

– Make required OCM customisations.

./emCCR automatic_update off

./emCCR set collection_interval=”FREQ=DAILY; BYHOUR=18; BYMINUTE=10″  <–  change BYHOUR and BYMINUTE to be some time preferably evenings within 6-7pm and record the details in  spreadsheet. For existing deployments please reuse current set times.

 

– Add in below 3 lines of text to OCM properties file to exclude IP and MAC address collection.

vi $ORACLE_HOME/ccr/hosts/<hostname>/config/collector.properties  <–  add in hostname in lowercase before using this line. There should be content in the file already.

ccr.metric.host.ecm_hw_nic.inet_address=false

ccr.metric.host.ecm_hw_nic.mac_address=false

ccr.metric.host.ecm_hw_nic.broadcast_address=false

 

– Cycle OCM to pickup this exclusion.

$ORACLE_HOME/ccr/bin/emCCR stop

$ORACLE_HOME/ccr/bin/emCCR start

 

# OCM Install into each database if Oracle Home is a DB Home

 

– Instrumenting the Database for Configuration Collection

cd $ORACLE_HOME/ccr/admin/scripts

./installCCRSQL.sh collectconfig -s <Oracle Sid>  <–  change Oracle Sid before using this line.

Successfully installed collectconfig in the database with SID=<Oracle Sid>.

 

– Repeat above line for other databases in this Oracle Home.

 

– Run new manual collection to pickup database(s) content

cd $ORACLE_HOME/ccr/bin

./emCCR collect

 

# Go back and do the above for ASM or other Homes (e.g. Agent Home) if present – but with a collection time ideally 5 mins before or after the DB Home.

 

# Remove the OCM install zipfile once the change is complete.

 

 

 

Posted in OCM for Oracle 11GR2 | Leave a Comment »

Managing Memory in Oracle11gR2

Posted by Hendry chinnapparaj on August 17, 2011

Specify a memory target covering the SGA and PGA – and then also the db cache and share pool size parms (as these change from being a setting to a lower limit when memory_target is set). This way it prevents the auto tuning from getting carried away with undersizing either of these.

memory_target            5,368,709,120
db_cache_size              805,306,368
shared_pool_size          805,306,368

Upon dumping the spfile contents to pfile the double underscore hidden parms show where these two are currently operating – you can see it is actually working at present with the shared pool being capped from going any lower:

__sga_target            3,489,660,928
__pga_aggregate_target    1,879,048,192

__db_cache_size        2,583,691,264
__shared_pool_size          805,306,368

__java_pool_size           16,777,216
__large_pool_size           33,554,432
__streams_pool_size           16,777,216
__shared_io_pool_size            0

Posted in Oracle11gR2 Tuning | Leave a Comment »

Rman: Ora-01008 When Connecting To Target in 11.2.0.2

Posted by Hendry chinnapparaj on August 15, 2011

Rman: Ora-01008 When Connecting To Target in 11.2.0.2 [ID 1280447.1]

Problem:

The RMAN Backup job failed with the error

RMAN> backup archivelog all;

Starting backup at 15-08-2011 14:40:29

DBGSQL:     TARGET> select  nvl(max(al.recid), ‘0’),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in (‘X’, ‘A’)    and  al.is_recovery_dest_file = ‘YES’    and  al.creator = ‘RMAN’
DBGSQL:        sqlcode = 1008
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/15/2011 14:40:29
RMAN-03014: implicit resync of recovery catalog failed
ORA-01008: not all variables bound

RMAN> exit

This is a bug for  11.2.0.2 databases or so says Metalink note 1280447.1

To fix the issue – alter system  flush shared_pool ;

Cause
This issue is discussed in Bug#10414993 which has been closed as a duplicate of (non-published)
Bug# 9877980 which in turn will be fixed in the 11.2.0.3 Patch Set Release.

Solution
As a workaround you can flush the shared pool.

SQL> alter system flush shared_pool;

This will allow you to connect with rman.

Posted in RMAN11G | Leave a Comment »