Database Recovery #5 - RMAN Recover everything

--Restoring a database from scratch.
--These steps remove the entire instance, and the restore and recover from backup pieces outside the FRA. Please be careful
with the remove script.


--Prep database cleanout
sqlplus /nolog
conn / as sysdba
!rm /tmp/drop_orcl.sql
spool /tmp/drop_orcl.sql
select '!rm '||value from v$parameter where name = 'spfile';
select '!rm '||replace(value,'spfile','init') from v$parameter where name = 'spfile';
select '!rm '||name from v$controlfile;
select '!rm '||file_name from dba_data_files;
select '!rm '||member from v$logfile;
select '!rm -fr '||value||'/ORCL' from v$parameter where name = 'db_recovery_file_dest';
spool off


exit


--Take a backup and note the DBID of the database you are going to use to restore the instance.
[oracle@vmorcl backup]$ rman target /


Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 22 20:29:32 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1296288227)

--Set controlfile autobackup ON
RMAN> configure controlfile autobackup on;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>


--Clear out backups and backup the database, make sure SPFILE is backed up
delete noprompt backup;
backup database tag="MASTER_DB" plus archivelog tag="MASTER_LOG" delete input;


--List and copy the backup pieces from the recovery area to another location
list backup;


host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_annnn_MASTER_LOG_7h6ygq1p_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_nnndf_MASTER_DB_7h6ygrg9_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_annnn_MASTER_LOG_7h6yh7t6_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_12_22/o1_mf_s_770589705_7h6yh9gp_.bkp /u01/backup/';


--Check the backups were moved
host 'ls -rtl /u01/backup';


exit


--Once files are in the location, clear out the instance using the spool commands from the prep, and kill the instance.
--MAKE SURE YOU HAVE THE DBID BEFORE FIRING THIS OFF!
sqlplus / as sysdba @/tmp/drop_orcl.sql


--This should throw an error if the instance has be removed.create table sys.test as select * from v$instance;


--shutdown
shutdown abort;
exit;


--Right, the database is GONE. Time to restore and recover.--set DBID
rman target=/
set DBID=1296288227
--You have no spfile or initfile, the only way to startup is using the default init.ora file supplied by oracle
startup nomount force;


--Restore the spfile from the autobackup piece.run {
restore spfile from '/u01/backup/o1_mf_s_770589705_7h6yh9gp_.bkp';
}
shutdown abort;


--Startup using the restored spfile 
startup nomount;


--Restore the control file from the same autobackup piece
run {
restore controlfile from '/u01/backup/o1_mf_s_770589705_7h6yh9gp_.bkp';
}


--Mount the database with the new found control files.
alter database mount;


--Add the rest of the backup pieces to the catalog
catalog start with '/u01/backup/';


--Restore the database to its former glory.
--Get the last sequence number.


list backup of archivelog all;


--Restore the database. Set the UNTIL SEQUENCE clause to the highest sequence + 1


run {
    set until sequence 3;
    restore database;
    recover database;
    }


--Be a hero - open the database with reset logs since we used a backup control file.


alter database open resetlogs;


--Once the database is  open, it is important to take a NEW backup!


backup database tag="MASTER_DB" plus archivelog tag="MASTER_LOG" delete input;


--Done. To handle the password file, either create a new one, or copy the one from the original hosts app tree.

Database Recovery #4 - RMAN Recover control file without FLASH_RECOVERY_AREA

--Open rman
rman target=/
--Ensure autobackup is set to on
configure controlfile autobackup on;


--Take note of the DBID, delete backups, make a new backup of the datbase and logs. 
backup database tag='MASTER_DB' plus archivelog tag='MASTER_LOGS' delete input;


--Ensure you have a copy of the controlfile
list backup of controlfile;

exit;


--Move autobackup off to the a new location OUT OF THE instance's FLASH_RECOVERY_AREA
--%F is not just a filename format. It means FOLDERS as well. /FRA/{%F} = /FRA/{ORCL/AUTOBACKUP/YYYYMMDD}
mv /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_12_20/o1_mf_s_770381286_7h0ly6sk_.bkp /u01/backup/ORCL/autobackup/2011_12_20


--Connect to instance and remove the control files from the database
sqlplus /nolog
conn / as sysdba


