Cheng's Oracle

RMAN commands

2008-05-17T10:31:00.000-07:00

To enable control file autobackup
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

To change the default format for autobackup file name
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO '/u1/oradata/cf_ORCL_auto_%F';

where %F is mandatory and translates into c-IIIIIIIIII-YYYYMMDD-QQ being:

IIIIIIII the DBID
YYYYMMDD timestamp of the backup
QQ a sequence that starts with 00 and ends with FF

Use the SHOW command to list current settings
RMAN> SHOW CONTROLFILE AUTOBACKUP FORMAT;
RMAN> SHOW EXCLUDE;
RMAN> SHOW ALL;

Use the CLEAR command to reset any persistent settings to its default value
RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;
RMAN> CONFIGURE MAXSETSIZE CLEAR;
RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;

Configure the default device type for automatic channel allocation
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;

or manually allocate a channel

RMAN> RUN {
2> ALLOCATE CHANNEL c1 DEVICE TYPE disk;
3> BACKUP DATAFILE '/u0/oradata/user01.dbf';
4> }

Configure parallelism
RMAN> CONFIGURE DEVICE TYPE disk PARALLELISM 3;

Specify the maximum backup piece size
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;

Format the name of the generated backup files
RMAN> RUN {
2> ALLOCATE CHANNEL d1 DEVICE TYPE disk
3> FORMAT '/disk1/backups/%U';
4> BACKUP DATABASE PLUS ARCHIVELOG; }

Some backup options (more on Administration Workshop II 3-9)
RMAN> BACKUP device type disk tag '%TAG' database include current controlfile;
RMAN> BACKUP database plus archivelog;
RMAN> BACKUP datafile '/u0/oradata/user01.dbf';
RMAN> BACKUP AS BACKUPSET TABLESPACE hr_data; (AS BACKUPSET can be omitted if RMAN is configured in mode BACKUPSET)

After a online backup, remember to archive current redo logs using:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
or all archive logs:
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
or a specific redo group:
SQL> ALTER SYSTEM ARCHIVE LOG GROUP n;

More on backups at http://www.stanford.edu/dept/itss/docs/oracle/9i/server.920/a96519/backup.htm

See backup constraints at Administration Workshop II 3-10
See parallel backup commands at Administration Workshop II 3-11

To create a clone database or a standby database from backups of the target database
RMAN> DUPLICATE database to auxiliaryDb;

To flashback a database

RMAN> FLASHBACK DATABASE
2> TO TIME = TO_DATE
3> ('06/25/03 12:00:00','MM/DD/YY HH:MI:SS');

after you have performed flashback database you should remember to reset logs:

SQL> ALTER DATABASE RESETLOGS;

To restore/recover
RMAN> RESTORE/RECOVER database;
RMAN> RESTORE/RECOVER tablespace
RMAN> RESTORE/RECOVER datafile

Reporting commands

LIST
REPORT

For catalog maintenance (Administration Workshop II 3-8)

CROSSCHECK
DELETE
CHANGE
LIST

RMAN command-line arguments

2008-05-17T10:19:00.000-07:00

(Administration Workshop II 2-27)
First set the ORACLE_SID env
$ export ORACLE_SID=orcl

Writing RMAN output to a log file
$ rman TARGET sys/oracle LOG=$HOME/oradata/u03/rman.log APPEND

Executing a command file when RMAN is invoked
$ rman TARGET sys/oracle CMDFILE=$HOME/scripts/my_rman_script.rcv

Establishes database connections on RMAN startup (both TARGET and CATALOG DBs)
$ rman TARGET sys/oracle@orcl CATALOG rman/rman@rcat

Note: NOCATALOG is the default mode (rman will use the control file as rman repository) unless a CATALOG database is specified.

Administration Workshop II. 10-3. Table types

2008-05-16T15:27:00.000-07:00

Ordinary (heap organized table): Data is stored as an unordered collection (heap).
Partitioned table: data is divided into smaller, more manageable pieces.
Index-organized table (IOT): Rows (key and non-key values) are sorted and stored in a B-tree index structure.
Clustered table: related data from more than one table are stored together. In other words, a cluster is a group of tables that share the same data blocks because they share common columns and are often used together.

Administration Workshop II. 4-14. re-creating indexes

2008-05-14T18:01:00.000-07:00

Use options to reduce the time it takes to create the index:

PARALLEL
NOLOGGING

SQL> CREATE INDEX rname_idx ON hr.regions (region_name) PARALLEL 4;

WARNING: if you created the index with PARALLEL option 4, then you'll need to change it back to PARALLEL 1 after the index is created so that it doesn't use 4 CPUs every time it access the index later!

