A setup to test backup and restore options

A couple of years my team put together some procedures and the underlying databases to allow testing of as many RMAN recovery options as we could think of. We had two databases which gave us a Dataguard capability and we secured both databases using Commvault backups so we always had a clean starting point. Each team member had a training objective to complete the course and we all thought it was an excellent refresher process.   As I recall Hamid Ansari did most of the work and I should mention him if I mention the output.

 

I noticed today that Francisco Munoz Alvarez has provided a script which does very much the same thing and I can only applaud the idea. I don’t think his script covers Dataguard but no doubt it could.  His web page Crash Simulator is where the script can be found. I will be looking at this myself in the next few days.

RMAN checksyntax function

I was looking at the RMAN DEBUG options and came across the CHECKSYNTAX function which I had not used before.

Firstly a quick recap on the DEBUG option.

This can be called using the following syntax

rman target / catalog rman12g1/xxxx@rmancat debug trace=rmantrace.log cmdfile=backup.rcv

or

rman target / catalog rman12g1/xxxx@rmancat debug trace=rmantrace.log then run RMAN> @backup.rcv (or just type in your run block of commands)

There are a number of options to DEBUG and one of the error messages lists them out quite neatly

RMAN-01009: syntax error: found "integer": expecting one of: "all, duplicate, recover, restore, resync, sql"

To be honest if I was tracing I would just stick with the DEBUG=ALL format. The DEBUG=SQL gives all the internal commands that RMAN calls and could be interesting if you were doing a deep dive into RMAN functionality

Anyway, back to the CHECKSYNTAX option.

I run it against the edited version of the command file used above

rman target / catalog rman12g1/xxxx@rmancat checksyntax cmdfile=backup.rcv 

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Dec 28 10:22:20 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: T12TEST (DBID=1543168240)
connected to recovery catalog database

