library cache pins/locks

Note:169139.1 - on investigating library cache pin/locks situations

Oracle Clusterware installation fails on Linux

when running root.sh, the following error is returned:

Failed to upgrade Oracle Cluster Registry configuration

Solution:

Note:465001.1
If using any form of multipathing (EMC PowerPath, MPIO, device-mapper-multipath, etc.) to access Clusterware devices, be sure to obtain and apply Oracle 10.2.0 CRS patch for Bug 4679769 when installing the Oracle Clusterware (Step 5).


After applying the patch, installation was successful.

configure ssh for rac installation

1. create the keys

/usr/bin/ssh-keygen -t dsa
/usr/bin/ssh-keygen -t rsa

2. copy the public keys into authorized_keys

3. run
exec /usr/bin/ssh-agent $SHELL
/usr/bin/ssh-add

CLUVFY

to run the prerequisites check for oracle clusterware, download the latest cluvfy.

1. unzip into /home/oracle/cvu
2. setup the environment

export CV_HOME=/home/oracle/cvu
export CV_JDKHOME=/home/oracle/cvu/jdk/jre

3. run

./cluvfy stage -pre crsinst -n hostname -orainv dba -r 10gR2

(don't forget to specify the -r version)

TAR + gzip + remove files

tar --remove-files -czf datafiles.tar.gz *

LIST BACKUP

to list backups taken at a certain point in time:

How To List All The Named Events Set For A Database

Metalink

SQL> oradebug setmypid //This will set the current session's pid.

SQL> oradebug dump events 1 //This will dump the events information in the tracefile based on the level

SQL> oradebug tracefile_name //This will return the trace file generated for this event.

ASM and Clulster Manager (CSSD)

One interesting thing, which oracle installation docs keep in secret is Cluster Manager service, which is needed to run ASM instance.

in 10.1 it is installed by default (in 10.2 it is installed along with ASM instance creation, I believe)

How do you setup / remove / modify Cluster Synchronization Services Daemon (CSSD) ?

to uninstall: $ORACLE_HOME/bin/localconfig delete
to install: $ORACLE_HOME/bin/localconfig add

to start: /etc/init.d/init.cssd start
to stop: /etc/init.d/init.cssd stop

CONTROL_FILES

control_files parameter is a list of comma-separated strings! it's not a single string of comma-separated values, which would be more logical to my mind...

to change the control_files parameter:
alter system set control_files="/path/control01.ctl", "/path/control02.ctl", "/path/control03.ctl" scope=spfile;

MATVIEWS

first experience with materialized views

1. the refresh needs hell lot of UNDO space and produces tonns of archivelog
2. if mview was created before the MVIEW LOG, then you cannot perform FAST refresh. You need to perform COMPLETE refresh first. Otherwise you will get ORA-12034: materialized view log on "%s"."%s" younger than last refresh. Metalink Note:365157.1 discusses this problem.
3. to refresh it manually, use the DBMS_MVIEW package's refresh procedure
DBMS_MVIEW.REFRESH('[MVIEW NAME]','C'); --where C is for Complete
4. MVIEW must be registered with it's MASTER site and be listed in SYS.SLOG$ table.
5. It is much cheaper to drop and re-create the m.view than to perform a complete refresh!

How to monitor the progress of a materialized view refresh (MVIEW)
MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring