Appendix B: Database Scripts For Validations

Use the following scripts to validate your database:

Script

Description

SGA (Memory) Validation

Requires 8 GB for larger systems.

Script:

SQL> SELECT * FROM v$sgainfo;

SQL> show sga

Check archive log mode

For ERDB, the database should be in non-archive mode.

Script:

SQL> SELECT log_mode FROM v$database;

Processes Init Parameter

Requires a minimum of 300.

Script:

SQL> show parameter process

Temp Tablespace Validation

Requires a minimum of two files with file size set at the OS maximum, typically 32 GB.

Larger systems should have three files for the temp tablespace.

Script:

SQL> select TABLESPACE_NAME, FILE_ID, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

UNDO Tablespace Validation

Set the datafile to be autoextensible.

Set the maxbytes to the OS maximum, typically 32 GB.

Requires a minimum of two files; three to five files for larger systems.

Script:

SQL> select s.tablespace_name, f.file_name, f.maxbytes, f.autoextensible from dba_data_files f inner join dba_tablespaces s on s.tablespace_name = f.tablespace_name where s.tablespace_name like '%UNDO%';

Tablespaces for Star

Set the datafile to be autoextensible.

Set the maxbytes to the OS maximum, typically 32 GB.

Requires a minimum of two files; three to five files for larger systems.

Script:

SQL> select s.tablespace_name, f.file_name, f.maxbytes, f.autoextensible from dba_data_files f inner join dba_tablespaces s on s.tablespace_name = f.tablespace_name where s.tablespace_name like '%STAR%';


Last Published Tuesday, December 20, 2022