RMAN> run {
2> sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''";
3> allocate channel c1 device type disk format '/app/oracle/backup/backup_db_%d_S_%s_P_%p_T_%t';
4> allocate channel c2 device type disk format '/app/oracle/backup/backup_db_%d_S_%s_P_%p_T_%t';
5> backup database INCLUDE CURRENT CONTROLFILEs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "controlfile"
RMAN-01008: the bad identifier was: CONTROLFILEs
RMAN-01007: at line 6 column 33 file: backup.rcv

Note the file backup.rcv has a blank line as the first line which confuses the line numbering

Pretty neat. I edit the file and put a different error in, a much more common missing semi-colon

RMAN> run {
2> sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''";
3> allocate channel c1 device type disk format '/app/oracle/backup/backup_db_%d_S_%s_P_%p_T_%t';
4> allocate channel c2 device type disk format '/app/oracle/backup/backup_db_%d_S_%s_P_%p_T_%t'
5> backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "backup": expecting one of: "auxiliary, connect, format, maxopenfiles, maxpiecesize, parms, rate, send, to, comma, ;"
RMAN-01007: at line 6 column 1 file: backup.rcv

Overall not a mind-shatteringly exciting find but something that might be of use one day

UKOUG Tech13 day 1 review

UKOUG conference day 1 and to my eye there does not seem to be that many differences between Manchester Central and Birmingham ICC.  Lots of smaller halls and a large main hall,  a similar lunch available, the same lack of seating. I understand that attendance is seen comparable as well. Overall I see it as a refreshing change which will suit some and not others.  Make or break is the quality of the drinks do later on – that will be reported on tomorrow.

An early start for a standing room only talk by Carl Dudley on Flashback in 11g and 12c. A really good handout was provided, which negated the need to attend the presentation. Nice factual presentation of the capabilities and options of recovering data. Would have been nice to have seen a bit more real-world ideas and examples but a session that I would recommend downloading the paper later.

Andy Colvin of Enkitec talked about 12C RMAN new features. He covered recovery of a table from an RMAN backup and I proposed my unproven theory that it might be possible to use the functionality in 11GR2 (using the 12c binaries) and he suggested trying it in 11.2.0.4 – the story continues. He also mentioned the role SYSBACKUP which takes away the need for the RMAN user to have select any table privilege. Someone in the audience stated that you might not have permission to read a table before you do a restore but you will afterwards which proved to be true. Looks like an Oracle bug. I think Andy learnt as much as the audience did. An hour well spent.

I did attend a presentation which I walked out of. I felt that the presenter was divulging too much about poor practises / long turnarounds at companies which he named. He then went into blatant advertising mode. Out I walked.

Then the CERN people were talking about latency. Lots of examples which were very good and some demos of a few tools they have created and seem to be publicly available. Well worth searching out. However I do not understand the concept of people sharing a presentation. Quite frankly one of the presenters added little value, perhaps it was a way of getting more speaker badges.

I also went to another CERN talk by David Wojcik on Cardinality feedback in 12C. This had the potential of being interesting but it was much too rushed and I doubt many in the audience came away much better informed. It was a pity because I think the slides are worthy of download and review.

My last one of the day was Martin Bach doing a non-technical presentation  – shock and horror. This was about how to manage engineered systems (or Exadata if you are in the Oracle world). An interesting and challenging talk and one that I really enjoyed. I did take issue with him re one of his recommendations and that started off what was quite a lively chat afterwards with most of the audience remaining.

Oh and the Manchester climate was quite mild, compared with ice and snow that I have suffered in Birmingham in recent years.

My final thought. Walking back to my hotel I saw a pub called The Ape and Apple. That must be worthy of some research as to what the name signifies and how it came about.

Flashback logging at tablespace level

Our site policy is to enable flashback logging on our production databases unless there is a good reason not to, two examples are the data warehouse where volume is prohibitive and the other is a highly active Peoplesoft database where performance is a primary consideration and we suffer from “flashback buf free by RVWR" wait event as it cannot write the flashback logs quickly enough. 

On the same system we had experimented with disabling flashback against two tablespaces which contained objects of a transient nature and that could be rebuilt if necessary. We finally agreed that the overhead of flashback was too heavy balanced aganst the likelyhood of us ever using it and so we disabled it at the database level.

However, when we are applying application code changes during an agreed outage we bounce the database and restart it with flashback enabled. Therefore when the changes have been applied and basic testing has taken place we can quickly flashback the database to a clean point with a minimum of fuss in the event that the changes have not produced the desired results. If the bundle is OK we disable flashback logging dynamically without a further database bounce. We find this an excellent belt and braces method which has little overhead or manageability cost.

During a recent bundle release we did want to flashback but had some problems

Cataloging RMAN backups

We had a situation where we had backed up several databases and then would restore them as part of performance testing work. The catalog entries in the controlfile were lost either because they had become obsolete or we had restored to another point prior to the backup being taken.

 The backups were on still on disk and therefore they could be re-introduced to the controlfile. 

Identifying corrupt blocks

Recently we had a datafile with a number of corrupt blocks and once it was fixed I resolved to try out all the methods available of identifying block corruption. Searching around I came across an excellent blog by   Asif Momen – Practising block recovery which provided me pretty much what I wanted. However I did think it worth adding a blog entry as I wanted to document all the various methods available to identify corruption.  I also wanted to cover how to test causing a corruption when using ASM datafiles.

Create a datafile, add data and find out which block that data is in

 create smallfile tablespace bad_data datafile '+DATA' size 10M;

Create table test (username varchar2(9), password varchar2(6)) tablespace bad_data;

DECLARE

u  VARCHAR2(9);

p  VARCHAR2(6);

BEGIN

FOR jump  IN 1 ..10000  LOOP

u := 'TEST'||jump;

p := 'P'||jump;

insert into test values (u,p);

END LOOP;

commit;

END;

/

PL/SQL procedure successfully completed.

select

rowid ,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,

to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot

from test where password='P7777'

ROWID                   OBJID    FILENUM   BLOCKNUM    ROWSLOT

—————— ———- ———- ———- ———-

AAAXaiAAHAAAACdAAr      95906          7        156         43

Reducing RMAN backup time for unevenly sized tablespaces

The following note was written by Kevin Cardew after we had discussed how to reduce the backup time of some of our large databases, especially when they constrained by most of the data being stored in a single bigfile tablespace .

Our build standards require that 11g (11.1.0.7) databases are built using BIGFILE tablespaces, however this has led to one or two problems with RMAN backups. In several of the databases the deployed application, third party supplied, has all the objects deployed to a single tablespace. The result of this is that an increasing amount of the database size resides in a single data file. During the backup RMAN balances the load by attempting to divide the backup volume (data files) over the available channels to maximise backup speed. Given the unbalanced nature of the data files, this results in a single channel being allocated to the largest data file and the rest load balanced across the remaining channels. Therefore the overall backup time is determined by the time taken to backup a single data file.

Recovering from a cold backup using RMAN

These are  basic notes that I am using when taking copies of databases that are being used for performance testing work and on which we will be performing regular regressions. I prefer writing  to disk if we have sufficient space as no time is lost in waiting for tape drive availability and  also know that I don’t have to worry about being past the retention data and the backup being deleted. There are 4 databases involved in this test and I prefer to close them all down, startup mount, backup to disk and then make them all available again.

It is a good habit to create the recovery script at the same time as the backup is created and leave a copy in the same directory as the RMAN backup pieces as it aids recovery. Both the channel command and the use of the TAG command both help in this.

Technically this is not a cold backup as the database is open, although not in use, which is why a resetlogs is required at the end. A proper cold backup is to shut the database down and take image copies of the database files and replace them as part of a restore.

Recovering old spfile from autobackup

This post is about a problem I had today when trying to restore a spfile from a previous autobackup.  I did not want the latest one but one from earlier in the week. The database was a RAC one but I don’t think that is relevant to the problems I had.

Recovering a spfile from autobackup is relatively easy – startup mount and recover spfile from autobackup.  I set my date format and started the database up in mount mode and then tried to recover the spfile.

Cloning a database (ASM to ASM)

Cloning production ASM (RAC)  to ASM (non RAC)

In this document I will be cloning database SOURCEDB (11.1.0.6 RAC) to TARGETDB (11.1.0.6 single instance). There will be no downtime on the source database and minimal impact.

Source server source_server  150Gb data ASM storage used

Target server target_server

Pre-requirements -ensure that you have backed up the source database and are comfortable that you are covered in case anything goes wrong

200Gb of disk space

NFS mount between the 2 servers. In this case I created a directory /app/oradata/rman_backup_dir and arranged for a NFS mount to the same filesystem on target_server

source_server:/app/oradata/rman_backup_dir

403701760 42657229 338479316   11% /app/oradata/rman_backup_dir

The steps we will follow will be :-

• Prepare database on target server
• Backup source database
• Run duplicate database scripts

Note 1

RMAN uses a terminology of target as being the source database and auxiliary as the duplicated database. In this document I am using the terms source and target for the two databases. However the RMAN script will use target and auxiliary instead out of necessity.

Note 2

Initially I wanted to use active duplicate database however whilst it did work once it failed many more times and there is an Oracle bug Bug 6603587 – CREATION OF SECOND STANDBY USING RMAN DUPLICATE FAILS WITH RMAN-20242

I then tried using non-active database and with a database backup. This time I hit Bug.5653255: PHSB: RMAN CAN\’T RESTORE DATAFILES CORRECTLY ON ASM USING IMAGE COPIES. The workaround to this was to use a backupset..

Prepare target database

I am assuming that we have an existing database built on the target server and we are just performing a refresh from production. However for completeness this section shows how to prepare the spfile and startup nomount the target database.

If this process is a refresh rather than new build then go to here

Create a tnsnames.ora entry on the source server

Note that we are using port 1524 whereas the source database uses port 1522

TARGETDB_CLONE  =

(DESCRIPTION =

(ADDRESS_LIST=

(ADDRESS = (PROTOCOL = TCP)(HOST = target_server)(PORT = 1524))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TARGETDB)

)

)

Create a listener on the target server

Note that we are using port 1524 whereas the source database uses port 1522

LISTENERCLONE =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = target_server)(PORT = 1524))

)

)