spool /tmp/delete_controlfile.sql
select '!rm '||name from v$controlfile;
spool off


--Drop controlfiles
@/tmp/delete_controlfile.sql


--Shutdown abort
shutdown abort;
exit;


--Reconnect to RMAN to recover the database
rman target /


--Startup database in NOMOUNT state and set the DBID (DBID is used when no FRA is available)
startup nomount;
set dbid=1296288227


--Restore the controlfile from the relocated autobackup piece from the new FRA location
restore controlfile from autobackup db_recovery_file_dest='/u01/backup';


--Mount, recover and open resetlogs the instance. 
alter database mount;
recover database;
alter database open resetlogs;

--Delete backups from previous incarnation
delete noprompt backup;

--Take a new backup
backup database tag='MASTER_DB' plus archivelog tag='MASTER_LOGS' delete input;

Database Recovery #3 - Recover from cold backup using RMAN

--Take cold backup with RMAN
rman target=/
shutdown immediate;
startup mount;
backup database plus archivelog delete input;
alter database open;

exit;


--Prep the delete script to remove *.dbf files
sqlplus /nolog
conn / as sysdba


spool /tmp/remove_data_files.sql
select '!rm '||file_name from dba_data_files;
spool off
shutdown abort;
--DESTROY datafiles
@/tmp/remove_data_files.sql
exit;
--Try to startup the database
sqlplus /nolog
conn / as sysdba
startup;
--SQL> startup;
--ORACLE instance started.
--Total System Global Area  389189632 bytes
--Fixed Size                  1336736 bytes
--Variable Size             226495072 bytes
--Database Buffers          146800640 bytes
--Redo Buffers               14557184 bytes
--Database mounted.
--ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
--ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
shutdown abort;
exit;


--Restore and recover the database from the cold backup
rman target=/
startup mount;
restore database;
recover database;
--Be a hero
alter database open;

Autostart Script for Linux

If you want your database to startup when the server starts up, as well as shutdown when the server shuts down - log in as root and carry out the following steps.


Create the following file
/etc/init.d/dbora


Add the following lines:
#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.


ORA_HOME=/app/oracle/product/10.2.0/se
ORA_OWNER=oracle


if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi


case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
        su - $ORA_OWNER -c $ORA_HOME/bin/dbstart
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c $ORA_HOME/bin/dbshut
        su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
        rm -f /var/lock/subsys/dbora
        ;;
esac

Change the file permissions:

chmod 750 /etc/init.d/dbora

Add the file as service:

chkconfig --add dbora

Test (will shutdown abort and startup all database in the /etc/oratab file, so be careful):

 /etc/init.d/dbora stop

 /etc/init.d/dbora start

Create a recovery catalog #1

Creating and using a dedicate recovery catalogue has benefits over a standard control file based catalogue.
-A single location for RMAN info
-Recovery views are well documented so can one can write reports
-Makes global scripting easier as you can store scripts for all databases
-Extra options like keep forever or keep records for longer than a year

To create a recovery catalogue we need a new database. 

#Edit the vi tab and add a new database line
vi /etc/oratab
orcl:/u01/app/oracle/product/11.1.0/db_1
rcat:/u01/app/oracle/product/11.1.0/db_1
utf:/u01/app/oracle/product/11.1.0/db_1


. oraenv
rcat


sqlplus /nolog
conn / as sysdba

--CREATE RECOVERY DATABASE

STARTUP NOMOUNT

CREATE DATABASE rcat

   USER SYS IDENTIFIED BY SYS

   USER SYSTEM IDENTIFIED BY SYS

   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/rcat/redo01.log') SIZE 100M,

           GROUP 2 ('/u01/app/oracle/oradata/rcat/redo02.log') SIZE 100M,

           GROUP 3 ('/u01/app/oracle/oradata/rcat/redo03.log') SIZE 100M

   MAXLOGFILES 5

   MAXLOGMEMBERS 5

   MAXLOGHISTORY 1

   MAXDATAFILES 100

   MAXINSTANCES 1

   CHARACTER SET WE8ISO8859P15

   NATIONAL CHARACTER SET AL16UTF16

   DATAFILE '/u01/app/oracle/oradata/rcat/system01.dbf' SIZE 325M REUSE EXTENT MANAGEMENT LOCAL

   SYSAUX DATAFILE '/u01/app/oracle/oradata/rcat/sysaux01.dbf' SIZE 325M REUSE

   DEFAULT TABLESPACE users 

      DATAFILE '/u01/app/oracle/oradata/rcat/users01.dbf' SIZE 200M REUSE EXTENT MANAGEMENT LOCAL

   DEFAULT TEMPORARY TABLESPACE temp

      TEMPFILE '/u01/app/oracle/oradata/rcat/temp01.dbf' 

      SIZE 100M REUSE

   UNDO TABLESPACE undots 

      DATAFILE '/u01/app/oracle/oradata/rcat/undots01.dbf'

      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 300M;

