Quick notes on restoring DB to a new host

Quick Notes on Restoring a DB to another host

Just had to do this, so decided to put it here where I know I'll be able to find it.

I had to copy  a V11.2.0.2 database from one linux box to another host, and the new version was 11.2.0.3.

Rman can do this with no issue, but you need to run the upgrade script after the restore. 

The databases were test, and were running in no archive log mode. 

This is what I did.

Shutdown the source database and restart it in mount mode.

Run rman and connect to the target, set the config and backup the controlfile. These are rough notes, you should be able to fill in the gaps.

shutdown immediate

startup mount

rman

connect target /

show all;

configure controlfile autobackup on;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u04/oraexport/orabackup/nmsmopdv_rman_%F';

configure channel device type disk format '/u04/oraexport/orabackup/nmsmopdv_rman_%U';

backup current controlfile;

backup database;

This will create the backup files in the /u04/oraexport/orabackup/ directory

scp the files to the target host. Preferably the same location, but if it is a different location it doesn't matter, there is a command to fix this.

While they are being scp'd, create the new database directories on the target host.

mkdir /u01/oradata/NMSMOPDV

mkdir /u01/app/oracle/admin/NMSMOPDV

mkdir /u02/oradata/NMSMOPDV

mkdir /u04/oradata/NMSMOPDV

mkdir /u05/oradata/NMSMOPDV

Create an initialisation parameter file in the $ORAC:LE_HOME/dbs directory with the relevant file and directory names (control file etc), and create an entry in the oratab for the new database.

On the target, run RMAN, restore the controlfile and then the database. Make sure you set the new database environment with . oraenv

rman

connect target /

startup nomount

restore controlfile from /u04/oraexport/orabackup/nmsmopdv_rman_c-3406859642-20130306-00';

You should see some messages, including these:

output file name=/u01/oradata/NMSMOPTS/NMSMOPTS_control01.ctl

output file name=/u02/oradata/NMSMOPTS/NMSMOPTS_control02.ctl

Mount the database then restore it

alter database mount

restore database;

If you see this:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 03/06/2013 10:19:05

RMAN-06026: some targets not found - aborting restore

RMAN-06023: no backup or copy of datafile 4 found to restore

RMAN-06023: no backup or copy of datafile 3 found to restore

RMAN-06023: no backup or copy of datafile 2 found to restore

RMAN-06023: no backup or copy of datafile 1 found to restore

it means the files haven't been found by rman. You can just do this and reply "yes" at the prompt:

catalog start with '/u04/oraexport';

You should see this:

List of Cataloged Files

=======================

File Name: /u04/oraexport/nmsmopts_rman_c-3406859642-20130306-00

File Name: /u04/oraexport/nmsmopts_rman_c-3406859642-20130306-01

File Name: /u04/oraexport/nmsmopts_rman_03o3r6e3_1_1

and can run the restore again:

RMAN> restore database;

Starting restore at 06-MAR-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u05/oradata/NMSMOPTS/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u05/oradata/NMSMOPTS/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u05/oradata/NMSMOPTS/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u05/oradata/NMSMOPTS/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u05/oradata/NMSMOPTS/NMSMOPTS_sams_op_data01.dbf

channel ORA_DISK_1: reading from backup piece /u04/oraexport/nmsmopts_rman_03o3r6e3_1_1

channel ORA_DISK_1: piece handle=/u04/oraexport/nmsmopts_rman_03o3r6e3_1_1 tag=TAG20130306T093707

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:26

Finished restore at 06-MAR-13

You should now be able to restart the database.

Because I was going from V11.2.0.2 to V11.2.0.3, I needed to do this:

SQL> startup upgrade;

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd

then it restarted normally. Do any tidying like creating an spfile.

Using OS Watcher Black Box

OS Watcher Black Box (formerly OS Watcher) is an Oracle provided tool which monitors the host hardware resources and can be presented as charts to identify potential bottle-necks outside of the database. It is relatively easy to set up, but since it is polling the CPU, memory and disk (using vmstat, iostat etc) it is essential that you advise the client if you intend to use it in Production in case it has an impact.

Go to  supporthtml.oracle.com and search for 301137.1 . 

This is the OSWatcher BB main document.

Download the latest oswbbxxx.tar , where xxx is the release number.

Extract to a directory on the Unix host.

There is an OSWbb_README.txt file in the docs directory which has detailed information.

Use the JAVA installation that comes with Oracle, put this in your PATH for V11:

export PATH=$ORACLE_HOME/jdk/jre/bin:$PATH

If you are using V10 of Oracle, then this should work:

export PATH=$ORACLE_HOME/jre/1.4.2/bin:$PATH

