In order to run dml or ddl statements into the database, I pack them into a SQL*Plus template script that keeps track of its execution and let everything very well documented.
SET Echo Off
CONNECT / AS SysDBA
COLUMN "NAME" NEW_VALUE DataBase
COLUMN "Now" NEW_VALUE Now
SELECT NAME FROM V$DataBase;
SELECT To_Char(SysDate,'YYYY-MM-DD_HH24-MI-SS')
AS "Now" FROM Dual;
ALTER SESSION SET
Current_Schema = ' put the schema name here'
NLS_Date_Format = 'YYYY-Mon-DD HH24:MI:SS';
SET Define On
SPOOL " put the script name here ".1.&DataBase..&Now..log
PROMPT EXIT
SET LineSize 254 TrimSpool On
ALTER SYSTEM CheckPoint LOCAL;
COLUMN CheckPoint_Change#-
FORMAT 999999999999999999
SELECT
Name,
To_Char(SysDate,'YYYY-MM-DD_HH24-MI-SS'),
CheckPoint_Change#
FROM V$DataBase;
WHENEVER SQLerror EXIT FAILURE ROLLBACK
SET Echo On ServerOutput On Format Wrapped
/*******************************************/
--- put the statement(s) here
/*******************************************/
Commit WORK;
DisCONNECT
SPOOL OFF
EXIT
Thursday, 3 January 2008
Thursday, 26 July 2007
Enabling Block Change Tracking
10G's Change Tracking feature improves the performance of incremental backups by recording datafile block changes in a change tracking file. Without Change Tracking, RMAN incremental backups are required to scan every block in the datafile to identify the blocks that have changed since the last database backup. Activating Change Tracking allows RMAN to read the change tracking file to identify the changed blocks. So, RMAN incremental backups should run much faster because they are no longer required to read each and every block in the datafile.
To increase performance on incremental database backups, enable block change tracking using the following command:
alter database enable block change tracking using file file_name;
If the db_create_file_dest parameter is set in the spfile or init.ora file of the database, the following command can be used:
alter database enable block change tracking;
Once you enable block change tracking, incremental database backup will use block change tracking.
The size of the change tracking file depends on the size of the database and not the frequency of updates. Oracle states that the size of the block tracking file is 1/30,000 the size of all the database data blocks being tracked by Change Tracking. Oracle also states that the file is created in 10 MB increments. For databases up to and including one terabyte, the size of the change tracking file will be 10MEGs, 2 terabyte databases will require 20MEGs and so on.
To increase performance on incremental database backups, enable block change tracking using the following command:
alter database enable block change tracking using file file_name;
If the db_create_file_dest parameter is set in the spfile or init.ora file of the database, the following command can be used:
alter database enable block change tracking;
Once you enable block change tracking, incremental database backup will use block change tracking.
The size of the change tracking file depends on the size of the database and not the frequency of updates. Oracle states that the size of the block tracking file is 1/30,000 the size of all the database data blocks being tracked by Change Tracking. Oracle also states that the file is created in 10 MB increments. For databases up to and including one terabyte, the size of the change tracking file will be 10MEGs, 2 terabyte databases will require 20MEGs and so on.
Wednesday, 18 July 2007
RMAN Compressed Files
With disk-based backups in the flash recovery area, you still have a big limitation: disk space. Especially when going across a network—as is usually the case—it's advisable to create as small a backup set as possible. In Oracle Database 10g RMAN, you can compress files inside the backup command itself:
RMAN>
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
setlimit channel d1 kbytes 1024000;
setlimit channel d2 kbytes 1024000;
backup format '/oradisk2/backups/ORATST/rman_%d_%t_%U.ctf' current controlfile;
backup format '/oradisk2/backups/ORATST/rman_%d_%t_%U.arf' filesperset 10 archivelog all;
backup as compressed backupset database format '/oradisk2/backups/ORATST/rman_%d_%t_%U.dtf';
release channel d1;
release channel d2;
}
exit;
Note the use of the clause COMPRESSED. It compresses backup files with an important difference: while restoring, RMAN can read the files without uncompressing. To confirm compression, check for the following message in the output:
channel d1: starting compressed full datafile backupset
Furthermore, you can verify that the backup was compressed by checking the RMAN list output:
RMAN> list backup of database;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
49 Full 61.18M DISK 00:01:24 18-JUL-07
BP Key: 49 Status: AVAILABLE Compressed: YES Tag: TAG20070718T142021
Piece Name: /oradisk2/backups/ORATST/rman_ORATST_628266022_1pin5516_1_1.dtf
List of Datafiles in backup set 49
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 386458 18-JUL-07 /oradisk1/oradata/ORATST/datafile/o1_mf_undotbs1_1zl14557_.dbf
3 Full 386458 18-JUL-07 /oradisk1/oradata/ORATST/datafile/o1_mf_sysaux_1zl14gvs_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50 Full 66.14M DISK 00:01:30 18-JUL-07
BP Key: 50 Status: AVAILABLE Compressed: YES Tag: TAG20070718T142021
Piece Name: /oradisk2/backups/ORATST/rman_ORATST_628266022_1oin5516_1_1.dtf
List of Datafiles in backup set 50
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 386456 18-JUL-07 /oradisk1/oradata/ORATST/datafile/o1_mf_system_1zl13ds1_.dbf
4 Full 386456 18-JUL-07 /oradisk1/oradata/ORATST/datafile/o1_mf_users_1zl150qk_.dbf
As with any compression process, this approach puts pressure on CPUs. As a tradeoff, you can keep more RMAN backups on disk that are readily available for restore-and-recover operations. Alternatively, you can make RMAN backups at the Physical Standby Database that can be used to recover the primary database. That approach will offload backup resourses to another host.
Monday, 16 July 2007
RMAN without a recovery catalog
You can use RMAN with or without the central information repository called a recovery catalog. If you do not use a recovery catalog, RMAN uses the database’s control file to store information necessary for backup and recovery operations.
How to start RMAN without a Catalog
C:\>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 17 00:11:28 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORATST (DBID=709496429)
RMAN>
How to configure Backup Parameters
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/10.2.0/dbs/snapcf_ORATST1.f'; # default
>> Oracle recommends that CONFIGURE CONTROLFILE AUTOBACKUP be set to ON
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.
Run Full Backup
RMAN> REPORT NEED BACKUP;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /oradisk1/oradata/ORATST/datafile/o1_mf_system_1zl13ds1_.dbf
2 0 /oradisk1/oradata/ORATST/datafile/o1_mf_undotbs1_1zl14557_.dbf
3 0 /oradisk1/oradata/ORATST/datafile/o1_mf_sysaux_1zl14gvs_.dbf
4 0 /oradisk1/oradata/ORATST/datafile/o1_mf_users_1zl150qk_.dbf
Performing a Full Level 0 Backup
RMAN> run {
allocate channel d1 type disk;
setlimit channel d1 kbytes 1024000;
backup format '/oradisk2/backups/ORATST/rman_%d_%t_%U.ctf' current controlfile;
backup format '/oradisk2/backups/ORATST/rman_%d_%t_%U.arf' archivelog all;
backup database format '/oradisk2/backups/ORATST/rman_%d_%t_%U.dtf';
release channel d1;
}
exit;
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 17-JUL-07 1 1 NO TAG20070717T011634
2 B A A DISK 17-JUL-07 1 1 NO TAG20070717T011638
3 B F A DISK 17-JUL-07 1 1 NO TAG20070717T011643
4 B F A DISK 17-JUL-07 1 1 NO TAG20070717T011759
Detecting Physical and Logical Block Corruption
You can run the BACKUP ... VALIDATE command to check datafiles for physical and logical corruption, or to confirm that all database files exist in the correct locations. No backup is taken, but all specified files are scanned to verify that they can be backed up. All corruptions are recorded in theV$DATABASE_BLOCK_CORRUPTION view.
The following example shows how to check your entire database and archived redo log files for physical and logical corruption:
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
Starting backup at 17-JUL-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradisk1/oradata/ORATST/datafile/o1_mf_system_1zl13ds1_.dbf
input datafile fno=00002 name=/oradisk1/oradata/ORATST/datafile/o1_mf_undotbs1_1zl14557_.dbf
input datafile fno=00003 name=/oradisk1/oradata/ORATST/datafile/o1_mf_sysaux_1zl14gvs_.dbf
input datafile fno=00004 name=/oradisk1/oradata/ORATST/datafile/o1_mf_users_1zl150qk_.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=1 stamp=628132598
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-JUL-07
Rman Views V$RMAN_BACKUP_JOB_DETAILS, V$RMAN_BACKUP_TYPE, V$RMAN_OUTPUT
SQL>
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'yyyy/mm/dd hh24:mi') start_time,
to_char(END_TIME,'yyyy/mm/dd hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key ;
The output may resemble the one shown below:
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS
----------- ------------- --------- ---------------- ---------------- -------
2 CONTROLFILE FAILED 2007/07/17 01:14 2007/07/17 01:14 .00
6 DB FULL COMPLETED 2007/07/17 01:16 2007/07/17 01:18 .02
11 DB FULL COMPLETED 2007/07/17 01:29 2007/07/17 01:37 .13
SQL>
col ins format a10
col outs format a10
col time_taken_display format a20
select SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY ins,
OUTPUT_BYTES_PER_SEC_DISPLAY outs,
TIME_TAKEN_DISPLAY
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SESSION_KEY OPT COMPRESSION_RATIO INS OUTS TIME_TAKEN_DISPLAY
----------- --- ----------------- ---------- ---------- --------------------
2 NO 1 0.00K 0.00K 00:00:03
6 NO 1.3441441 9.12M 6.79M 00:01:29
11 NO 1 3.46M 0.00K 00:07:41
SQL> select * from V$RMAN_BACKUP_TYPE;
WEIGHT INPUT_TYPE
---------- -------------
1 BACKUPSET
2 SPFILE
3 CONTROLFILE
4 ARCHIVELOG
5 DATAFILE INCR
6 DATAFILE FULL
7 DB INCR
8 RECVR AREA
9 DB FULL
SQL>
select output
from v$rman_output
where session_key = 4
order by recid;
OUTPUT
--------------------------------------------------------------------------------
connected to target database: ORATST (DBID=709496429)
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /oradisk1/oradata/ORATST/datafile/o1_mf_system_1zl13ds1_.dbf
2 0 /oradisk1/oradata/ORATST/datafile/o1_mf_undotbs1_1zl14557_.dbf
3 0 /oradisk1/oradata/ORATST/datafile/o1_mf_sysaux_1zl14gvs_.dbf
4 0 /oradisk1/oradata/ORATST/datafile/o1_mf_users_1zl150qk_.dbf
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/10.2.0/dbs/snapcf_ORATST
1.f'; # default
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /oradisk1/oradata/ORATST/datafile/o1_mf_system_1zl13ds1_.dbf
2 0 /oradisk1/oradata/ORATST/datafile/o1_mf_undotbs1_1zl14557_.dbf
3 0 /oradisk1/oradata/ORATST/datafile/o1_mf_sysaux_1zl14gvs_.dbf
4 0 /oradisk1/oradata/ORATST/datafile/o1_mf_users_1zl150qk_.dbf
allocated channel: d1
channel d1: sid=42 devtype=DISK
Starting backup at 17-JUL-07
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 17-JUL-07
released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on d1 channel at 07/17/2007 01:14:42
ORA-19504: failed to create file "/oradisk2/backups/ORATST/rman_ORATST_628132480
_01in12k0_1_1.ctf"
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
How to start RMAN without a Catalog
C:\>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 17 00:11:28 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORATST (DBID=709496429)
RMAN>
How to configure Backup Parameters
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/10.2.0/dbs/snapcf_ORATST1.f'; # default
>> Oracle recommends that CONFIGURE CONTROLFILE AUTOBACKUP be set to ON
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.
Run Full Backup
RMAN> REPORT NEED BACKUP;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /oradisk1/oradata/ORATST/datafile/o1_mf_system_1zl13ds1_.dbf
2 0 /oradisk1/oradata/ORATST/datafile/o1_mf_undotbs1_1zl14557_.dbf
3 0 /oradisk1/oradata/ORATST/datafile/o1_mf_sysaux_1zl14gvs_.dbf
4 0 /oradisk1/oradata/ORATST/datafile/o1_mf_users_1zl150qk_.dbf
Performing a Full Level 0 Backup
RMAN> run {
allocate channel d1 type disk;
setlimit channel d1 kbytes 1024000;
backup format '/oradisk2/backups/ORATST/rman_%d_%t_%U.ctf' current controlfile;
backup format '/oradisk2/backups/ORATST/rman_%d_%t_%U.arf' archivelog all;
backup database format '/oradisk2/backups/ORATST/rman_%d_%t_%U.dtf';
release channel d1;
}
exit;
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 17-JUL-07 1 1 NO TAG20070717T011634
2 B A A DISK 17-JUL-07 1 1 NO TAG20070717T011638
3 B F A DISK 17-JUL-07 1 1 NO TAG20070717T011643
4 B F A DISK 17-JUL-07 1 1 NO TAG20070717T011759
Detecting Physical and Logical Block Corruption
You can run the BACKUP ... VALIDATE command to check datafiles for physical and logical corruption, or to confirm that all database files exist in the correct locations. No backup is taken, but all specified files are scanned to verify that they can be backed up. All corruptions are recorded in theV$DATABASE_BLOCK_CORRUPTION view.
The following example shows how to check your entire database and archived redo log files for physical and logical corruption:
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
Starting backup at 17-JUL-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradisk1/oradata/ORATST/datafile/o1_mf_system_1zl13ds1_.dbf
input datafile fno=00002 name=/oradisk1/oradata/ORATST/datafile/o1_mf_undotbs1_1zl14557_.dbf
input datafile fno=00003 name=/oradisk1/oradata/ORATST/datafile/o1_mf_sysaux_1zl14gvs_.dbf
input datafile fno=00004 name=/oradisk1/oradata/ORATST/datafile/o1_mf_users_1zl150qk_.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=1 stamp=628132598
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-JUL-07
Rman Views V$RMAN_BACKUP_JOB_DETAILS, V$RMAN_BACKUP_TYPE, V$RMAN_OUTPUT
SQL>
col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'yyyy/mm/dd hh24:mi') start_time,
to_char(END_TIME,'yyyy/mm/dd hh24:mi') end_time,
elapsed_seconds/3600 hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key ;
The output may resemble the one shown below:
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS
----------- ------------- --------- ---------------- ---------------- -------
2 CONTROLFILE FAILED 2007/07/17 01:14 2007/07/17 01:14 .00
6 DB FULL COMPLETED 2007/07/17 01:16 2007/07/17 01:18 .02
11 DB FULL COMPLETED 2007/07/17 01:29 2007/07/17 01:37 .13
SQL>
col ins format a10
col outs format a10
col time_taken_display format a20
select SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY ins,
OUTPUT_BYTES_PER_SEC_DISPLAY outs,
TIME_TAKEN_DISPLAY
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SESSION_KEY OPT COMPRESSION_RATIO INS OUTS TIME_TAKEN_DISPLAY
----------- --- ----------------- ---------- ---------- --------------------
2 NO 1 0.00K 0.00K 00:00:03
6 NO 1.3441441 9.12M 6.79M 00:01:29
11 NO 1 3.46M 0.00K 00:07:41
SQL> select * from V$RMAN_BACKUP_TYPE;
WEIGHT INPUT_TYPE
---------- -------------
1 BACKUPSET
2 SPFILE
3 CONTROLFILE
4 ARCHIVELOG
5 DATAFILE INCR
6 DATAFILE FULL
7 DB INCR
8 RECVR AREA
9 DB FULL
SQL>
select output
from v$rman_output
where session_key = 4
order by recid;
OUTPUT
--------------------------------------------------------------------------------
connected to target database: ORATST (DBID=709496429)
using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /oradisk1/oradata/ORATST/datafile/o1_mf_system_1zl13ds1_.dbf
2 0 /oradisk1/oradata/ORATST/datafile/o1_mf_undotbs1_1zl14557_.dbf
3 0 /oradisk1/oradata/ORATST/datafile/o1_mf_sysaux_1zl14gvs_.dbf
4 0 /oradisk1/oradata/ORATST/datafile/o1_mf_users_1zl150qk_.dbf
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/product/10.2.0/dbs/snapcf_ORATST
1.f'; # default
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /oradisk1/oradata/ORATST/datafile/o1_mf_system_1zl13ds1_.dbf
2 0 /oradisk1/oradata/ORATST/datafile/o1_mf_undotbs1_1zl14557_.dbf
3 0 /oradisk1/oradata/ORATST/datafile/o1_mf_sysaux_1zl14gvs_.dbf
4 0 /oradisk1/oradata/ORATST/datafile/o1_mf_users_1zl150qk_.dbf
allocated channel: d1
channel d1: sid=42 devtype=DISK
Starting backup at 17-JUL-07
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 17-JUL-07
released channel: d1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on d1 channel at 07/17/2007 01:14:42
ORA-19504: failed to create file "/oradisk2/backups/ORATST/rman_ORATST_628132480
_01in12k0_1_1.ctf"
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Overview of RMAN Functional Components
The RMAN environment consists of the utilities and databases that play a role in backing up your data. At a minimum, the environment for RMAN must include the following:
● The target database to be backed up
● The RMAN client, which interprets backup and recovery commands, directs server sessions to execute those commands, and records your backup and recovery activity in the target database control file.
Some environments will also use these optional components:
● A flash recovery area, a disk location in which the database can store and manage files related to backup and recovery
● Media management software, required for RMAN to interface with backup devices such as tape drives
● A recovery catalog database, a separate database schema used to record RMAN activity against one or more target databases
The following diagram illustrates these components:
● The target database to be backed up
● The RMAN client, which interprets backup and recovery commands, directs server sessions to execute those commands, and records your backup and recovery activity in the target database control file.
Some environments will also use these optional components:
● A flash recovery area, a disk location in which the database can store and manage files related to backup and recovery
● Media management software, required for RMAN to interface with backup devices such as tape drives
● A recovery catalog database, a separate database schema used to record RMAN activity against one or more target databases
The following diagram illustrates these components:
Source: Oracle 11g RMAN
Wednesday, 11 July 2007
10g Automatic Statistics Generation
In releases prior to Oracle10g, administrators were required to schedule DBMS_STATS jobs on a regular basis to ensure that valid statistics were available to the optimizer. In releases prior to Oracle9i, it was necessary to "guess" how much of the data changed to determine if statistics collection was necessary.
In Oracle9i, the GATHER AUTO option of the DBMS_STATS procedure could be used to help determine if statistics generation was required. If more than 10% of the rows changed in the table since the last analyze was performed, the DBMS_STATS procedure (with the GATHER_AUTO option activated) analyzed the table.
By default, Oracle10G automates these tasks by evaluating the statistics for all of the tables in the database and running analyze when required. Oracle10g's default maintenance window is nightly from 10 PM to 6 AM and all day on weekends. During these time periods, statistics are automatically collected using the GATHER_STATS_JOB procedure. The maintenance window time-periods can be adjusted to tailor them to each individual application's business processing requirements.
In Oracle Database 10g, Automatic Optimizer Statistics Collection reduces the likelihood of poorly performing SQL statements due to stale or invalid statistics and enhances SQL execution performance by providing optimal input to the query optimizer.
To ensure that 10G is automatically gathering statistics for data objects, you'll need to verify that:
- The statistics job is running by executing the following SQL statement:
SELECT * FROM DBA_SCHEDULER_JOBS
In Oracle9i, the GATHER AUTO option of the DBMS_STATS procedure could be used to help determine if statistics generation was required. If more than 10% of the rows changed in the table since the last analyze was performed, the DBMS_STATS procedure (with the GATHER_AUTO option activated) analyzed the table.
By default, Oracle10G automates these tasks by evaluating the statistics for all of the tables in the database and running analyze when required. Oracle10g's default maintenance window is nightly from 10 PM to 6 AM and all day on weekends. During these time periods, statistics are automatically collected using the GATHER_STATS_JOB procedure. The maintenance window time-periods can be adjusted to tailor them to each individual application's business processing requirements.
In Oracle Database 10g, Automatic Optimizer Statistics Collection reduces the likelihood of poorly performing SQL statements due to stale or invalid statistics and enhances SQL execution performance by providing optimal input to the query optimizer.
To ensure that 10G is automatically gathering statistics for data objects, you'll need to verify that:
- The statistics job is running by executing the following SQL statement:
SELECT * FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOB';
- The modification monitoring feature that identifies stale statistics is enabled.
This feature is enabled when the STATISTICS_LEVEL parameter is set to TYPICAL (default) or ALL.
- The modification monitoring feature that identifies stale statistics is enabled.
This feature is enabled when the STATISTICS_LEVEL parameter is set to TYPICAL (default) or ALL.
Source: DBAzine.com
Sunday, 8 July 2007
Bug 4690794 - "error in kqlmbivg see lck trace file" messages
I often see the following error message in the alert.log file of an Oracle10gR2 database for Linux:
>>> ERROR IN KQLMBIVG SEE LCK TRACE FILE <<<
According to Oracle Metalink Note 4690794.8, this issue is fixed on patch set 10.2.0.3.
Related to: RAC (Real Application Clusters) / OPS
Symptoms: None
Description:
Global enqueue ordering can be violated which can lead to messages of the form:
"error in kqlmbivg see lck trace file"
>>> ERROR IN KQLMBIVG SEE LCK TRACE FILE <<<
According to Oracle Metalink Note 4690794.8, this issue is fixed on patch set 10.2.0.3.
Related to: RAC (Real Application Clusters) / OPS
Symptoms: None
Description:
Global enqueue ordering can be violated which can lead to messages of the form:
"error in kqlmbivg see lck trace file"
Subscribe to:
Posts (Atom)