CONNECT SYS/password AS SYSDBA

-- create a user tablespace to be assigned as the default tablespace for users

CREATE TABLESPACE users LOGGING 

     DATAFILE '/u01/oracle/oradata/mynewdb/users01.dbf' 

     SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 

     EXTENT MANAGEMENT LOCAL;

-- create a tablespace for indexes, separate from user tablespace

CREATE TABLESPACE indx LOGGING 

     DATAFILE '/u01/oracle/oradata/mynewdb/indx01.dbf' 

     SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 

     EXTENT MANAGEMENT LOCAL;

--Create catalog, views and procedures

@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catalog.sql

@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catproc.sql

exit

--Create recovery catalog tablespace

create tablespace rcat_data

datafile '/u01/app/oracle/oradata/rcat/rcat_data.dbf' size 60m autoextend on next 10m maxsize 200m;

--Create recovery catalog user

create user rcat_user identified by rcat_user 

default tablespace rcat_data

quota unlimited on rcat_data;

--Grant privileges

grant recovery_catalog_owner to rcat_user;

--Create catalog

[oracle@vmorcl u01]$ rman catalog=rcat_user/rcat_user

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Dec 13 06:43:06 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN>

--Now that the catalogue has been created, we can make use of the catalogue views that come with the catalogue

All views start with “RC_”
-RC_DATABASE
-RC_BACKUP_PIECE
-RC_PIECE_DETAIL
-RC_ARCHIVED_LOG
-RC_BACKUP_ACRHIVELOG_SUMMARY
-RC_BACKUP_SET
-RC_BACKUP_SET_DETAILS
-RC_BACKUP_SPFILE

JVM Controllers and Pooling

Are your 11g frmweb.exe processes consuming too much memory in a Windows 2008 x64 environment? Noticed that those processes are using webutil?

Heres a short story.
There are issues with Webutil and a Win x64 environement. An SR was opened with Oracle. The guide below tells each forms process to use the "community" JVM instead of spawning its own.
**UPDATE: Oracle will not continue to work on this request. It is closed. It is considered normal.

The default behaviour is for each session to start and maintain its own JVM.

For an idea of what the heck a JVM controller is on an application server click here (diagrams included)

To interface with a JVM Controller click here

You can either administrator JVM controllers via Middleware EM (7001) or you can use the cmd line on the server.

Enterprise Manager:

http://mymiddlewareserver:7001/em
Logon to enterprise manager, expand the forms folder and rclick on "forms". Select JVM Configuration

 On the JVM Configuration, choose "Create Like" , input "testingJvm" and select "EXAMPLE" as the jvm to copy, then complete by clicking the "Create" button.



For JVM options : -Xms1024m -Xmx2048m for good measure.
Classpath : Basically the same classpath as the one found inside your application server environment file. D:\Oracle\Middleware\11gfr\forms\j2ee\frmsrv.jar;D:\Oracle\Middleware\11gfr\jlib\ldapjclnt11.jar;D:\Oracle\Middleware\11gfr\jlib\debugger.jar;D:\Oracle\Middleware\11gfr\jlib\ewt3.jar;D:\Oracle\Middleware\11gfr\jlib\share.jar;D:\Oracle\Middleware\11gfr\jlib\utj.jar;D:\Oracle\Middleware\11gfr\jlib\zrclient.jar;D:\Oracle\Middleware\11gfr\reports\jlib\rwrun.jar;D:\Oracle\Middleware\11gfr\forms\java\frmwebutil.jar;D:\Oracle\Middleware\11gfr/jlib/start_dejvm.jar;D:\Oracle\Middleware\11gfr\opmn\lib\optic.jar;D:\Oracle\Middleware\11gfr\forms\java\jacob.jar;D:\Oracle\Middleware\11gfr\forms\java\frmall.jar

