A Few CHM/OS Command For CRS

CHM/OS is installed by default after CRS 11.2.0.2
By default the location is on $GI_HOME/crf/

To check CHM/OS status:
crsctl stat res –t –init

To gather last 1 hour statistics of CHM/OS

oclumon dumpnodeview -allnodes -v -last "01:00:00"  > chmos_data.txt

To gather certain time of statistics
start on  "2012-10-28 21:05:00"
end on  "2012-10-28 21:15:00"
oclumon dumpnodeview -allnodes -v -s  "2012-10-28 21:05:00" -e  "2012-10-28 21:15:00"

More hints on
oclumon showobjects -h
oclumon dumpnodeview -h
oclumon manage -h
oclumon version -h
oclumon debug -h

Steps To Change DBNAME DBID

To reset the DBID and DBNAME, here are some top level steps

• alter system set cluster_database=false scope=spfile;
• shutdown immediate;
• startup mount;
• exit;
• nid TARGET=SYS  DBNAME="your DB Unique Name"
• sqlplus / as sysdba
• create pfile=’dbid_pfile.ora’ from spfile ='spfile_dbname.ora';
• exit;
• Edit  dbid_pfile.ora
• SET *.db_name parameter =’ <DB_UNIQUE_NAME>’
• SET *.cluster_database=true
• Save your changes.
• sqlplus / as sysdba
• create spfile ='spfile_dbname_new.ora' from pfile=’dbid_pfile.ora’;
• startup mount;
• alter database open resetlogs;

Refer :  http://docs.oracle.com/cd/B28359_01/server.111/b28319/dbnewid.htm

Linux HugPages With SGA

Normally when your Linux OS (no matter 32bit or 64bit)has more than 16G RAM while DB SGA is more than 8G, we recommend to set hugpages for SGA.
Just remember we can't use  Automatic Memory Management (AMM) and HugePages together.
The main benefit is to avoid swap in OS and save the PIN RAM,thus overall memory performance improves

The calculation is simple:

# Hugepages/bigpages values are strongly tied to SGA size.
#  vm.nr_hugepages = ((1+3%)*SGA_SIZE)/2MB

# vm.hugetlb_shm_group  = <oracle process group id>
# vm.hugetlb_shm_group  =1001

Main Parameters When Tuning Oracle NET

DEFAULT_SDU_SIZE
RECV_BUF_SIZE
SEND_BUF_SIZE
TCP.NODELAY

The whole list of 11gR1 is here
http://docs.oracle.com/cd/B28359_01/network.111/b28317/sqlnet.htm

A few DataGuard Sqls

Just share the sqls I often use to monitor DataGuard

Monitor log transport sql (primary):
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,'.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
);

Monitor recovery (standby)
select inst_id, process, status, count(*)
from gv$managed_standby
group by  inst_id, process, status
order by inst_id, process, status;

Gap  sql (standby):
set linesize 200
column max_redo_seq format a30
column max_recovery_seq format a30
column gap_seq format a30

SELECT thread,
       max(redo) || ' : ' || max(next_scn_redo) as max_redo_seq,
       max(recovery) || ' : ' ||   max(next_scn_recovery) as max_recovery_seq,
       (max(redo) - max(recovery)) || ' : ' || (max(next_scn_redo) - max(next_scn_recovery)) as gap_seq
FROM (
select thread# as thread,
       max(sequence#) as redo,
       max(next_change#) as next_scn_redo,
       0 as next_scn_recovery,
       0 as recovery
from v$archived_log
group by thread#
union all
select thread# as thread,
       0 as redo,
       0  as next_scn_redo,
       max(next_change#) as next_scn_recovery,
       max(sequence#) as recovery
from v$log_history
group by  thread# )
group by thread
order by thread
/

SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE

ALTER DATABASE REGISTER logfile '/oraarch/testdb/testdb_628319983_1_43377.arc';

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;