SID_LIST_LISTENERCLONE =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = TARGETDB)

(ORACLE_HOME = /app/oracle/product/11.1.0/db_1/)

(SID_NAME = TARGETDB)

)

)

Start the listener

lsnrctl start listenerclone

On source_server

create pfile=\’/home/oracle/initTARGETDB.ora\’ from spfile;

Create pfile – target_server. Edit to remove references to RAC (threads etc). As I am moving from DATA/FRA diskgroup to DATA/FRA diskgroup I have no need to use any covert file_name parameters. Edit the spfile to replace the source SID name with the new SID

Edit /etc/oratab to add database entry

Copy the pfile from source_server

cd $ORACLE_HOME/dbs

scp oracle@source_server:/home/oracle/initTARGETDB.ora .

Create audit dest on targetserver

mkdir -p /app/oracle/admin/TARGETDB/adump

Copy passwordfile from source_server

scp oracle@source_server:/app/oracle/product/11.1.0/db_1/orapwSOURCEDB /app/oracle/product/11.1.0/db_1/orapwTARGETDB

Startup standby database from targetserver

sqlplus  / as sysdba

create spfile from pfile;

startup nomount

exit

Start the clone listener on target_server.

This is required as you cannot connect to a database that is unavailable with the asm listener

lsnrctl start listenerclone