User count : 200 - also for good measure.
Logdir : D:\Oracle\Middleware\Forms\FRComponent\frcommon\tools\jvm\log

Logging : "ON"



And thats it, the next step is to navigate to JVM Controller, select your control and click start.

One last step is required to take advantage of your new JVM Controller. Open formsweb.cfg and add the following parameter to the otherparameters option:

jvmcontroller=testingJvm

All sessions using the configuration under which this option is set will use less memory when needing to use a JVM.Save formsweb.cfg.

Your otherparameters will look like this: otherparams=jvmcontroller=testingJvm


JVM Controller command line
If you want to control DEJVM, you should make use of the cmd line options for JVM Controllers.


It's really simple, open a command window and run the following commands, dont forget to adjust the paths for your environment:

echo Set your home and instance info.
set ORACLE_HOME=D:\Oracle\Middleware\11gfr
set ORACLE_INSTANCE=D:\Oracle\Middleware\Forms

echo Stop any dejvm which might be running with specified name.
dejvm -stop jvmcontroller=testingJvm

echo Start testingJvm
dejvm -start jvmcontroller=testingJvm jvmoptions="-Xms256m -Xmx512m" maxsessions=200 classpath=D:\Oracle\Middleware\11gfr\forms\j2ee\frmsrv.jar;D:\Oracle\Middleware\11gfr\jlib\ldapjclnt11.jar;D:\Oracle\Middleware\11gfr\jlib\debugger.jar;D:\Oracle\Middleware\11gfr\jlib\ewt3.jar;D:\Oracle\Middleware\11gfr\jlib\share.jar;D:\Oracle\Middleware\11gfr\jlib\utj.jar;D:\Oracle\Middleware\11gfr\jlib\zrclient.jar;D:\Oracle\Middleware\11gfr\reports\jlib\rwrun.jar;D:\Oracle\Middleware\11gfr\forms\java\frmwebutil.jar;D:\Oracle\Middleware\11gfr/jlib/start_dejvm.jar;D:\Oracle\Middleware\11gfr\opmn\lib\optic.jar;D:\Oracle\Middleware\11gfr\forms\java\jacob.jar;D:\Oracle\Middleware\11gfr\forms\java\frmall.jar logdir=D:\Oracle\Middleware\Forms\FRComponent\frcommon\tools\jvm\log logging=on




Things to consider:Restarting your weblogic server will not kill the dejvm process, it will soldier on. So if you have a scheduled WLS_FORMS restart on the cards, I would recommend taking the above commands and adding them along with your service restart routines. By default, should a DEJVM process not be running when a forms process requires it, the DEJVM specified inside the session config file will be started automatically.  


Also:IMPORTANT NOTE:  Before restarting the Oracle WebLogic managed server, all the JVM Controller processes (dejvm) started by that server must be stopped. Otherwise, WLS_FORMS will not restart after a shutdown. See Stop dejvm Before Stopping and Restarting WLS_FORMS

Cheers

Database Recovery #2 - Inactive redo log member (offline)

The following guide is for when your instance losses an INACTIVE REDO LOG MEMEBER file while the instance is offline. For online, see DATABASE RECOVERY #3.


--Firstly shutdown your instance neatly to perform a cold backup.
sqlplus '/ as sysdba' 
shutdown immediate;