Administration Workshop II. 2-16. Monitoring the Flash Recovery Area evolution with EM

2008-05-13T18:14:00.000-07:00

Go to Enterprise Manager -> Related Links -> All metrics. Click on Recovery Area link then again on the percentage number to see the chart.

Administration Workshop II. 2-5. RMAN Performance Enhancements

2008-05-13T18:02:00.001-07:00

Automatic parallelization of backup, restore, and recovery operations
No generation of extra redo during online database backups
Backups that are restricted to limit the number of reads per file per second to avoid interfering with OLTP work
The use of multiplexing, which can prevent flooding of any one file with reads and writes while still keeping a tape drive streaming

Administration Workshop II. 2-5. Use RMAN to detect block corruption

2008-05-13T17:58:00.000-07:00

You can use RMAN to detect block corruption. The information relating to the block corruption that is detected during backup can be obtained by using the V$BACKUP_CORRUPTION and V$COPY_CORRUPTION dynamic views.

Administration Workshop II. 2-14. Using a Flash Recovery Area with RMAN

2008-05-13T17:39:00.000-07:00

Flash Recovery Area simplifies managing disk space and files related to backup and recovery:

When the flash recovery area is used, RMAN automatically uses Oracle Managed Files (OMF) for its backup files.
Each time RMAN creates a file in the flash recovery area, the Oracle database updates the list of files that are no longer required on disk. When a file needs to be written into the flash recovery area and space is not available for that file, the Oracle database deletes a file that is on the obsolete files list and writes a notification to the alert log.
A warning is issued when the flash recovery area experiences space pressure or is low on free space because there are no files that can be deleted from the flash recovery area. To resolve the problem, you can:

- add disk space
- back up your files to a tertiary device
- delete the files from the flash recovery area using RMAN
- or change the RMAN retention policy.

The flash recovery area is configured by setting the DB_RECOVERY_FILE_DEST initialization parameter. The DB_RECOVERY_FILE_DEST_SIZE parameter specifies its size.

18-12. How to avoid generating REDO entries

2008-05-11T08:57:00.000-07:00

Even though a database is in archive log mode, it's possible to individually disable logging for a particular table. To do so:

Set the NOLOGGING attribute for the table or

Specifying the UNRECOVERABLE Clause in the Control File. Example:

UNRECOVERABLE
LOAD DATA
INFILE 'sample.dat'
INTO TABLE emp
(ename VARCHAR2(10), empno NUMBER(4));

Timestamp manipulation

2008-05-11T08:30:00.000-07:00

Get the timestamp 10 minutes earlier from now:

SELECT SYSTIMESTAMP - INTERVAL '10' minute FROM DUAL;

Get the timestamp 1 year later from the specified timestamp:

SELECT timestamp'2004-02-29 00:00:00' + INTERVAL '1' year(1) FROM dual;

Extract fields from a timestamp:

SELECT
EXTRACT(year FROM current_timestamp) EY,
EXTRACT(month FROM current_timestamp) EM,
EXTRACT(day FROM current_timestamp) ED,
EXTRACT(hour FROM current_timestamp) EH,
EXTRACT(minute FROM current_timestamp) EM,
EXTRACT(second FROM current_timestamp) ES,
EXTRACT(timezone_hour FROM current_timestamp) TH,
EXTRACT(timezone_minute FROM current_timestamp) TM,
EXTRACT(timezone_region FROM current_timestamp) TR,
EXTRACT(timezone_abbr FROM current_timestamp ) TA
FROM dual;

More on: Oracle Timestamp Data Type

17-25. Flashback Versions Query Considerations

2008-05-10T13:07:00.000-07:00

The VERSIONS clause cannot be used to query

External tables
Temporary tables
Fixed tables
Views

The VERSIONS clause cannot span DDL commands
The VERSIONS clause in a SELECT statement cannot produce versions of rows across the DDL statements that change the structure of the corresponding tables. This means that the query stops producing rows after it reaches a time in the past when the table structure was changed.
What if I want to flashback version query after a DDL? is it possible?

Segment shrink operations are filtered out
Certain maintenance operations, such as a segment shrink, may move table rows across blocks. In this case, the version query filters out such phantom versions because the row data remains the same.

17-9. Flashback Database Limitations

2008-05-10T13:00:00.000-07:00

You cannot use Flashback Database if the control file has been restored or re-created.
Data file dropped after Flashback target time cannot be recovered (the dropped data file is added to the control file and marked offline, but it is not flashed back)
Data file that has been shrunk since the Flashback target time cannot be flashed back. Any such data files must be taken offline before performing the flashback operation.

