Restore Archives » Musings of an IT Implementor

Scenario: You’re about to implement some major changes to the Oracle database that will adjust the database statistics.
Oracle provide the DBMS_STATS package to help administer stats. The package includes some procedures that can be used to export/import stats, plus restore them to a previous point in time.

When statistics are updated using DBMS_STATS.GATHER_*_STATS, it saves the previous version in the database (can be changed with DBMS_STATS.ALTER_STATS_HISTORY_RETENTION procedure). Also, see table DBA_TAB_STATS_HISTORY.

These versions are retained for a specific retention period, which you can check using the GET_STATS_HISTORY_RETENTION procedure:

SQL> set serveroutput on
SQL> DECLARE
v number;
BEGIN
v := DBMS_STATS.GET_STATS_HISTORY_RETENTION;
DBMS_OUTPUT.PUT_LINE(‘Stats history retention: ‘ || v || ‘ days.’);
END;
/ 
Stats history retention x days.
PL/SQL procedure successfully completed. 

You can also check the date of the oldest stats history:

SQL> set serveroutput on
SQL> DECLARE
v timestamp;
BEGIN
v := DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY;
DBMS_OUTPUT.PUT_LINE(‘Oldest stats history: ‘ || v);
END;
/
Oldest stats history: 15-DEC-13 11.29.32.143688 PM
PL/SQL procedure successfully completed

To restore the statistics you can use one of the relevant procedures:

DBMS_STATS.RESTORE_DICTIONARY_STATS
DBMS_STATS.RESTORE_FIXED_OBJECT_STATS
DBMS_STATS.RESTORE_SCHEMA_STATS
DBMS_STATS.RESTORE_SYSTEM_STATS
DBMS_STATS.RESTORE_TABLE_STATS

See here for parameters:
https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm#insertedID2
As an example, the RESTORE_SCHEMA_STATS procedure takes the following parameters:

ownname   Schema owner,
timestamp   Timestamp,
force   TRUE|FALSE   Restore even if stats are locked, default TRUE,
no_invalidate   TRUE|FALSE   Invalidate cursors, default get_param(‘NO_INVALIDATE’).

If the stats are restored to a specific timestamp, it means that whatever statistics values were applicable to a specific table at a specific point in time, are applied to the tables. If the table’s statistics are not changed then there will be gaps in the history.
You can imagine this being like a roll-forward through the DBA_TAB_STATS_HISTORY table, until the timestamp specified.

WARNING: If the table’s statistics are not changed then there will be gaps in the history. In which case, you may not be able to restore previous statistics if the table stats have not changed within the last history window (default 31 days).

Some great examples are here: https://www.morganslibrary.org/reference/pkgs/dbms_stats.html

You should also note, that under an SAP system, the Oracle stats gatherer is called by BR*Connect, and note that it calls the GATHER_TABLE_STATS procedure for each table that is mentioned in table DBSTATC for tables that have stats enabled in DBSTATC.
If the table is not enabled to collect stats, then it may have stats delivered by SAP (see SAP note 1020260), in which case, there may not be any history.

Also see my blog post on SAP statistics and DBSTATC.

Scenario: You have restored a database from a backup but part way through recovery, you didn’t have all the archive redo logs.
Your recovery is an automated script that then progressed on to OPEN RESTLOGS, which failed.

Now you have all the archive logs and you want to re-run recovery, but you get ORA-01547 plus ORA-01194.

If you are certain that you now have all the archive logs (maybe you forgot to copy all of them), you need to restart recovery:

SQL> shutdown abort; SQL> startup mount exclusive; SQL> recover automatic database using backup controlfile until cancel;

You now get the error:

ORA-00279: change 5996813573990 generated at 01/30/2013 18:38:25 needed for thread 1 ORA-00289: suggestion : /oradata/PROD/archive/PROD_806092582_1_1.arc ORA-00280: change 5996813573990 for thread 1 is in sequence #1 ORA-00278: log file '/oradata/PROD/archive/PROD_806092582_1_1.arc' no longer needed for this recovery ORA-00308: cannot open archived log '/oradata/PROD/archive/PROD_806092582_1_1.arc' ORA-27037: unable to obtain file status HPUX-ia64 Error: 2: No such file or directory Additional information: 3

The database is looking for redo information generated during the previous recovery session.
During a recovery, redo information is generated because the database is replaying the undo records of transactions to be rolled back, but these records may need to be rolled back. So you get redo records.

Unfortunately, because your database is in a precarious state, it doesn’t quite know where to look for the redo records.
So, all you need to do is tell it where the redo logs are located (your database probably won’t have performed a log switch yet).

First, cancel the recovery:

SQL> CANCEL

For checking purposes, it’s good to confirm that only SYSTEM tablespace is needing recovery by querying the current SCNs across all datafiles:

SQL> select distinct fhscn SCN from x$kcvfh;

SCN ---------------- 5996813573990

Again, for checking purposes, you can see that one of the datafiles has a status of != 4. (4= consistent):

SQL> select distinct fhsta STATUS from x$kcvfh;

STATUS ---------- 4 8196

You can see that this is the SYSTEM datafile (filenumber =1):

SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh where fhsta = 8196;

FILENUMBER STATUS SCN SEQUENCE ---------- ---------- ---------------- ---------- 1 8196 5996813573990 1

To be able to provide the first redo log to the recovery process, you should query your log members.
I have found that usually, the recovery only needs the very first log member listed when you run the following query (I guess this is the “active” log member at the time the RESETLOGS was called):

SQL> select member from v$logfile;

MEMBER
—————————————————-
/oradata/PROD/log_a/log4a_PROD.log <<– This one.
/oradata/PROD/log_b/log4b_PROD.log
/oradata/PROD/log_a/log3a_PROD.log
/oradata/PROD/log_b/log3b_PROD.log
/oradata/PROD/log_a/log2a_PROD.log
/oradata/PROD/log_b/log2b_PROD.log
/oradata/PROD/log_a/log1a_PROD.log
/oradata/PROD/log_b/log1b_PROD.log

8 rows selected.

Now you can call the recovery process again.

This time, when you are prompted for the archive log name, enter the redo log member name & path you got from the previous SQL:

SQL> recover automatic database using backup controlfile until cancel;

 
ORA-00279: change 5996813573990 generated at 01/30/2013 18:38:25 needed for thread 1
ORA-00289: suggestion : /oradata/PROD/archive/PROD_806092582_1_1.arc
ORA-00280: change 5996813573990 for thread 1 is in sequence #1
ORA-00278: log file '/oradata/PROD/archive/PROD_806092582_1_1.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/oradata/PROD/archive/PROD_806092582_1_1.arc'
ORA-27037: unable to obtain file status
HPUX-ia64 Error: 2: No such file or directory
Additional information: 3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/PROD/log_a/log4a_PROD.log

Log applied. Media recovery complete.

Now open your database:

SQL> alter database open resetlogs;
Database altered.

SQL> exit

You should now run an immediate FULL backup using your preferred method.

Tag: Restore

HowTo: Using DBMS_STATS to Restore Stats

HowTo: Recover Oracle DB with ORA-01547 after RESETLOGS Failed