Recommended Oracle Database Startup/Shutdown Steps
- Shut down the database and then shut down SQL*Net.
- Delete or archive old trace files and logs - each of the background processes creates a trace file which is stored in the directory identified by the BACKGROUND_DUMP_DEST parameter in INIT.ORA and the users' trace files are stored in the directory identified by the USER_DUMP_DEST parameter in INIT.ORA. You should regularly compress, back up, and remove trace files from your directories. SQL*Net also creates log files - in v2 it's called listener.log and use the command 'lsnrctl status' to determine its location.
- Rename the alert log - stored in the directory identified by the BACKGROUND_DUMP_DEST parameter in INIT.ORA - keep the alert log to a small size by renaming it periodically.
- Restart the database.
- Generate a 'CREATE CONTROLFILE' command - if the database becomes damaged you have to rebuild your control files. The command's 2 uses are to build a control file from scratch and to alter database level parameters such as MAXDATAFILES. The ideal time to run it is after a physical file backup and during a shutdown/startup cycle right after startup. Example:
alter database backup controlfile to trace;
The file will be called <something>.trc and will be put in the directory identified by the USER_DUMP_DEST parameter in INIT.ORA - then rename it to something more meaningful.
- Pin packages and procedures - to improve the ability to keep large PL/SQL objects pinned in the library cache you should load them into the SGA as soon as the database is opened. To load a package in memory, you can reference a dummy procedure defined in the package or you can recompile the package. You can pin a cursor by executing its SL statement. Once the object has been references you can execute the DBMS_SHARED_POOL.KEEP procedure to pin the object. Example:
alter package APPOWNER.ADD_CLIENT compile;
execute DBMS_SHARED_POOL.KEEP('APPOWNER.ADD_CLIENT', 'P');
The P is for procedures and C is for cursors. The core set of packages you should pin include the SYS-owned packages STANDARD, DBMS_SQL, DBMS_UTILITY, and DIUTIL. To determine which of your packages to pin query DBA_OBJECT_SIZE - the largest should be pinned first. Example:
select Owner, Name, Type, Source_Size+Code_Size+Parsed_Size+Error_Size Total_Bytes
from DBA_OBJECT_SIZE
where Type = 'PACKAGE BODY'
order by 4 desc;
- Create an owner-to-object location map - during recovery, maintenance, and tuning operations it is useful to know which users own objects in which tablepsaces. Example:
break on Tablespace_Name Skip 1 on Owner
select Tablespace_Name, Owner, Segment_Name, Segment_Type
from DBA_SEGMENTS order by Tablespace_Name, Owner, Segment_Name;
To see the map by owner do:
break on Owner Skip 1 on Tablespace_Name
select Owner, Tablespace_Name, Segment_Name, Segment_Type
from DBA_SEGMENTS order by Owner, Tablespace_Name, Segment_Name;
If you want you can also create an owner-to-datafile location map:
break on Owner on Segment_Name
select DBA_EXTENTS.Owner, DBA_EXTENTS.Segment_Name, DBA_DATA_FILES.Tablespace_Name, DBA_DATA_FILES.File_Name, SUM(DBA_EXTENTS.Bytes) Bytes
from DBA_EXTENTS, DBA_DATA_FILES
where DBA_EXTENTS.File_ID = DBA_DATA_FILES.File_ID
group by DBA_EXTENTS.Owner, DBA_EXTENTS.Segment_Name, DBA_DATA_FILES.Tablespace_Name, DBA_DATA_FILES.File_Name;
- Recompute statistics - example:
analyze table ORDERS compute statistics;
This command can create very large temporary segments during its processing. You can substitute the 'compute' keyword with 'estimate' if you don't need exact results. The best time to run the command is when no one else is using the database. You can analyze an entire schema by:
execute DBMS_UTILITY.ANALYZE_SCHEMA('KGM','COMPUTE');
- Shrink rollback segments that have extended past optimal - set in the OPTSIZE column in V$ROLLSTAT. A rollback segment will stay overextended beyond its optimal size until a second transaction forces it to extend past its optimal size. You can use the SHRINK option of the ALTER ROLLBACK SEGMENT command to shrink it to any size - if you don't specify a size it goes back to its optimal size.
