RAC : 11gR2 Clusterware Startup Sequence

 Here is the brief explanation that how the clusterware brings up step by step .

1. When a node of an Oracle Clusterware cluster start/restarts, OHASD is started by platform-specific means. OHASD is the root for bringing up Oracle Clusterware. OHASD has access to the OLR (Oracle Local Registry) stored on the local file system. OLR provides needed data to complete OHASD initialization.

2. OHASD brings up GPNPD and CSSD. CSSD has access to the GPNP Profile stored on the local file system. This profile contains the following vital bootstrap data;

        a. ASM Diskgroup Discovery String

        b. ASM SPFILE location (Diskgroup name)

        c. Name of the ASM Diskgroup containing the Voting Files

3. The Voting Files locations on ASM Disks are accessed by CSSD with well-known pointers in the ASM Disk headers and CSSD is able to complete initialization and start or join an existing cluster.

4. OHASD starts an ASM instance and ASM can now operate with CSSD initialized and operating. The ASM instance uses special code to locate the contents of the ASM SPFILE, assuming it is stored in a Diskgroup.

5. With an ASM instance operating and its Diskgroups mounted, access to Clusterware’s OCR is available to CRSD.

6. OHASD starts CRSD with access to the OCR in an ASM Diskgroup.

7. Clusterware completes initialization and brings up other services under its control.

When Clusterware starts three files are involved.

1. OLR – Is the first file to be read and opened. This file is local and this file contains information regarding where the voting disk is stored
and information to startup the ASM. (e.g ASM DiscoveryString)

2. VOTING DISK – This is the second file to be opened and read, this is dependent on only OLR being accessible.

ASM starts after CSSD or ASM does not start if CSSD is offline (i.e voting file missing)

How are Voting Disks stored in ASM?

Voting disks are placed directly on ASMDISK. Oracle Clusterware will store the votedisk on the disk within a disk group that holds the Voting Files.
Oracle Clusterware does not rely on ASM to access the Voting Files, which means Oracle Clusterware does not need of Diskgroup to read and write on ASMDISK. It is possible to check for existence of voting files on a ASMDISK using the V$ASM_DISK column VOTING_FILE.
So, voting files not depend of Diskgroup to be accessed, does not mean that the diskgroup is not needed, diskgroup and voting file are linked by their settings.

3. OCR – Finally the ASM Instance starts and mount all Diskgroups, then Clusterware Deamon (CRSD) opens and reads the OCR which is stored on Diskgroup.

So, if ASM already started, ASM does not depend on OCR or OLR to be online. ASM depends on CSSD (Votedisk) to be online.

There is a exclusive mode to start ASM without CSSD (but it’s to restore OCR or VOTE purposes)

As per Oracle Documentation

The full description, the really unreadable diagram and/or any updates to this you can find it in MOS Document 1053147.1

Click on the image below to see the full size 

Level 1: OHASD Spawns:

• cssdagent – Agent responsible for spawning CSSD.
• orarootagent – Agent responsible for managing all root owned ohasd resources.
• oraagent – Agent responsible for managing all oracle owned ohasd resources.
• cssdmonitor – Monitors CSSD and node health (along wth the cssdagent).

Level 2: OHASD rootagent spawns:

• CRSD – Primary daemon responsible for managing cluster resources.
• CTSSD – Cluster Time Synchronization Services Daemon
• Diskmon
• ACFS (ASM Cluster File System) Drivers

Level 2: OHASD oraagent spawns:

• MDNSD – Used for DNS lookup
• GIPCD – Used for inter-process and inter-node communication
• GPNPD – Grid Plug & Play Profile Daemon
• EVMD – Event Monitor Daemon
• ASM – Resource for monitoring ASM instances

Level 3: CRSD spawns:

• orarootagent – Agent responsible for managing all root owned crsd resources.
• oraagent – Agent responsible for managing all oracle owned crsd resources.

Level 4: CRSD rootagent spawns:

• Network resource – To monitor the public network
• SCAN VIP(s) – Single Client Access Name Virtual IPs
• Node VIPs – One per node
• ACFS Registery – For mounting ASM Cluster File System
• GNS VIP (optional) – VIP for GNS

Level 4: CRSD oraagent spawns:

• ASM Resouce – ASM Instance(s) resource
• Diskgroup – Used for managing/monitoring ASM diskgroups.
• DB Resource – Used for monitoring and managing the DB and instances
• SCAN Listener – Listener for single client access name, listening on SCAN VIP
• Listener – Node listener listening on the Node VIP
• Services – Used for monitoring and managing services
• ONS – Oracle Notification Service
• eONS – Enhanced Oracle Notification Service
• GSD – For 9i backward compatibility
• GNS (optional) – Grid Naming Service – Performs name resolution

List the GI Processes 

REORG STEPS:

Step 1 :

Create a new tablespace;

CREATE BIGFILE TABLESPACE CDMDM_NEW DATAFILE '+SPARSE' SIZE 20G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

Step 2 :