--Clear out any old backups previously taken from the backup locations
!rm /u01/backup/*
!rm /u01/backup/log/*
!rm /redologs/stage1/*


--Make a brand new cold backup of your files, along with your log files
!cp /u01/app/oracle/oradata/orcl/*  /u01/backup/
!cp /redologs/stage1/* /u01/backup/log


--Startup the database and switch some logs
startup;
alter system switch logfile;


--Delete an inactive group.
set lines 200
select '!rm /u01/app/oracle/oradata/orcl/redo0'||group#||'.log' "DELETE",
       'alter database add logfile group '||group#||' ''/u01/app/oracle/oradata/orcl/redo0'||group#||'.log'' size 100M;' "CREATE"
  from v$log where status = 'INACTIVE' and rownum < 2;


shutdown immediate;


--Run the DELETE command generated by sql statement above
--!rm /u01/app/oracle/oradata/orcl/redo03.log


--Startup the database, instance will shutdown if you try to perform regular start.
startup mount;


--Drop the group
alter database drop logfile group 3;


--Recreate the group
--Run the CREATE command generated by sql statement above
--alter database add logfile group 3 '/u01/app/oracle/oradata/orcl/redo03.log' size 100M;


--Open the database
alter database open;

Database Recovery #1 - System and Undo datafiles

Over the weeks I will be posting exercises to demonstrate database recovery after various disasters.

In summary, the basic form of each article will be:
-A cold back
-Disaster
-Recovery


The following steps are a guide to complete recovery after the loss of a system datafile.

--Firstly shutdown your instance neatly to perform a cold backup.
sqlplus '/ as sysdba'
shutdown immediate;

--Clear out any old backups previously taken from the backup locations !rm /u01/backup/*.dbf
!rm /u01/backup/log/*
!rm /redologs/stage1/*

--Make a brand new cold backup of your files, along with your log files
!cp /u01/app/oracle/oradata/orcl/* /u01/backup/
!cp /redologs/stage1/* /u01/backup/log

--Startup the database and switch some logs startup;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

--Check current scn information inside the database, datafiles, redolog files and archived log files
set lines 200
column change# format 999999999999999
column current_scn format 999999999999999
column next_change# format 999999999999999
column name format a40

select current_scn from v$database;
select file#, status, enabled, checkpoint_change# from v$datafile;
select sequence#, status, first_change#, next_change# from v$log;
select name, sequence#, first_change#, archived from v$archived_log;
select change# from v$recover_file;

--Labour the database: Create a second session on the instance and run the following to generate activity.
--While the loop is running, run the step following the pl/sql block.
drop table sys.database_objects;

create table sys.database_objects as select * from dba_objects where rownum < 1;

declare
  w_object_count number;
begin
  --basic work load to generate redo
  for x in 1..30 loop
  --count objects in table and output to screen
    select count(*)
      into w_object_count
      from sys.database_objects;
    dbms_output.put_line(w_object_count);

    --delete and repopulate
    delete from sys.database_objects;
    insert into sys.database_objects
    select * from dba_objects;

    dbms_lock.sleep(2);

  end loop;
end;
/


--Break the instance,
--PLEASE DONT RUN THE FOLLWING UNLESS YOU ARE ABSOLUTELY SURE YOU ARE ON A PRACTICE DATABASE --!rm /u01/app/oracle/oradata/orcl/system01.dbf

--Watch the log for errors. You should see ORA-27041 at some point when the DBW process tries to write to system01.dbf. !tail -f -n 100 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

----------Error to expect 
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'
ORA-27041: unable to open file
----------

--The instance shutdowns down. Restore datafile and mount the database. 
!cp /u01/backup/system01.dbf /u01/app/oracle/oradata/orcl/system01.dbf
startup mount;

--Check the available SCN information to see the disparity in database files. 
set lines 200
column change# format 999999999999999
column current_scn format 999999999999999
column next_change# format 999999999999999
column name format a40

select current_scn from v$database;
select file#, status, enabled, checkpoint_change# from v$datafile;
select sequence#, status, first_change#, next_change# from v$log;
select name, sequence#, first_change#, archived from v$archived_log;
select * from v$recover_file;

--Recover database
recover datafile 1;

--Open database
alter database open;

--Transact
alter system checkpoint;
alter system switch logfile;
alter system checkpoint;

--Check all the SCN values again to ensure system01.dbf is up to date and no longer needs recovering. Also check the log the the system switchlogile and wrote a check point 
set lines 200
column change# format 999999999999999
column current_scn format 999999999999999
column next_change# format 999999999999999
column name format a40

select current_scn from v$database;
select file#, status, enabled, checkpoint_change# from v$datafile;
select sequence#, status, first_change#, next_change# from v$log;
select name, sequence#, first_change#, archived from v$archived_log;
select * from v$recover_file;

!tail -f -n 100 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log