Useful links

HOWTO: Manually Convert Single Instance to RAC on Unix
http://orabackus.wordpress.com/2007/11/18/howto-manually-convert-single-instance-to-rac-on-unix/

ASMLib

https://blogs.oracle.com/wim/entry/asmlib

Oracle Clusterware Node Fencing
http://www.trivadis.com/uploads/tx_cabagdownloadarea/Trivadis_oracle_clusterware_node_fencing_v.pdf

Oracle Database 12c: New Features - Pluggable Databases

http://www.orafaq.com/node/2756

ASM: Difference's between CORSE and FINE Striping

COARSE STRIPING	FINE GRAINED STRIPING
It is used for all voluminous input/output, e.g. input/output operations on datafiles.	It is used for all small input/output, e.g. input/output operations on online redolog files and control files.
The size of the coarse grained data stripes is large.	The size of the fine grained data stripes is small.
It manages the load balance across the disk groups.	It spreads the load on disk groups reducing latency for certain file types.
The size of the coarse-grained stripe is always equal to the size of ASM Allocation Units (AU).	The size of the fine-grained stripe is always 128 KB.
The size for coarse striping can be set using the _asm_ausize parameter.	The size for fine grained striping can be set using the _asm_stripesize parameter.

Extra Redo generation during online backup

There is not excessive redo generated, there is additional information logged into the online redo log during a hot backup the first time a block is modified in a tablespace that is in hot backup mode. In hot backup mode only 2 things are different:

The first time a block is changed in a datafile that is in hot backup mode, the ENTIRE 

BLOCK is written to the redo log files, not just the changed bytes.  Normally only the 

changed bytes (a redo vector) is written. In hot backup mode, the entire block is logged 

the FIRST TIME.  This is because you can get into a situation where the process copying 

the datafile and DBWR are working on the same block simultaneously.  Lets say they are 

and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). 

 The backup program goes to read an 8k Oracle block.  The OS gives it 4k.  Meanwhile -- 

DBWR has asked to rewrite this block.  the OS schedules the DBWR write to occur right 

now.  The entire 8k block is rewritten.  The backup program starts running again 

(multi-tasking OS here) and reads the last 4k of the block.  The backup program has now 

gotten an impossible block -- the head and tail are from two points in time.  We cannot 

deal with that during recovery.  Hence, we log the entire block image so that during 

recovery, this block is totally rewritten from redo and is consistent with itself at 

least.  We can recover it from there.

The datafile headers which contain the SCN of the last completed checkpoint are NOT
updated while a file is in hot backup mode.  This lets the recovery process understand
what archive redo log files might be needed to fully recover this file.

To limit the effect of this additional logging, you should ensure you only place one
tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon
as you have backed it up.  This will reduce the number of blocks that may have to be
logged to the minimum possible.

steps to reset original password

Use below steps to reset original password:

Look at the example:

schema name is : OASTOLTP

SQL> set lines 9999
set pages 9999
set long 90000000SQL> SQL>
SQL>
SQL> select dbms_metadata.get_ddl('USER','OASTOLTP') from dual;

DBMS_METADATA.GET_DDL('USER','OASTOLTP')
--------------------------------------------------------------------------------

CREATE USER "OASTOLTP" IDENTIFIED BY VALUES 'S:46DE8181197D5D6A2BEE2B43B21762
BE9B725B07217DA76100F37D12598D;6B9A3DF7BDFDD198'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP_0"


SQL>

to reset original password you will user above command as

ALTER USER OASTOLTP IDENTIFIED BY 'you will get encrypted password of OASTOLTP' default tablespace ........

Temporary tablespace usage for each user

COLUMN tablespace FORMAT A20

COLUMN temp_size FORMAT A20

COLUMN sid_serial FORMAT A20

COLUMN username FORMAT A20

COLUMN program FORMAT A40

SET LINESIZE 200



SELECT b.tablespace,

ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,