From the primary database server source_server test the connection to the target database using the command:

sqlplus sys/password@TARGETDB_CLONE as sysdba

Prepare refreshed database

sqlplus  / as sysdba

shutdown immediate;

startup nomount;

exit

Backup source database

rm /app/oradata/rman_backup_dir/*

export NLS_DATE_FORMAT=\’YYYY-MM-DD:HH24:MI:SS\’

rman target /

run

{

allocate channel d1 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d2 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d3 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d4 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d5 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

backup as backupset database plus archivelog;

}

This takes around 10 minutes. Make a note of the finish time (which is why the NLS_DATE_FORMAT variable was set)

Duplicate database

2 scripts have been created in /home/oracle on pebor02 which are pasted here

rman_duplicate_from_backup.sh

#

# Set the Oracle Environment

#

export ORACLE_HOME= /app/oracle/product/11.1.0/db_1/

export ORACLE_SID=SOURCEDB

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_LANG=american

export NLS_DATE_FORMAT=\’YYYY-MM-DD:HH24:MI:SS\’

echo $ORACLE_HOME

echo $ORACLE_SID

#

# Run the RMAN Command

#

nohup  $ORACLE_HOME/bin/rman target sys/password nocatalog auxiliary sys/password@TARGETDB_clone @rman_duplicate_from_backup.cmd log=/home/oracle/rman_duplicate_from_backup.log  &

rman_duplicate_from_backup.cmd

run

{

set until scn 9660022443946;

allocate auxiliary channel d1 device type disk;

allocate auxiliary channel d2 device type disk;

allocate auxiliary channel d3 device type disk;

allocate auxiliary channel d4 device type disk;

allocate auxiliary channel d5 device type disk;

duplicate target database to TARGETDB;

}

Run the shell script  ./ rman_duplicate_from_backup.sh then it will fail with an invalid SCN, however  the output will provide the latest SCN that can be recovered until. Edit the rman_duplicate_from_backup.cmd file and replace the SCN and rerun.

You can get the SCN from the timestamp by this way you are letting Oracle do the work for you..

Restart the shell script and monitor the log file specified in the shell script.

Tidy up

Stop the listener_clone and start a listener for the newly refreshed database.

Remove the files from /app/oradata/rman_backup_dir

Notify the Apps team that the database has been refreshed and the time that the backup finished.