Move all tables to new tablespace by below dynamic query :

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE ONLINE TABLESPACE CDMDM_NEW compress for Archive high;' from dba_tables where owner='CDMDM' ;

****

Partition tables also need to move to new tablespace.

select 'ALTER TABLE '||a.OWNER||'.'||a.TABLE_NAME||' move PARTITION '||b.PARTITION_NAME||' TABLESPACE CDMDM_NEW compress for Archive high;' from dba_part_tables a, dba_segments b where a.table_name=b.segment_name and a.owner='CDMDM' and b.tablespace_name='CDMDM';

select 'ALTER TABLE '||TABLE_OWNER||'.'||TABLE_NAME||' move PARTITION '||PARTiTION_NAME||' TABLESPACE NONGWDATA1;' from dba_tab_partitions where tablespace_name in ('NONGWDATA_NEW','NONGWDATA');

****
If there Bitmap Indexes presents , 1st need to unusable those indexes. Otherwise it will fail .

select 'alter INDEX '||OWNER||'.'||INDEX_NAME||' UNUSABLE;' from dba_indexes where owner='CDMDM' and index_type like '%BITMAP%' and table_name in ('W_CLAIMELMNT_D','W_EMPLOYEE_D','WC_UNIT_FINANCIAL_F','W_CLAIMELMNT_D_PZ','WC_MM_BILL_HEADER_F','W_EMPLOYEE_D_OLD');

Step 3 :

Move all indexes to new tablespace by below dynamic query :

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE TABLESPACE CDMDM_NEW compress ADVANCED high parallel 20;' from dba_indexes where owner='CDMDM' and tablespace_name='CDMDM' and index_type <> 'LOB';

Step 4 :

** Rebuild all Indexes are in UNUSABLE State :

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD parallel 20;' from dba_indexes where status='UNUSABLE' ;

Convert all indexes to noparallel

select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' noparallel;' from dba_indexes where owner='CDMDM' and degree > 1;

Step 5 :

Compress all LOBS where securefile is YES

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' modify LOB ( '||COLUMN_NAME||' ) ( COMPRESS HIGH );' from dba_lobs where owner='CDMDM' and tablespace_name='CDMDM' and securefile='YES';

Step 6 :

Move all LOBS to new tablespace

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' move LOB ( '||COLUMN_NAME||' ) STORE AS ( tablespace CDMDM_NEW );' from dba_lobs where owner='CDMDM' and tablespace_name='CDMDM';

Move LOB Partitions :

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as (tablespace NONGWDATA1);' FROM DBA_LOB_PARTITIONS WHERE tablespace_name in ('NONGWDATA_NEW','NONGWDATA');

Move SDO LOB segments :

ALTER TABLE CCUSER."CC_USER" MOVE TABLESPACE OP VARRAY "SPATIALPOINTDENORM"."SDO_ELEM_INFO" STORE AS LOB(TABLESPACE LOB_NEW) VARRAY "SPATIALPOINTDENORM"."SDO_ORDINATES" STORE AS LOB(TABLESPACE LOB_NEW) parallel 16;

ALTER TABLE CCUSER."CC_ADDRESS" MOVE TABLESPACE OP VARRAY "SPATIALPOINT"."SDO_ELEM_INFO" STORE AS LOB(TABLESPACE LOB_NEW) VARRAY "SPATIALPOINT"."SDO_ORDINATES" STORE AS LOB(TABLESPACE LOB_NEW) parallel 16;

ALTER TABLE CCUSER."CC_CLAIM" MOVE TABLESPACE OP VARRAY "LOSSLOCATIONSPATIALDENORM"."SDO_ELEM_INFO" STORE AS LOB(TABLESPACE LOB_NEW) VARRAY "LOSSLOCATIONSPATIALDENORM"."SDO_ORDINATES" STORE AS LOB(TABLESPACE LOB_NEW) parallel 16;

Step 7 :

Validation :

select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='CDMDM';

select owner,object_name,object_type from dba_objects where status <> 'VALID' and owner='CDMDM';

select * from dba_segments where tablespace_name='CDMDM';

select * from dba_tables where tablespace_name='CDMDM';

select * from dba_indexes where tablespace_name='CDMDM';

select * from dba_lobs where tablespace_name='CDMDM';

select * from dba_tab_partitions where tablespace_name in ('ODSDATA2','ODS_BASE_NEW');

select * from dba_ind_partitions where tablespace_name in ('ODSDATA2','ODS_BASE_NEW');

select * from dba_lob_partitions where tablespace_name in ('ODSDATA2','ODS_BASE_NEW');

DATAFILE MOVEMENT Dynamic QUERY:

select 'alter database move datafile '''||FILE_NAME||''' to ''+DATAC1'';' from dba_data_files where file_name like '%SPARSE%';

Verify Default attribute tablespace:

select * from dba_part_tables where DEF_TABLESPACE_NAME like '%del%'

SELECT 'alter table ' || OWNER || '.' || TABLE_NAME || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE NONGWDATA1;' from dba_part_tables where DEF_TABLESPACE_NAME ='%del%'

Table Online Redefination :

exec DBMS_REDEFINITION.REDEF_TABLE(uname=>'CDH_GWODS',tname=>'CDH_BATCH_CTRLID',table_part_tablespace=>'CDH');

============================================================

col USERNAME for a30

col TABLESPACE_NAME for a40

SELECT USERNAME, TABLESPACE_NAME, BYTES, MAX_BYTES FROM DBA_TS_QUOTAS ORDER BY USERNAME, TABLESPACE_NAME;

alter database datafile '/u02/oracle/oradata/u7bcs1d7/tools_01.dbf' resize 26g autoextend on next 250m maxsize unlimited;

========================

ALTER TABLE MEDIATION.QRTZ_JOB_DETAILS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.QRTZ_TRIGGERS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.QRTZ_BLOB_TRIGGERS move LOB ( BLOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.QRTZ_CALENDARS move LOB ( CALENDAR ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.REST_TEMPLATE move LOB ( TEMPLATE ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.EVENT_HISTORY_DATA move LOB ( DETAILS ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.NOTIFICATIONS move LOB ( NOTIFICATION ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.NOTIFICATIONHISTORY move LOB ( NOTIFICATION ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.SOAPHEADER move LOB ( HEADER ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.CREATE$JAVA$LOB$TABLE move LOB ( LOB ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPADMIN move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPGWA move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPSCHEDULE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPCONFIG move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPDATAPATH move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPPOLICY move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE PCMS.DMPTEMPLATE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.TASKPAYLOAD move LOB ( TASK ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.JMSCONTEXT move LOB ( CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPSCHEDULE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPTEMPLATE move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPADMIN move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPGWA move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.SOAPHEADER move LOB ( HEADER ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.JMSCONTEXT move LOB ( CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.TOAD_PLAN_TABLE move LOB ( OTHER_XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_BLOB_TRIGGERS move LOB ( BLOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_CALENDARS move LOB ( CALENDAR ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_JOB_DETAILS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.QRTZ_TRIGGERS move LOB ( JOB_DATA ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.XMLRESPONSE move LOB ( RESPONSEXML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.SYS_IMPORT_FULL_01 move LOB ( XML_CLOB ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.SYS_IMPORT_FULL_01 move LOB ( TARGET_XML_CLOB ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.BATCH_STEP_EXECUTION_CONTEXT move LOB ( SERIALIZED_CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.BATCH_JOB_EXECUTION_CONTEXT move LOB ( SERIALIZED_CONTEXT ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.CREATE$JAVA$LOB$TABLE move LOB ( LOB ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPCONFIG move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPDATAPATH move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE BCS.DMPPOLICY move LOB ( XML ) STORE AS ( tablespace DATA );

ALTER TABLE MEDIATION.JMSTEST move LOB ( CONTEXT ) STORE AS ( tablespace DATA );

When moving a LOB column, if the LOB is stored as a SecureFile, and you want to maintain or explicitly specify that the LOB should continue to be stored as a SecureFile, you should include the SECUREFILE keyword in your ALTER TABLE statement.

Checking if a LOB is SecureFile or BasicFile

To check whether a LOB column is stored as SecureFile or BasicFile, you can query the DBA_LOBS view:

sql
SELECT TABLE_NAME, COLUMN_NAME, SECUREFILE
FROM DBA_LOBS
WHERE TABLE_NAME = 'your_table_name' AND COLUMN_NAME = 'your_column_name';

• SECUREFILE: This column will show YES if the LOB is stored as a SecureFile, and NO if it's stored as a BasicFile.

Moving a SecureFile LOB

If the LOB is a SecureFile, and you want to maintain that storage type while moving it, you should use the following syntax:

sql
ALTER TABLE schema_name.table_name
MOVE LOB (column_name)
STORE AS SECUREFILE (TABLESPACE tablespace_name);

Example

Here is how you would move a SecureFile LOB column while ensuring it stays a SecureFile:

sql
ALTER TABLE MEDIATION.QRTZ_JOB_DETAILS 
MOVE LOB (JOB_DATA) 
STORE AS SECUREFILE (TABLESPACE DATA);

If you omit SECUREFILE, the column might revert to the BasicFile format depending on your Oracle database's default LOB storage settings.

Best Practice

• For SecureFile LOBs: Always specify STORE AS SECUREFILE to ensure the LOB remains in the SecureFile format after the move.
• For BasicFile LOBs: You don't need to specify anything special; just using the STORE AS (TABLESPACE tablespace_name) syntax is sufficient.

If you want to check the existing storage type before moving, always query DBA_LOBS to avoid inadvertently changing the LOB type.

SELECT OWNER, TABLE_NAME, COLUMN_NAME 

FROM ALL_TAB_COLUMNS 

WHERE DATA_TYPE = 'SDO_GEOMETRY';

DATA_TYPE

--------------------------------------------------------------------------------

DATE

CLOB

CHAR

TIMESTAMP(6)

BLOB

RAW

NUMBER

UNDEFINED

VARCHAR2

LONG

TIMESTAMP(3)

NVARCHAR2