17-7. Flashback Database: Reducing Restore Time

2008-05-05T20:59:00.001-07:00

With Flashback Database, the time to recover a database is now proportional to the number of changes that need to be backed out (and not to the size of the database) because you do not have to restore data files.

The Oracle database periodically logs "before images" of data blocks in the Flashback Database logs. Bock images can be reused to quickly back out the data file changes to any time at which flashback logs are captured just before the desired target time. Then, changes from the redo log files are applied to fill in the gap (????? why applying the redo log?).

16-8. Loss of a Redo Log File

2008-05-05T19:20:00.000-07:00

Database Control does not allow you to clear a log group that has not been archived. Doing so breaks the chain of redo information. If you must clear an unarchived log group, you should immediately take a full backup of the whole database. Failure to do so may result in a loss of data if another failure ocurrs.

15-19. Flash Recovery Area

2008-05-04T17:42:00.001-07:00

The Flash Recovery Area contains:

Archived logs *
Backups
Flashback logs

Oracle recommends that the size of the flash recovery area be at least twice the size of the database so that it can hold one backup and several archived logs.

*If you have configured your archived logs to be written to this location (with the USE_DB_RECOVERY_AREA flag in one of the locations), then it is important to monitor this space to ensure that it des not reach its capacity.

15-16. Backup control file to a trace file

2008-05-04T13:47:00.000-07:00

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

It would be backed up at the location specified at USER_DUMP_DEST initialization parameter with a name sid_ora_pid.trc

15-7. Backup sets vs. image copies

2008-05-03T13:11:00.000-07:00

Data blocks
Image copies: all data blocks
Backup sets: no empty data blocks are copied (saves space). Typically 20% of database space is empty data block.

Granularity
Image copies: only the file we need has to be copied
Backup sets: all backup set must be retrieved even though only one of them is needed

A backup of a database running in NOARCHIVELOG mode must have all three attributes:

offline
full backup (level 0)
whole database

9-6 to 9-11

2008-04-21T20:55:00.000-07:00

13-15. SQL Access Advisor

2008-04-16T14:35:00.000-07:00

This advisor requires that you identify a SQL workload, which is a representative set of SQL statenments that access the schema. You can select you workload from different sources including current and recent SQL activity, a SQL repository, or a user-defined workload such as from a development environment.
The SQL Access Advisor may make recommendations such as creating indexes or materialized views to improve your query performance for the given workload.

13-9. SQL Tuning Advisor

2008-04-16T12:57:00.000-07:00

Oracle's Tuning Advisor calls Automatic Tuning Optimizer (ATO) to perform 4 types of analysis:

Statistics Analysis: The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and makes recommendations to gather relevant statistics.
SQL Profiling (Tune SQL plan): The ATO verifies its own estimates and collects auxiliary information to remove estimation errors. It builds a SQL profile using the auxiliary information and makes a recommendation to create it. When a SQL profile is created, it enables the query optimizer to generate a well-tuned plan.
Access path Analysis: the ATO explores whether a new index can be used to significantly improve access to each table in the query and, when appropriate, makes recommendations to create such indexes.
SQL Structure Analysis: The ATO tries to identify SQL statements that use bad plans and makes relevant suggestions to restructure them. The suggested changes can be syntactic as well as semantic.

It's important to choose the appropriate scope for the tuning task. If you choose the Limited option, then recommendations are based on:

Statistics check
Access path analysis
SQL structure analysis

If you choose comprehensive option, then SQL profile recommendations are generated as well.

12-22. Server-generated alerts

2008-04-13T17:09:00.001-07:00

A few key metrics that can provide early problem notifications are:

Average File Read Time (centiseconds)
Dump Area Used (%)
Response Time (per transaction)
SQL Response Time (%)
Tablespace Used (%)
Wait Time (%)

By default, the following server-generated alerts are enabled:

Tablespace Space Usage (warnings 85%, critical 97%)
Snapshot Too Old
Recovery Area Low on Free Space
Resumable Session Suspended

To set or edit a threshold for your whole database, select Manage Metrics in the Related Links region of the database home page.

There are two kinds of server-generated alerts:

Threshold (stateful) alerts: they are configured by setting a warning and critical threshold values on database metrics. For example: Physical Reads per second, User commits per second, SQL Service Response Time, etc.
Except for the Tablespace Space Usage metric, which is database related, the other metrics are instance related.
Stateful alerts appear in DBA_OUTSTANDING_ALERTS and when cleared, go to DBA_ALERT_HISTORY.
Nonthreshold (stateless) alerts: corresponds to specific database events such as Snapshot Too Old errors, Recovery Area Low On Free Space, and Resumable Session Suspended.
Stateless alerts go directly to the history table. Clearing a stateless alert makes sense only in the Database Control environment because Database Control stores stateless alerts in its own repository.

