Debug CRS not coming up on BOOT

# su - <crs-user> -c "<CRS_HOME>/bin/crsctl check boot"
# echo $?

i.e.

# su - crsuser -c "/ocw/crs/bin/crsctl check boot"
# echo $?
0

The Block Change Tracking File

When using Oracle block change tracking we see this procedure.  As data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory.

When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file.

The block change tracking file is maintained in the directory defined by the db_create_file_dest parameter, if it is configured. You can also define the location of the block change tracking file via the using file clause of the alter database enable block change tracking command. You can rename the block change tracking file with the alter database rename file command, just as you would with any normal database file. Here is an example of turning on block change tracking, using the using file clause to define where the block change tracking file should be:

Alter database enable block change tacking
using file '/u01/oracle/RMAN/blocktrack/my_db_tracking.chg';
The block change tracking file size depends on svereal considerations, including the number of enabled redo threads, the size of the database, and the number of RMAN backups for which change data needs to be stored. The formula looks like this:
R=((T * 2)+ B) * (S/250000)
where:
R = The size of the block change tracking file, in bytes

T = The number of enabled redo threads

S = The size of the database, in bytes

B = The number of incremental backups for which RMAN needs to store change tracking data

• To avoid overhead of allocating space as your database grows, the change tracking file size starts at 10MB, and new space is allocated in 10MB incremenents [sic]. Thus, for any database up to approximately 300GB the file size is no smaller than 10MB, for up to approximately 600GB the file size is no smaller than 20MB, and so on.

• For each datafile, a minimum of 320K of space is allocated in the change tracking file, regardless of the size of the file. Thus, if you have a large number of relatively small datafiles, the change tracking file is larger than for databases with a smaller number of larger datafiles containing the same data.

Restoring previous version of stats

Scenario : Performance of certain queries has deteriorated suddenly after collecting fresh statistics . You want to revert the stats to previous values .

Solution : Use the dbms_stats.restore_stats procedure to revert to an older set of optimizer statistics .

1. Check the retention of stats for your database

SQL > select dbms_stats.get_Stats_history_retention from dual

GET_STATS_HISTORY_RETENTION
--------------------------------------------------
                              31

2. Check how far you can go to restore the stats :

SQL> select dbms_stats.get_stats_history_availability from dual ;

GET_STATS_HISTORY_AVAILABLITY
--------------------------------------------------
17-APR-2016 03:05:26.7180000000 AM

3. exec dbms_stats.restore_schema_stats(ownername=>'SUMIT' , as_of_timestamp = > '15-May-2016 01.30.30.211212121 PM' , no_invalidate=> false ) ;

How to Estimate the Size of Tables and Indexes Before Being Created and Populated in the Database?

Estimate The Size Of Tables

The CREATE_TABLE_COST procedure of the DBMS_SPACE package helps to estimate the size of the table segment ,

This procedure is used in capacity planning to determine the size of the table given various attributes , The CREATE_TABLE_COST procedure has two versions :

• The first version takes the average row size of the table as argument and outputs the table size.
• The second version takes the column information of the table as argument and outputs the table size.

Both Versions also Requires the following input :

•  the expected number of rows
•  pct_free setting for the table
•  the tablespace name where the table would be created.

Version 1
=========
DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN VARCHAR2,
   avg_row_size       IN NUMBER,
   row_count          IN NUMBER,
   pct_free           IN NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

Version 2
=========
DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN VARCHAR2,
   colinfos           IN CREATE_TABLE_COST_COLUMNS,
   row_count          IN NUMBER,
   pct_free           IN NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

The output of the procedure contains used_bytes and alloc_bytes :

•  The used_bytes : represent the actual bytes used by the data. This includes the overhead due to the block metadata, pctfree etc.
•  The alloc_bytes : represent the size of the table segment when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.

Example for using Version 1:

This methode requires creating the table first with some sample data to calculate the average row size of the table ,

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (9999999,'aaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaa',sysdate);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');

select AVG_ROW_LEN from user_tables where TABLE_NAME='TEST';

AVG_ROW_LEN
-----------
         68

Drop table test;

set serveroutput on 

DECLARE 
 ub NUMBER; 
 ab NUMBER; 
BEGIN 
  DBMS_SPACE.CREATE_TABLE_COST('USERS',68,100000,10,ub,ab); 
  DBMS_OUTPUT.PUT_LINE('Used Bytes      = ' || TO_CHAR(ub)); 
  DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab)); 
END; 
/ 

Used Bytes      =  8036352
Allocated Bytes =  8388608

which is around 8 MB , now lets create and populate the actual table.

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

BEGIN
  FOR i IN 1..100000 LOOP
  INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
  END LOOP;
 END;
/

select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';

     BYTES SEGMENT_TYPE
---------- ------------------
  9437184 TABLE

which is around 9 MB

Example for using Version 2:

This example estimate a Table size with one column NUMBER (10) , two columns VARCHAR2 (30) and one column date , with pct_free=10 and row_count=100000 :

set serveroutput on 

DECLARE 
 ub NUMBER; 
 ab NUMBER; 
 cl sys.create_table_cost_columns; 
BEGIN 
  cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10), 
        sys.create_table_cost_colinfo('CHAR',30), 
        sys.create_table_cost_colinfo('CHAR',30), 
        sys.create_table_cost_colinfo('DATE',NULL)); 

  DBMS_SPACE.CREATE_TABLE_COST('USERS',cl,100000,10,ub,ab); 

  DBMS_OUTPUT.PUT_LINE('Used Bytes      = ' || TO_CHAR(ub)); 
  DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab)); 
END; 
/ 

Used Bytes      = 9314304
Allocated Bytes = 9437184

which is around 9 MB , now lets create and populate the actual table.  

Note : we changed VARCHAR2 to CHAR to get the maximum possible estimation .

  

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

BEGIN
  FOR i IN 1..100000 LOOP
  INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
  END LOOP;
 END;
/

select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';

     BYTES SEGMENT_TYPE
---------- ------------------
  9437184 TABLE

which is around 9 MB

You may notice that version 2 of The CREATE_TABLE_COST procedure is more accurate than version 1 , which is expected because version 2 estimates the size based on the table structure which includes the column data types and length , plus the storage attributes .

Estimate The Size Of Indexes

The CREATE_INDEX_COST Procedure of the DBMS_SPACE package helps to estimate the size of creating an index on an existing table.

The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.

Usage Notes :

•     The table on which the index is created must already exist.
•     The computation of the index size depends on statistics gathered on the segment.
•     It is imperative that the table must have been analyzed recently.
•     In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors.

The output of the procedure contains used_bytes and alloc_bytes :

•  The used_bytes : how much space is for the index data
•  The alloc_bytes : how much space is allocated within the tablespace for the index segment.

Estimate Index Size example :

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

BEGIN
  FOR i IN 1..100000 LOOP
  INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
  END LOOP;
 END;
/

exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');

set serveroutput on 

declare
   l_used_bytes number;
   l_alloc_bytes number;
begin
   dbms_space.create_index_cost (
      ddl => 'create index test_indx on test (a,b) tablespace users',
      used_bytes => l_used_bytes,
      alloc_bytes => l_alloc_bytes
   );
   dbms_output.put_line ('Used Bytes      = '||l_used_bytes);
   dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/   

Used Bytes      = 3800000
Allocated Bytes = 6291456

which is around 6 MB , now lets create the actual index.

create index test_indx on test (a,b) tablespace users

select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST_INDX';

     BYTES SEGMENT_TYPE
---------- ------------------
   6291456 INDEX

which is around 6 MB 

 Reference oracle support (Doc ID 1585326.1)