My notes for teaching the OCA 11g DBAI class, for students preparing to take the OCA exam.
Database Writer writes when:
- No free buffers
- Too many dirty buffers
- 3 second timout
Log Writer writes when:
- session COMMIT
- buffer 1/3 full
- DBWR is about to write
UNDO is used for:
- Reversing incomplete transactions
- Read consistency (temporary)
- Transaction isolation
- Flashback (9i temporary)
Run out of space ORA-30036 “unable to extend segment in undo tablespace” Lets the query succeed
Fails on consistent read- undot for CR has been overwritten ORA-01555 “snapshot too old”. This means the transaction will succeed.
- Statement failure
- User process failure- Network(listener, network card, route), User error
- Media Failure
- Instance Failure
- Loss of control file
- Loss of online redo log
- Loss of datafile in NOARCHIVELOG mode
- Loss of datafile in ARCHIVELOG mode
alter system checkpoint
alter system switch logfile
How to Set Archive Log Mode
- ARCHIVE LOG LIST
- ALTER DATABASE ARCHIVELOG
- ALTER SYSTEM ARCHIVE LOG CURRENT, or
- ALTER SYSTEM SWITCH LOGFILE
- SELECT name, is_recovery_dest_file FROM v$archive_log
Commands to Perform a Hot Backup
- ALTER TABLESPACE <name> BEGIN BACKUP
- LIST BACKUP OF DATABASE
- LIST BACKUP OF ARCHIVE ALL;
- REPORT NEED BACKUP;
All the RMAN command are listed at the bottom.
- DELETE EXPIRED
- DELETE OBSOLETE
Copy the file(s) to the new location.
RMAN> COPY DATAFILE 8 TO '/u01/app/oracle/oradata/users.dbf';
Turn the tablespace to offline. We could have turned the tablespace offline before the copy, removing the need for a recovery, but the tablespace would have been offline longer using that method.
RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE';
Switch to the new datafile copy(s) and recover the tablespace.
RMAN> SWITCH DATAFILE 8 TO COPY; RMAN> RECOVER TABLESPACE users;
Turn the tablespace online again.
RMAN> SQL 'ALTER TABLESPACE users ONLINE';
Remove the old datafile(s).
RMAN> HOST 'rm /u01/app/oracle/product/18.104.22.168/dbs/users.dbf';
Value Based Triggers
FGA_DBMS_FGA (all rows) These two views combine to form DBA_COMMON_AUDIT_TRAIL
ALERTS – Threshold Alerts – MMON – stateful
DBA_ALERT_HISTORY –> “snapshot too old is stateless”
INDEXES DBA_INDEXES, INDEX_STATS
STATISTICS_LEVEL = TYPICAL, ALL, BASIC
To move or rename a datafile do the following.
- Shutdown the database.
- Rename the physical file on the OS.
- Start the database in mount mode.
- Issue the
ALTER DATABASE RENAME FILEcommand to rename the file within the Oracle dictionary.
- Open the database.
To move or rename a controlfile do the following.
- Alter the
control_filesparameter using the
- Shutdown the database.
- Rename the physical file on the OS.
- Start the database.
|“@”||Run a command file.|
|“@@”||Run a command file in the same directory as another command file that is currently running. The
|“ALLOCATE CHANNEL”||Establish a channel, which is a connection between RMAN and a database instance.|
|“ALLOCATE CHANNEL FOR MAINTENANCE”||Allocate a channel in preparation for issuing maintenance commands such as
|“allocOperandList”||A subclause that specifies channel control options such as
|“ALTER DATABASE”||Mount or open a database.|
|“archivelogRecordSpecifier”||Specify a range of archived redo logs files.|
|“BACKUP”||Back up database files, copies of database files, archived logs, or backup sets.|
|“BLOCKRECOVER”||Recover an individual data block or set of data blocks within one or more datafiles.|
|“CATALOG”||Add information about a datafile copy, archived redo log, or control file copy to the repository.|
|“CHANGE”||Mark a backup piece, image copy, or archived redo log as having the status
|“completedTimeSpec”||Specify a time range during which the backup or copy completed.|
|“CONFIGURE”||Configure persistent RMAN settings. These settings apply to all RMAN sessions until explicitly changed or disabled.|
|“CONNECT”||Establish a connection between RMAN and a target, auxiliary, or recovery catalog database.|
|“connectStringSpec”||Specify the username, password, and net service name for connecting to a target, recovery catalog, or auxiliary database. The connection is necessary to authenticate the user and identify the database.|
|“CONVERT”||Converts datafile formats for transporting tablespaces across platforms.|
|“CREATE CATALOG”||Create the schema for the recovery catalog.|
|“CREATE SCRIPT”||Create a stored script and store it in the recovery catalog.|
|“CROSSCHECK”||Determine whether files managed by RMAN, such as archived logs, datafile copies, and backup pieces, still exist on disk or tape.|
|“datafileSpec”||Specify a datafile by filename or absolute file number.|
|“DELETE”||Delete backups and copies, remove references to them from the recovery catalog, and update their control file records to status
|“DELETE SCRIPT”||Delete a stored script from the recovery catalog.|
|“deviceSpecifier”||Specify the type of storage device for a backup or copy.|
|“DROP CATALOG”||Remove the schema from the recovery catalog.|
|“DROP DATABASE”||Deletes the target database from disk and unregisters it.|
|“DUPLICATE”||Use backups of the target database to create a duplicate database that you can use for testing purposes or to create a standby database.|
|“EXECUTE SCRIPT”||Run an RMAN stored script.|
|“EXIT”||Quit the RMAN executable.|
|“fileNameConversionSpec”||Specify patterns to transform source to target filenames during BACKUP AS COPY, CONVERT and DUPLICATE.|
|“FLASHBACK”||Returns the database to its state at a previous time or SCN.|
|“formatSpec”||Specify a filename format for a backup or copy.|
|“HOST”||Invoke an operating system command-line subshell from within RMAN or run a specific operating system command.|
|“keepOption”||Specify that a backup or copy should or should not be exempt from the current retention policy.|
|“LIST”||Produce a detailed listing of backup sets or copies.|
|“listObjList”||A subclause used to specify which items will be displayed by the
|“maintQualifier”||A subclause used to specify additional options for maintenance commands such as
|“maintSpec”||A subclause used to specify the files operated on by maintenance commands such as
|“obsOperandList”||A subclause used to determine which backups and copies are obsolete.|
|“PRINT SCRIPT”||Display a stored script.|
|“QUIT”||Exit the RMAN executable.|
|“recordSpec”||A subclause used to specify which objects the maintenance commands should operate on.|
|“RECOVER”||Apply redo logs and incremental backups to datafiles restored from backup or datafile copies, in order to update them to a specified time.|
|“REGISTER”||Register the target database in the recovery catalog.|
|“RELEASE CHANNEL”||Release a channel that was allocated with an
|“releaseForMaint”||Release a channel allocated with an
|“REPLACE SCRIPT”||Replace an existing script stored in the recovery catalog. If the script does not exist, then
|“REPORT”||Perform detailed analyses of the content of the recovery catalog.|
|“RESET DATABASE”||Inform RMAN that the SQL statement
|“RESTORE”||Restore files from backup sets or from disk copies to the default or a new location.|
|“RESYNC”||Perform a full resynchronization, which creates a snapshot control file and then copies any new or changed information from that snapshot control file to the recovery catalog.|
|“RUN”||Execute a sequence of one or more RMAN commands, which are one or more statements executed within the braces of
|“SEND”||Send a vendor-specific quoted string to one or more specific channels.|
|“SET”||Sets the value of various attributes that affect RMAN behavior for the duration of a RUN block or a session.|
|“SHOW”||Displays the current
|“SHUTDOWN”||Shut down the target database. This command is equivalent to the SQL*Plus
|“SPOOL”||Write RMAN output to a log file.|
|“SQL”||Execute a SQL statement from within Recovery Manager.|
|“STARTUP”||Start up the target database. This command is equivalent to the SQL*Plus
|“SWITCH”||Specify that a datafile copy is now the current datafile, that is, the datafile pointed to by the control file. This command is equivalent to the SQL statement
|“UNREGISTER DATABASE”||Unregisters a database from the recovery catalog.|
|“untilClause”||A subclause specifying an upper limit by time, SCN, or log sequence number. This clause is usually used to specify the desired point in time for an incomplete recovery.|
|“UPGRADE CATALOG”||Upgrade the recovery catalog schema from an older version to the version required by the RMAN executable.|
|“VALIDATE”||Examine a backup set and report whether its data is intact. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksums to verify that the contents can be successfully restored.|