a.sid||','||a.serial# AS sid_serial,

NVL(a.username, '(oracle)') AS username,

a.program

FROM v$session a,

v$sort_usage b,

v$parameter p

WHERE p.name = 'db_block_size'

AND a.saddr = b.session_addr

ORDER BY b.tablespace, b.blocks;

Oracle RAC: restoring lost OCR file

Shutdown everything oracle instances then run

- rootdelete.sh
- root.sh this will create OCR


Add resource manually.

Silent mode Oracle RAC installation

Install Oracle clusterware and database software using response file as below.
------------------------------------------------------------------------------


1. To create a response file, start the OUI with the following command and perform an installation as normal.

./runInstaller -record -destinationFile /tmp/10gR2.rsp

2. A silent installation is initiated using the following command


./runInstaller -silent -responseFile /tmp/10gR2.rsp



Database creation using silent mode.
------------------------------------


dbca -createDatabase -silent -templateName General_Purpose.dbc -gdbName dtrac -sysPassword vcs -systemPassword vcs -emConfiguration NONE -disableSecurityConfiguration ALL -datafileDestination /oradata -storageType CFS -nodelist node1,node2


You could get template information from:

$ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc

How can the ASM instance start when the spfile is located on a diskgroup

SQL> show parameter pfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/asm/asmparameterfile/registry.253.729008643

The location of the initialization parameter file specified Grid Plug and Play.
However in the GPnP file, the OLR (Oracle Local Registry) and the OCR (Oracle Cluster Registry) they have the entries to identify the DISCOVERY String and the spfile location.
So ASM can startup but will then access the SPFILE on the ASM diskgroup.

At startup, the Oracle ASM instance attempts to mount the following disk groups:
* Disk groups specified in the ASM_DISKGROUPS initialization parameter
* Disk group used by Cluster Synchronization Services (CSS) for voting files
* Disk groups used by Oracle Clusterware for Oracle Cluster Registry (OCR)
* Disk group used by the Oracle ASM instance to store the ASM server parameter file (SPFILE)

Monitor user session.

set linesize 220
col first_load_time format a20
col client_info format a20
col username format a20
col last_load_time format a20
col sql_text format a30
select a.sid,a.serial#,a.client_info,a.username,a.logon_time,a.status,b.sql_text,a.last_call_et,b.first_load_time,b.last_load_time
from v$session a ,v$sql b
where a.sql_id=b.sql_id(+)
and a.sid='&sid'