Navigate to the directory where you extracted the oswbb files and run the OSW start script.

I usually use

nohup ./startOSWbb.sh &

otherwise it holds the terminal session. It will advise that it is using defaults, then will start to log the data.

You can check to see if it’s running:

ps –ef | grep –i osw

/usr/bin/ksh ./OSWatcher.sh

/usr/bin/ksh ./OSWatcherFM.sh 48

The files are generated every 20 seconds and kept for 48 hours. If you want to keep them longer you need to specify this when you run startOSWbb.sh –

./startOSWbb.sh 60 72

This would start the tool and collect data at 60 second intervals and keep the last 72 hours of data.

To view the output as charts, you will need to have an X-server session on your PC or enable it on the linux host.

To enable it on the linux host, you may need to log in as root and type

xhost +

If you have an X-Server client installed on your PC (I use Xming), then you either need to set the DISPLAY variable to your ip address (with the suffix :0.0):

export DISPLAY=192.168.3.81:0.0

or in putty you can enable x-forwarding which doesn't need the DISPLAY variable to be set:

Remember to save the session settings.

Once done, you can start the OS Watcher session using this:

java –jar oswbba.jar –i

I usually create a shell script to do this, uncomment the line depending on the database version:

# V11

export PATH=$ORACLE_HOME/jdk/jre/bin:$PATH

# V10

#export PATH=$ORACLE_HOME/jre/1.4.2/bin:$PATH

java –jar oswbba.jar –i /home/oracle/mga/akh/oswbb/archive

The program should read the files in the archive folder, scroll through and parse them, then you should see a menu:

Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs

Enter 6 to Generate All CPU Gif Files
Enter 7 to Generate All Memory Gif Files
Enter 8 to Generate All Disk Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter T to Specify Different Time Scale
Enter D to Return to Default Time scale
Enter R to Remove Currently Displayed Graphs
Enter P to Generate A Profile
Enter A to Analyze Data
Enter Q to Quit Program

Please Select an Option:

These options are fairly self-explanatory.

One thing to note is that if you display the charts, you can’t close them with the “X” – you need to go back into the menu and enter “R”.

To exit, type “Q”.


You can  use the “P” option to generate a profile which creates a folder with the gif files and an OSW_profile.htm file, which presents the charts in a page with some hints.

You can then either zip the files and copy it to a windows PC and open the file in a browser, or use firefox on the host if it is installed:

There is a new feature in this version called “Analyzer”. It can be accessed by entering “A” in the menu and it generates a text file with advice on where bottle-necks may be occurring. It places the file in the analyse directory. Here is an example of the first few lines: 

OSWatcher Black Box Analyzer

Input Archive:  /home/oracle/dba/work/akh/oswbb/archive
Hostname:       LNFSND92
OS Version:     Linux
Snapshot Freq:  30
CPU COUNT:      8

#####################################################################
Section 1: Overall Status

Subsystem       Status
------------------------
CPU             OK
MEMORY          UNKNOWN
I/O             CRITICAL

#####################################################################

Section 2.0: System Slowdown Summary Ordered By Impact

No System Wide Slowdowns Detected

#####################################################################

Section 3: Other General Findings

CRITICAL : Disk high service time observed.

#####################################################################

Section 4: CPU Detailed Findings

CPU RUN QUEUE:
                                       NUMBER  PERCENT
------------------------------------------------------
Snaps captured in archive                  88   100.00
High (>3)                                   0        0
Very High (>6)                              0        0
High spanning multiple snaps                0        0

CPU UTILIZATION: PERCENT BUSY
                                       NUMBER  PERCENT
------------------------------------------------------
Snaps captured in archive                 88   100.00
High (>95%)                                0        0
Very High (100%)                           0        0
High spanning multiple snaps               0        0

You can add this to the index.html file with a bit of manual intervention.

If you want to narrow down the time span of the sample, for example if a user says they noticed a slow-down of the system for a 1 hour period, you can specify this in the menu by selecting “T”:

Enter T to Specify Different Time Scale

      Please Select an Option:T

      Specify Chart Start Time. Valid entry between May 30 09:16:19 2012      and May 30 11:24:22 2012

      Example Format To Enter Time: May 30 09:16:19 2012  :May 30 10:00:00    2012

      Specify Chart End Time. Valid entry between May 30 09:16:19 2012 and    May 30 11:24:22 2012

      Example Format To Enter Time: May 30 11:24:22 2012  :May 30 11:00:00    2012

      Dates accepted. Recalibrating charts...

It will return you to the menu, and any charts or analysis generated will be for the specified time period.

Generate the charts again (6,7,8 from the menu) and create the index.html file the same way as before – the charts will now just show the events through the specified start and end times:

That should get you started with OSW BB.