Prev: Documentation
Next: Optimizer 10053 trace file, strange choises made by Oracle??
From: Gary on 14 Dec 2009 10:50
Jeremiah Wilton wrote:
> You can query something like:
>
> select l.name
> from v$archived_log l,
> v$database d
> where l.activation# = d.activation#
> and l.next_change# <
> (select min(change#)
> from v$backup);
>
> The above query lists all archivelogs older than the earliest start-
> backup time of all datafiles. You can delete logs in this list,
> assuming that:

Excellent. This is exactly what I was looking for. Thank you.

> 1. You are in fact using tablespace hot backup mode. If not, then all
> open backups are invalid.
> 2. You never want to recover to a point in time prior to the most
> recent backup using a prior backup
> 3. You are in fact copying all files that you put into backup mode
> 4. Your controlfile record keep time is longer than the age of your
> oldest archivelog.

1. Correct. The database is in archivelog mode and the backups are being
done within 'begin backup' and 'end backup' statement.
2. Correct. Any older ocopy backups will be restored from the time they
are run. Transactions logs will not be used.
3. Correct. I'm using a script which loops through the tablespaces in
sys.dba_tablespaces, putting them in backup mode, backing them up with
ocopy, and taking them out of backup mode.
4. I'm not sure. The control files are being backed up to trace, then
backed up to file:

dbms_output.put_line('alter database backup controlfile to trace;');
dbms_output.put_line('alter database backup controlfile to '||''''||
copy_dest||'control.'||dbname||'.'||
to_char(sysdate,'DDMonYYHH24MI')||''''||';');

This section comes last in the backup script, just before the the
logfile is switched. I'm not sure what is meant by "keeptime is longer
than the age of the oldest archivelog"; does this mean the controlfile
should be backed up /first/ in the script?

Thanks again,
-Gary
From: joel garry on 14 Dec 2009 12:41
On Dec 14, 7:50 am, Gary <gjwater...(a)gmail.com> wrote:

>
> This section comes last in the backup script, just before the the
> logfile is switched. I'm not sure what is meant by "keeptime is longer
> than the age of the oldest archivelog"; does this mean the controlfile
> should be backed up /first/ in the script?
>

There is an entry in the system reference manual for an alter system
command called CONTROL_FILE_RECORD_KEEP_TIME "...specifies the minimum
number of days before a reusable record in the control file can be
reused."

The default is 7 days, which is entirely too short for many places.
Google about for opinions on how this should be set for different ways
of dealing with archived logs like
http://mrothouse.wordpress.com/2006/11/06/oracle9i-control_file_record_keep_time/
Personally, I set it to 30 and then figure out what it should be with
management sign-off on a given retention policy.

jg
--
@home.com is bogus.
http://www.oracle.com/technology/tech/php/pdf/underground-php-oracle-manual..pdf
First  |  Prev  | 
Pages: 1 2
Prev: Documentation
Next: Optimizer 10053 trace file, strange choises made by Oracle??