OS Watcher (OSW

1. OS Watcher (OSW) is a collection of UNIX shell scripts intended to collect and archive operating system and network metrics to aid support in diagnosing performance issues.
   
   It collected statastics for following commnad
   ps
   top
   mpstat
   iostat
   netstat
   traceroute
   vmstat
   
   Steps to use it.
   1. Download it from oracle metalink 301137.1
   2. How to install it-
   tar -xvf osw212.tar
   3. How to start it
   ./startOSW.sh 60 1
   where
   ARG1 = snapshot interval in seconds.
   ARG2 = the number of hours of archive data to store.
   4. More details given
   https://metalink2.oracle.com/metalink/plsql/docs/OSW_UG.htm

Get DDL command from SQL query

select dbms_metadata.get_ddl('TABLE','table name') FROM DUAL;

e.g:

select dbms_metadata.get_ddl('TABLE','ORDR') FROM DUAL;

View Free space from temporary tablespace

select substr(a.tablespace_name,1,20) "TS_name", substr(a.file_name,1,35) "File_Name",
round(sum(a.bytes)/1024/1024,2) "Size_MB",sum(NVL(b.bytes,0)) "FreeSpace" from dba_TEMP_files a,
(select substr(tablespace_name,1,20) tablespace_name,file_id, round(sum(bytes_used)/1024/1024,2) used_sp,
round(sum(blocks_free * 8192)/1024/1024,2) bytes from v$temp_space_header group by substr(tablespace_name,1,20),file_id ) b where
a.tablespace_name=b.tablespace_name(+) and a.file_id=b.file_id(+) group by
substr(a.tablespace_name,1,20), substr(a.file_name,1,35)
/

View database size from sql

COLUMN "Total Mb" FORMAT 999,999,999.0
COLUMN "Redo Mb" FORMAT 999,999,999.0
COLUMN "Temp Mb" FORMAT 999,999,999.0
COLUMN "Data Mb" FORMAT 999,999,999.0

Prompt
Prompt "Database Size"

select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;

Migrate OCR and Vote disk on ASM storage

Storing the OCR and vote disk inside ASM eliminates the need for third party cluster and volume managers and also helps eliminate the complexity of managing separate disk partitions for the OCR in oracle clusterware installations

Steps to migrate OCR and Vote disk on ASM storage:

1. Create ASM diskgroup using ASMCA


2. Voting disk on raw before migration

(root@mysystem) /oracle/product/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 63b1d7539d25ef7affff433cbdc40f28 (/dev/vx/rdsk/ocrvotedg/votevol) []
Located 1 voting disk(s).


3. Relpacing votedisk from raw to ASM
(root@mysystem) /oracle/product/grid/bin/crsctl replace votedisk +ocrdg

4. Vote disk on ASM after migration
===================================
(root@mysystem) /oracle/product/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 96c7f0f903174fd1bf15432ad5c6dee6 (/dev/vx/rdsk/asmdg/crsvol1) [OCRDG]
2. ONLINE 14e557f987874f36bf1f116c8a51ba6c (/dev/vx/rdsk/asmdg/crsvol2) [OCRDG]
3. ONLINE 0b1961148b674f00bf64b21ea4e6a126 (/dev/vx/rdsk/asmdg/crsvol3) [OCRDG]
Located 3 voting disk(s).


5. View existing OCR configuration before Migration
(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 302956
Used space (kbytes) : 5820
Available space (kbytes) : 297136
ID : 1087500263
Device/File Name : /dev/vx/rdsk/ocrvotedg/ocrvol
Cluster registry integrity check succeeded

6. ADD new OCR by providing asm diskgroup
( root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrconfig -add +ocrdg

7. OCRCHECK after adding ASMDG for OCR
=========================================
(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 302956
Used space (kbytes) : 5820
Available space (kbytes) : 297136
ID : 1087500263
Device/File Name : /dev/vx/rdsk/ocrvotedg/ocrvol
Device/File integrity check succeeded
Device/File Name : +ocrdg
Cluster registry integrity check succeeded

Logical corruption check succeeded

8. DELETING Old OCR

(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrconfig -delete /dev/vx/rdsk/ocrvotedg/ocrvol


9. OCRCHECK after deleting old OCR
=====================================

(root@mysystem)[/] /oracle/11.2.0/gridhome/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 302956
Used space (kbytes) : 5820
Available space (kbytes) : 297136
ID : 1087500263
Device/File Name : +ocrdg
Device/File integrity check succeeded
Cluster registry integrity check succeeded

Migrate Oracled database from Admin managed to Policy Managed

Two Management Styles for Oracle RAC. Policy managed database introduced in oracle 11gr2


Steps to migrate Oracled database from Admin managed to Policy Managed

1. Add server pool
srvctl add srvpool -g oradbsrv -l 4 -u 4 -i 999 -n slial01,slial02,slial03,slial04

2. Check configuration of serverpool
srvctl config srvpool

3. Modify database with new serverpool created in step1
srvctl modify database -d oradb -g oastsrv

4. Check configuration of serverpool
srvctl config database -d oradb -a

5. Check status base of database and name of instances since its will changes after restarting instances
srvctl status database -d oradb

6. Stop and start DB
srvctl stop database -d oradb
srvctl start database -d oradb

5. Check status base of database with new name of instances
srvctl status database -d oradb

Note: New instance name will be diffrent from instance name before migration.