HOW TO START A SINGLE INSTANCE OR ALL NODES OF A RAC DATABASE ?
DEFINITION: IN RAC (REAL APPLICATION CLUSTERS), INSTEAD OF THE USUAL SETUP WITH ONE INSTANCE AND ONE DATABASE, WE HAVE MULTIPLE INSTANCES CONNECTING TO A SINGLE DATABASE.
USAGE: LOAD BALANCING, HIGH AVAILABILITY
ASSUMPTION : ASM AND CRS SERVICES ARE ALREADY UP AND RUNNING
1). STARTING ONLY ONE INSTANCE OF A RAC DATABASE
SCENARIO : ASM and CRS SERVICES ARE UP, DATABASE AND INSTANCES ARE DOWN
su - grid (or) su - root
cd $GRID_HOME/bin
./crsctl check crs
srvctl start instance -d db_name -i instance_name
set linesize 189 pages 2000
col name for a15
col instance_name for a15
col host_name for a15
select name, instance_name, host_name, db_unique_name, database_role from v$database, gv$instance;
2). STARTING ALL INSTANCES OF A RAC DATABASE
SCENARIO : CRS SERVICES ARE UP, DATABASE AND INSTANCES ARE DOWN
srvctl start database -d db_name
ps -ef | grep pmon
ACCEPT tbs_name PROMPT 'Enter the Tablespace Name: '
ReplyDeleteSET colsep |
SET linesize 100
SET pages 100
SET trimspool ON
SET numwidth 14
COL name FORMAT a25
COL owner FORMAT a15
COL "Used (GB)" FORMAT a15
COL "Free (GB)" FORMAT a15
COL "(Used) %" FORMAT a15
COL "Size (GB)" FORMAT a15
SELECT d.status "Status",
d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 /1024,'99999999.99') "Used (GB)",
TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "(Used) %"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')
AND d.tablespace_name = UPPER('&tbs_name')
UNION ALL
SELECT d.status "Status",
d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),'99,999,990.90') "Size (GB)",
TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,'99999999.99') "Used (GB)",
TO_CHAR(NVL((a.bytes - NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),'99,999,990.90') "Free (GB)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "(Used) %"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes_cached) bytes FROM v$temp_extent_pool GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.contents LIKE 'TEMPORARY'
AND d.tablespace_name = UPPER('&tbs_name');
SELECT
ReplyDeleteTO_CHAR(snap.begin_interval_time, 'YYYY-MM') AS month,
df.tablespace_name,
ROUND(AVG((tsu.tablespace_size * df.block_size)/1024/1024), 2) AS avg_size_mb
FROM
dba_hist_tbspc_space_usage tsu
JOIN
dba_hist_snapshot snap ON tsu.snap_id = snap.snap_id AND tsu.dbid = snap.dbid AND tsu.instance_number = snap.instance_number
JOIN
dba_tablespaces df ON tsu.tablespace_id = df.tablespace_id
JOIN
dba_tablespace_groups g ON df.tablespace_name = g.tablespace_name (+)
JOIN
dba_data_files f ON df.tablespace_name = f.tablespace_name
WHERE
snap.begin_interval_time >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3)
GROUP BY
TO_CHAR(snap.begin_interval_time, 'YYYY-MM'), df.tablespace_name
ORDER BY
month, df.tablespace_name;