Monday, December 18, 2017

Back to Basics - Keeping an RMAN backup and restoring a database to another host

A user requested a database backup that needed to be kept for 3 months and then restored.

Oh. Gee. Not had to do this for a while.

This was a non Prod database and backups are taken every night and only one copy kept. So the first thing I had to do was make sure the backups didn't get removed.

A bit of googling and this is the script I created:

run {
allocate channel d1 device type disk;
allocate channel d2 device type disk;
backup format '/backup/oraflash/UIQUT1/rman/keep/%u_%s_%p'
keep until time 'sysdate+100'
current controlfile
format '/backup/oraflash/UIQUT1/rman/keep/uiqut1_ctrl_%u.rman'
keep until time 'sysdate+100'
format '/backup/oraflash/UIQUT1/rman/keep/spfile_%u_%s_%p'
keep until time 'sysdate+100'
plus archivelog
format '/backup/oraflash/UIQUT1/rman/keep/arc_%u_%s_%p'
keep until time 'sysdate+100';

The first time I did this I only had the 'keep until time' as the last line - and it only kept the archived logs and removed the other backups - so you need it for each backup.

I checked it the next day and the files were all there.

You should also change the parameter 'control_file_record_keep_time' to a relevant figure depending on how long you want to keep the backups - not essential because the backup files can be recatalogued, but worthwhile.

I ran a 'restore database preview' which verified that the files were OK.

However, the user was pretty adamant that it was essential that the database could be restored down the line - and the only way I could be sure was to actually do it. I obviously couldn't overwrite the existing database, so I tarred up the backup files and scp'd them to another host.

Once there, I had some preparation to do

I created the directory - not 100% sure this is necessary, but worth doing

mkdir  /backup/oraflash/UIQUT1/rman/keep

and moved the tar file there and extracted it.

I also copied the init.ora file to the $ORACLE_HOME/dbs directory and checked the paths of the controlfiles, log_arch_dest and audit dump dest, and created directories for those if they didn't exist

mkdir /app/oracle/admin/UIQUT1
mkdir /app/oracle/admin/UIQUT1/adump



There was also a local listener parameter that I removed.

I added an entry to the oratab and set the database name with . oraenv

So the process to restore a backup like this is

1. Startup nomount
2. Restore controlfile
3. Alter database mount
4. Restore datafiles
5. Recover database
6. Open resetlogs

But there are some more things to do in between.

First off though, restore the controlfile:

The script backed up the controlfile to a file with this format - uiqut1_ctrl_%u.rman - so find that file and restore it

rman target/
restore controlfile from '/backup/oraflash/UIQUT1/rman/keep/uiqut1_ctrl_bhsmcleg.rman';

It should do the job.

Mount the database (in RMAN)

alter database mount;

You need to tell RMAN about the backups

catalog start with ''/backup/oraflash/UIQUT1/rman/keep/'

This should run through and register the files. If you 'list backup of database;' it should show the files.

An added complication here is that the host I am restoring to uses ASM, but the source database doesn't. Not really a big deal, just have to generate the new file names. If you don't need to do this, just create the directories like the host server.

In SQLPlus:

set pages 9999
set lines 200
select 'set newname for datafile '''||name||''' to '''||replace(name, '/dat/oradata/UIQUT1/', '+STSORA050_DATA_01')||''';' FROM V$DATAFILE;

This will generate something like this:

 set newname for datafile '/dat/oradata/UIQUT1/system01.dbf' to '+STSORA050_DATA_01/UIQUT1/system01.dbf';
set newname for datafile '/dat/oradata/UIQUT1/sysaux01.dbf' to '+STSORA050_DATA_01/UIQUT1/sysaux01.dbf';
set newname for datafile '/dat/orasys/UIQUT1/undotbs01.dbf' to '+STSORA050_DATA_01/UIQUT1/undotbs01.dbf';

Do the same for the TEMPFILEs (change V$DATAFILE to V$TEMPFILE)

Keep the output, you'll need it later.

Do the same for the redo logs :

alter database rename file '/dat/redoa/UIQUT1/redo03a.log' to '+STSORA050_DATA_01/UIQUT1/redo03a.log';
alter database rename file '/dat/redob/UIQUT1/redo03b.log' to '+STSORA050_DATA_01/UIQUT1/redo03b.log';

This is all that is needed.

Create a restore.rcv script and put this in it (if you need to rename the files - if not, just have the restore and recover commands)

run {
set newname for datafile '/dat/oradata/UIQUT1/system01.dbf' to '+STSORA050_DATA_01/UIQUT1/system01.dbf';
set newname for datafile '/dat/oradata/UIQUT1/sysaux01.dbf' to '+STSORA050_DATA_01/UIQUT1/sysaux01.dbf';
set newname for datafile '/dat/orasys/UIQUT1/undotbs01.dbf' to '+STSORA050_DATA_01/UIQUT1/undotbs01.dbf';
set newname for datafile '/dat/oradata/UIQUT1/users01.dbf' to '+STSORA050_DATA_01/UIQUT1/users01.dbf';
. (all the files from the rename command)
set newname for tempfile '/dat/orasys/UIQUT1/temp01.dbf' to '+STSORA050_DATA_01/UIQUT1/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
recover database;

Run it like this:

nohup rman target / cmdfile='/backup/oraflash/UIQUT1/rman/keep/restore.rcv' log='/backup/oraflash/UIQUT1/rman/keep/restore.log' &

This will ensure it will keep going if your session disconnects.

Once complete, you might see this at the end of the logfile

RMAN-03002: failure of recover command at 12/19/2017 11:27:29
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 146352 and starting SCN of 505408658

Doesn't matter, just means it has applied the last log that was backed up.

Go to SQLPlus, rename the log files, and open the database

alter database rename file '/dat/redoa/UIQUT1/redo03a.log' to '+STSORA050_DATA_01/UIQUT1/redo03a.log';
alter database rename file '/dat/redob/UIQUT1/redo03b.log' to '+STSORA050_DATA_01/UIQUT1/redo03b.log';

alter database open resetlogs;

Hopefully it will open.

All good.

Monday, August 21, 2017

Running Oracle in a Docker Container - Part 3

OK, my apologies - I have 2 blog posts on this and nowhere does it get to the actual point of running Oracle in a Docker Container. I got caught up with real work and although I have done it and documented it, I haven't got around to posting here until now.

So, this is what you've been waiting for!

Using a container with an Oracle database

A search for 'oracle' shows quite a few entries, including a few that have databases already installed:

I downloaded the 11g one to test:

I started it up (ignore the different sessions)

but there were some issues – vi wasn’t installed so I couldn’t edit files, plus the listener.ora pointed to a hostname that changes every time the container is restarted. 

Install vi:

I tried to force the hostname by editing /etc/hostname and /etc/hosts but it didn’t work, but I noticed that every time the container restarted it added an entry –, so I put that in the listener.ora and it worked:

I then tried starting the database:

It started fine, but was pretty slow. The Mac Docker app can change how much memory can be allocated, so I stopped the container, changed it, and restarted it.

But before that, I committed the container so that the changes would be saved:

I also uploaded it to my repository:

The upload took a while and timed out a few times, I had to retry – but it knows where it got to so continued.

So there, at last, you have Oracle XE running in a Docker container!

Connecting to the database with SQLDeveloper

A connection with SQL Developer is easy – but you need to restart the container and map a port:

Start the listener and database:

Then start SQLDeveloper and use these connection details (I created an ‘andy’ user in the database):

Even though the IP address in the container is, you need to use here.

You can then connect.

REMEMBER: Any database updates will be lost unless you commit the changes in the container.