Oracle Memory Structures (SGA)
- Shared Pool: stores info shared by multiple sessions. It’s sub-divided into 3 areas:
- Library Cache: fully parsed or compiled SQL statements or PL/SQL blocks (Procedures, Functions, Triggers, Packages and Anonymous PL/SQL blocks)
- Data Dictionary Cache: holds definition of data dictionary objects in memory
- User Global Area: contains session information if using Oracle shared server. See more on Administration Workshop II 8-10, 8-28 and full explaination of UGA, PGA
- Java Pool: used for all session-specific Java code and data within the Java engine. The OracleJVM memory manager allocates all other Java states during run-time execution from the Java pool, including the shared in-memory representation of Java method and class definitions, as well as the Java objects migrated to session space at end-of-call.
- However, Shared Pool is used by the class loader within the JVM. The class loader uses about 8KB of memory per loaded class. The shared pool is also used when compiling Java source code in the database or when using Java resource objects in the database. Shared pool memory is also consumed when you create call specifications and as the system tracks dynamically loaded Java classes at run time. (Administration Workshop II 8-12)
- Redo Log Buffer: The redo entries copied from the user’s memory space contain the information necessary to reconstruct or redo changes made to the database by DML and DDL operations.
LGWRwrites to disk from redo log buffer when:- A user process commits a transaction
- Every three seconds or when the redo log buffer is one-third full or contains at least 1 MB of data
- When a
DBWnprocess writes modified buffers to disk, if the corresponding redo log data has not already been written to disk (Administration Workshop II). Correction taken from Administration Workshop I 14-12:LGWRwrites beforeDBWnwrites.
Administration Workshop II 8-6. Least Recently Used (LRU) list
The least recently used (LRU) list monitors the usage of buffers. The buffers are sorted on the basis of a combination of how recently and how often they have been referenced. Thus, buffers that are most frequently and recently used are found at the most recently used end. Incoming blocks are copied to a buffer from the least recently used end, which is then assigned to the middle of the list, as a starting point. From here, the buffer works its way up or down the list, depending on usage.
Administration Workshop II 6-26. Monitoring Flashback Database
To monitor the ability to meet your retention target:
- View the flash recovery area disk quota
- Determine the current flashback window
- Monitor logging in the Flashback Database logs:
SQL> SELECT * FROM V$FLASHBACK_DATABASE_STAT;
You can use this view to determine rate changes in the flashback data generation.
You can query V$RECOVERY_FILE_DEST to view information regarding the flash recovery area:
SQL> SELECT name, space_limit AS quota, 2> space_used AS used, 3> space_reclaimable AS reclaimable, 4> number_of_files AS files 5> FROM V$RECOVERY_FILE_DEST
Administration Workshop II 6-19. Flashback Database Examples
With RMAN:
RMAN> FLASHBACK DATABASE TO TIME = 2> "TO_DATE('2004-05-27 16:00:00', 3> 'YYYY-MM-DD HH24:MI:SS')"; RMAN> FLASHBACK DATABASE TO SCN=23565; RMAN> FLASHBACK DATABASE 2> TO SEQUENCE=223 THREAD=1;
With SQL*PLUS:
SQL> FLASHBACK DATABASE 2> TO TIMESTAMP(SYSDATE - 1 / 24); SQL> FLASHBACK DATABASE TO SCN 53943; SQL> FLASHBACK DATABASE TO RESTORE POINT b4_load;
Note: The database must be mounted in exclusive mode to issue the FLASHBACK DATABASE command and must be opened with the RESETLOGS option when finished
Performance Tip: Redo log offloading frequency
In a busy production environment, it is important to ensure that the frequency of redo log switches is not more than 5 per hour and the v$log_history view can help.
Read more at Oracle V$LOG_HISTORY article at Burleson Consulting
Backing up SPFILE
To back up an SPFILE, you will first want to convert it to a PFILE (text file format). You can do this with the following syntax.
SQL> create pfile from spfile;
This will create a PFILE named initSID.ora in your $ORACLE_HOME/database (Windows) or $ORACLE_HOME/dbs (Linux/Unix) directory.
To restore the backed up SPFILE, do:
SQL> create spfile from pfile=/path/to/backup.ora
If your database is currently running using the SPFILE, be sure to shut down first so Oracle can replace the file. As your SPFILE is in use the entire time your database is running, you should never overwrite it during normal operations
Administration Workshop II 5-27. Incomplete Recovery Best Practices
1. Before starting incomplete recovery, perform a whole closed database backup, including control files and redo logs (see note below). This is helpful in the following ways:
- It allows you to recover from error: If your recovery fails (for example, you recover past the desired point of recovery), redo logs and control files cannot be used for the next recovery unless there is a backup of these files (that is because we must issue
ALTER DATABASE OPEN RESETLOGSafter every incomplete recovery). - It saves time if the recovery fails: In this situation, you can restore the data files from the new backup, rather than from a previous backup, which needs archives applied.
Note: if a whole backup is not performed, at least archive the current redo log:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT
and back up the control file:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/u01/data/backup.ctl'
2. Also, perform a whole closed backup after a successful recovery. This can save many hours if another recovery is rquired before completion of the next scheduled backup.
3. To prevent error because of using database of incorrect incarnation during recovery, use the %r format option of the log_archive_format database initialization parameter to:
- Automatically incorporate the database resetlogs ID into the archived log file names
- Ensure that unique names are constructed for the archived log files across multiple incarnations of the database
Alert Log location
The location of alert.log can queried using:
SQL> SHOW PARAMETER BACKGROUND_DUMP; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ background_dump_dest string C:\oracle\admin\orcl\bdump
Administration Workshop II 4-10. Dropping a redo log member
From: http://www.adp-gmbh.ch/ora/concepts/redo_log_switch.html
SQL> select sequence# from v$log where status = 'CURRENT'; SEQUENCE# ---------- 523 SQL> alter system switch logfile; System altered. SQL> select sequence# from v$log where status= 'CURRENT'; SEQUENCE# ---------- 524 SQL> alter system checkpoint; System altered. SQL> select sequence# from v$log where status = 'CURRENT'; SEQUENCE# ---------- 524
Handy Backup commands
Configuration
You can enable Block Change Tracking by issuing the following command:
SQL> ALTER DATABASE ENABLE 2> BLOCK CHANGE TRACKING 3> USING FILE '/mydir/rman_change_track.f' 4> REUSE;
Note: USING FILE '/mydir/rman_change_track.f' is not necessary if Oracle Managed Files is used (it will be created at the location specified by the DB_CREATE_FILE_DEST parameter).
