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.

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

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:
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
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.
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"

Thursday, 5 July 2007

How to get an Oracle DBA job

In just 48 hours, 120 resumes from Oracle DBAs landed on the desk of Debbie Reames, a senior technical recruiter for Los Angeles-based staffing company Commercial Programming Systems. So Reames did what a lot of recruiters do. She wiped out half the pile in a three-step process. Need an H-1B visa? Gone. Need to relocate? Not for this job. Only worked at small companies? This job might be too big for you.

Despite the steepest downturn in the history of IT, there are jobs to be had by Oracle certified professionals and DBAs, according to many surveys. Of course, there are also longer lists of qualified candidates. When it comes to searching for a job in this market, even the most experienced IT pros need to remember some of the rules they learned long ago and apply some new strategies as well.

In many ways, the DBA market can be divided into two groups – juniors and seniors.

The junior DBAs, those with less than three years of experience, are the ones facing layoffs and a meaner job market, according to Don Burleson, owner of Kittrell, N.C.-based BEI Oracle Consulting. Burleson is the author of 17 books related to Oracle technology and careers, including Conducting the Oracle Job Interview, a guide for IT managers who have to assess Oracle job candidates.

"Many companies will not hire beginners, period," Burleson said.

However, Burleson said, there is hope for junior DBAs. Companies that are not willing to spend $120,000 annually for a seasoned professional will sometimes take rookies and train them. Burleson suggested the health care industry and universities as two places that new DBAs should look for that critical first job.
Government security clearance key for veterans

Then there are the veterans. DBAs who have more than 10 years of experience, who hold advanced degrees and who have specialized skills are still in strong demand.

Knowledge of Oracle financials, SAP, PeopleSoft, RAC, iAS and Unix are qualities that carry weight, Burleson said. Also, government security clearance is something that gets many Oracle DBAs past the first round of resume cuts. In addition, development skills such as Java, Windows, J2EE and portals lend candidates a competitive advantage.

More important, Reames said, a candidate's skill set has to match a company's needs. "They really need to have every skill," Reames said.

"If the job calls for someone with data modeling," she said, "and I don't see any specific data modeling experience, I'll discount it."

In addition to specific technical skills, Burleson says, many companies are looking at educational backgrounds, preferring to hire candidates who have graduate degrees or MBAs. Companies value DBAs who have an understanding of finance or accounting, the business processes that DBAs support.
Follow-up calls, first-round interviews

For both groups, there are some basic ground rules to getting through the critical first round of interviews.

Simply getting the resume in as early as possible can help. With 120 to read, Reames might not see each of them before she finds enough qualified candidates to interview.

Candidates should call recruiters too see whether their resume was received, she said. "Don't be afraid to call to follow up," Reames said. "It may give you an advantage, and we may not have looked at the resume otherwise."

Applicants who make the first cut are usually interviewed on the telephone. It's important to remember that the person conducting the interview may not have a technical background, Burleson said. At this point, candidates are being judged on their non-technical qualities. Communication skills are crucial here, experts say.

Because communication skills are so critical on the job, Burleson recommended that candidates provide potential employers with writing samples, preferably ones that have been published. Several online sites, accept submissions of technical tips, he pointed out.
Real techie talk

The handful of applicants who survive the telephone interview can expect an interview at the job site and what Burleson calls the "teching" of the candidate. He advises interviewers to ask specific questions, such as "What is the default password for the sys user in Oracle?"

There's no faking answers to those questions, and an interviewer can easily judge the technical skills of the candidates. "Only a practicing DBA knows the answer is 'change_on_install,'" Burleson said. "A seasoned DBA can spot a faker instantly."

Then comes the last stage, on-site meetings with a company's IT staff. These meetings typically last about half a day, and this is where the potential hires will really be tested. Sometimes there are open-ended questions, such as "What would be the first thing you would do if an end user complains that performance is poor?" The answers to these questions can be very revealing, Burleson said, because they don't have one right answer, and they show how candidates can think on their feet, or how innovative a DBA is.

Perhaps more important, this final interaction is where the intangibles, like interpersonal skills, are judged. Having a DBA who is a team player is a priority for most, if not all, companies. In many cases, the intangibles make the difference.

"I've seen companies reject the most technically qualified candidate. It happens all the time," Burleson points out. "DBAs have to be able to play well with others."

Finally, Burleson said, forget casual Fridays.

"It's the kiss of death to underdress for an interview," he said.

"Appearance does count."
For more information, consult Don Burleson's book Conducting the Oracle Job Interview