12-21. The DBMS_ADVISOR Package

2008-04-13T16:54:00.000-07:00

The DBMS_ADVISOR package contains all constants and procedure declarations for all advisor modules. You can use this package to execute tasks via command line.
To execute advisor procedures, you must be granted the ADVISOR privilege. The ADVISOR privilege permits full access to the advisor procedures and views.

Procedures:

CREATE_TASK
DELTE_TASK
EXECUTE_TASK
INTERRUPT_TASK
GET_TASK_REPORT
RESUME_TASK
UPDATE_TASK_ATTRIBUTES
SET-TASK_PARAMETER
MARK_RECOMMENDATION
GET_TASK_SCRIPT

12-8. Using the Optimizer Statistics

2008-04-13T13:30:00.000-07:00

Go to the Enterprise Manager page for managing optimizer statistics by clicking Manage Optimizer Statistics on the Administration tabbed page. Note that GATHER_STATS_JOB should be enabled and for this to work, you should set STATISTICS_LEVEL initialization parameter is set to at least TYPICAL.

From Manage Optimizer Statistcs page, you can perform the following tasks on statistics:

Gather optimizer statistics manually. this action submits the job that GATHER_STATS_JOB automatically does. This should be done in the case where a table's contents have changed so much between automatic gathering jobs that the statistics no longer represent the table accurately. Examples of this are a table that is truncated in the middle of the day and a batch job that runs and adds large amounts of data to a table.
Restore optimizer statistics to a point in the past. The point in time chosen must be within the optimizer statistics retention period, which defaults to 30 days.
Lock optimizer statistics to guarantee that the statistics for certain objects are never overwritten. This is useful if statistics have been calculated for a certain table at a time when well-representative data is present, and you want to always have those statistics. No fluctuation in the table affects the statistics if they are locked.
Unlock optimizer statistics to undo the previously done lock
Delete optimizer statistics to delete statistics.

When to re-organize a table

2008-04-12T17:59:00.000-07:00

Notes from: All about chained rows @ Ask Tom

Before jumping into the topic, we need to first clarify some concepts:

A row that doesn't fit into a block A needs to be "chained" with another block B.
Now, if there was another row in the block A then it needs to be "migrated" to another block when block A fills up.
a Full table scan example is SELECT * FROM t
a query using an index is for example SELECT * FROM t WHERE t.col1 = 1 (it uses the index on col1 to find the row where t.col1 = 1)

Migrated rows and their impact on:

Full table scan: are not affected by migrated rows since it picks up data while reading the disks
Querying migrated rows with index (instead of full table scan): go through the ROWID pointer (that was left behind when a row was migrated) to find the data it needs so it adds 1 to Table Fetch Continue Row count.

Chained rows and their impact on:

Full table scan: since we can't find the data in one place only, we will need an additional read to find all the data
Querying chained rows with index (instead of full table scan): idem.

Conclusion
Chained rows are inevitable so there's not much we can do unless the data model could be changed so that fewer data is stored - but that's a functional problem. On the other hand, if we have lots of migrated rows that each of them would perfectly fit in one datablock, then we could potentially reorganize the table to reduce the additional disk lookup.

12-5. Optimizer Statistics

2008-04-12T16:51:00.000-07:00

Optimizer statistics include table, column, index and system statistics. They provide the optimizer a statistically correct snapshot of data storage and distribution, which the optimizer uses to make decisions on how to access data.

The statistics that are collected include:

Size of the table or index (in database blocks)
SELECT blocks FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
Number of rows
SELECT num_rows FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
Average row size and chain count (tables only)
SELECT avg_row_len, chain_cnt FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
Height and number of deleted leaf rows (indexes only)

Optimizer statistics are collected automatically by the preconfigured GATHER_STATS_JOB, which runs during predefined maintenance windows, once per day.

A large table that experiences 10 percent growth (or reduction) within a 24-hour period is usually considered too volatile for statistics collection once per day to be sufficient. For tables that experience this level of change, Oracle recommends collecting statistics more frequently so it won't exceed 10 percent growth between collection periods.

Statistics can be manually collected by using Enterprise Manager or through the use of the DBMS_STATS package as shown here:

SQL> EXEC dbms_stats.gather_table_stats('HR', 'EMPLOYEES');