Switch Database to RMAN Image Copy Backup and Switch It Back

Considering RMAN image copy backup for your production databases in your backup strategy is a very helpful approach to easily recover from disk failure scenarios.

Why consider RMAN image backups in your backup strategy:
It helps you fix disastrous situations due to physical damages, such as losing one or more datafiles in a minimal downtime, by eliminating the need for restoring the datafile from scratch.
Note: You should keep a recent RMAN image copy on the disk all the time to help minimize the downtime during physical damage situations.
This script can help you out in creating RMAN image copy backup:
https://www.dropbox.com/s/umempc88srgr8k8/schedule_rman_image_copy_bkp.sh?dl=0
You have to change the variables inside the script as instructed by the comments to match your environment.

When to switch to the image backup:
1-You lost a disk contains one datafile or more.
2-A datafile has been deleted or mistakenly overwritten on the OS level.
3-A datafile has a wide physical corruption.
4-You want to move your database datafiles to a different location on the same server with a minimal downtime.

In this demonstration I'll go through two real-world scenarios:
Scenario #1 is related to switching the whole database to the RMAN image backup.
Scenario #2 is for switching one datafile only to the RMAN image backup.

Scenario #1 Switching the whole database to "RMAN copy backup": [on Windows Platform]

> For this scenario, let's take first an RMAN backup as copy for the whole database:
RMAN> backup as copy database;

For more information about Why to consider RMAN image copy, How to use it and How to recover an RMAN image copy from incremental backup, visit this link:
http://dba-thoughts.blogspot.ae/2014/08/recover-rman-image-copy-backup.html

Now let's suppose you lost your datafiles and want to switch to the RMAN image copy:
>Re-bounce the database to the mount mode:
RMAN> shutdown immediate;
RMAN> startup mount;

Switch the database to the image copy backup:
Connect to the RMAN:
# rman target /
RMAN> switch database to copy;
datafile 1 switched to datafile copy "D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSTEM_7BZ8WZ7S_.DBF"
datafile 2 switched to datafile copy "D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSAUX_7BZ8ZZD8_.DBF"
datafile 3 switched to datafile copy "D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7BZ93XM6_.DBF"
datafile 4 switched to datafile copy "D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_USERS_7BZ963HC_.DBF"
datafile 5 switched to datafile copy "D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_EXAMPLE_7BZ95N9Z_.DBF"

RMAN> recover database;

This will recover the database until the time of failure, this means that the archives from the time of last RMAN copy backup till the time of the failure must exist.
e.g. If the RMAN copy backup has been taken at 1:00am and the failure happen at 9:00am the archives between 1:00 to 9:00am should be available plus the current database redologs.

RMAN> alter database open;

The case of Incomplete Recovery:
In case you lost one or more archive without having a backup for them, you will be forced to recover to the last available archive using:
SQL> RECOVER DATABASE UNTIL CANCEL;
AUTO
Or from RMAN:
RMAN> run{
                    set until sequence xxx;   # Where xxx is the last higher available archivelog sequence.
                     recover database;
                     alter database open resetlogs;
                    }

>Datafiles are now pointing to the RMAN copy backup:
SQL> select file_name from dba_data_files;
FILE_NAME
-----------------------------------------------------------------------------------------------------
D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_USERS_7BZK07B2_.DBF
D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7BZJYX5S_.DBF
D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSAUX_7BZJWLDN_.DBF
D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSTEM_7BZJT6RF_.DBF
D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_EXAMPLE_7BZK000C_.DBF

Note: Temporary tempfiles will not be switched to the image backup as they been never considered during the backup time, so you just need to re-add them back.

Switch back the database to its original files: [In case you managed to restore back the original datafiles]

Datafiles should exist in their original places, whatever was the way you used to copy them back.
You have to use "rename file" command for each datafile to point them to the original location:

>First you have to restart the database to the mount mode:
SQL> shu immediate;
SQL> startup mount;

>Second run the rename command to point to datafile in the original location:
SQL> alter database rename file 'D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_USERS_7BZK07B2_.DBF' to 'D:\ora11g\oradata\orcl\USERS01.dbf';

SQL> alter database rename file 'D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_UNDOTBS1_7BZJYX5S_.DBF' to 'D:\ora11g\oradata\orcl\UNDOTBS01.dbf';

SQL> alter database rename file 'D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSAUX_7BZJWLDN_.DBF' to 'D:\ora11g\oradata\orcl\SYSAUX01.dbf';

SQL> alter database rename file 'D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_SYSTEM_7BZJT6RF_.DBF' to 'D:\ora11g\oradata\orcl\SYSTEM01.dbf';

SQL> alter database rename file 'D:\ORA11G\FLASH_RECOVERY_AREA\ORCL\DATAFILE\O1_MF_EXAMPLE_7BZK000C_.DBF' to 'D:\ora11g\oradata\orcl\EXAMPLE01.dbf';

Note: You can re-create the controlfile pointing the datafiles to their old location instead of renaming the datafiles one by one.

Recover the database:
RMAN> recover database;

Open the database:
RMAN> alter database open;

Now the datafiles are pointing to its original location:
SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------
D:\ORA11G\ORADATA\ORCL\USERS01.DBF
D:\ORA11G\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORA11G\ORADATA\ORCL\SYSAUX01.DBF
D:\ORA11G\ORADATA\ORCL\SYSTEM01.DBF
D:\ORA11G\ORADATA\ORCL\EXAMPLE01.DBF


Scenario #2 Switch one datafile to the RMAN copy backup: [on Linux Platform]

The same practice you can do if you lost one or more datafiles:

Let's suppose we lost datafile# 4 which is part of tablespace users and you're getting this error in the alertlog:

ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/ora_dev1/LEGACY/datafiles/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory

If you have a recent RMAN "backup as copy" for the database or for the lost datafile, you can switch that datafile to its copy piece in the image backup, by doing the following:

>Take the lost datafile offline:
# rman target /
RMAN> sql 'alter database datafile 4 offline';

>Switch the datafile to it's Copy backup piece:
RMAN> switch datafile 4 to copy;

Datafile 4 switched to datafile copy "/ora_dev1/LEGACY/copy/LEGACY_766159463_55"

>Recover the datafile:
RMAN> recover datafile 4;

>Bring back the datafile online:
RMAN> sql 'alter database datafile 4 online';

Now the datafile location will point to the rman "copy backup" piece:

SQL> select tablespace_name,file_name,status from dba_data_files where file_id=4;

TABLESPACE_NAM FILE_NAME STATUS
-------------- -------------------------------------------- ---------
USERS /ora_dev1/LEGACY/copy/LEGACY_766159463_55 AVAILABLE

DONE.

Note: For the above scenario to succeed, all archive logs from the time of image copy backup creation until the time of failure should exist.

Synchronize | Refresh Standby Database From The Primary Database Using RMAN Incremental Backup

You need to synchronize a standby DB from an incremental RMAN backup in these two cases:

1-There is a big gap between the primary database and the standby database, whereas copying and applying the archives from Production to DR site will take a long time.

2-You lost one or more archive log file on the primary site that needed for the recovery of the standby database or nologging transactions have been run on the primary database.

In general the advantages of recovering the standby database from an incremental backup over the recovery from arvhivelogs is that Incremental backup will recover the standby database much faster than applying the archives, incremental backup apply only the COMMITTED transactions on the standby database while archives will apply the COMMITED & NON-COMMITTED transactions then ROLLBACK the non-committed transactions the thing makes the process much longer than recovering from RMAN incremental backup.

Note: The following demonstration done on 11g database [Linux OS platform]...

On 11g and older versions:

Step1: On The DR Site:
------  ------------------
Check the current SCN on the standby DB:

DEV > select to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
------------------------------
1552405397


Step 2: On The Primary Site:
-------   ----------------------
Create a standby control file:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/control/standby_control.ctl';

Move that new standby controlfile to the DR site:
#scp /home/oracle/control/standby_control.ctl oracle@dr:/ora_dr1/backup_inc

Create an incremental backup:

Take an incremental backup starting from the standby SCN -The one we checked in Step1-:
RMAN> Backup incremental from SCN 1552405397 database tag='FOR_STANDBY' format '/backupdisk/%d_%t_%s_%p';

Move the backup pieces to the DR site:
#scp /backupdisk/* oracle@dr:/ora_dr1/backup_inc


Step 3: On The DR Site:
-------  ------------------
Shutdown the standby database:

SQL> Shu immediate;

Rename the original control file of  the standby database:
# cp /ora_dr1/control/control01.ctl /ora_dr1/control/control01.ctl.sav

Restore the standby control file we just copied from the primary site:
Make sure to copy it to the right path of the standby DB control file and duplicate it if you have more than one control file path in the standby DB spfile:

# cp /ora_dr1/backup_inc/standby_control.ctl   /ora_dr1/control/control01.ctl

Startup the Standby database with the new controlfile:

SQL> startup mount;

Catalog the incremental backup pieces -which we moved from the Primary site- in the standby DB RMAN catalog to let the standby DB consider that backup piece during recovery process:

#rman target /

RMAN> catalog start with '/ora_dr1/backup_inc/' noprompt;

Recover the standby database from the incremental backup: -From RMAN-


RMAN> RECOVER DATABASE noredo;

Or:

RMAN> RECOVER DATABASE FROM TAG for_standby;

Now your standby database is refreshed from the incremental backup. You can start the Managed Recovery process on the standby DB:

SQL> recover managed standby database disconnect;

Done.

 

[Update: On 07-08-2014]

on 18c and above:

With one command, you can get the job done:

On standby DB: [From mount mode]

 
RMAN> RECOVER STANDBY DATABASE FROM SERVICE primary_tns USING COMPRESSED BACKUP;

 

Notes: 

- Service "primary_tns" should exist in the standby server tnsnames.ora file pointing to the primary DB.

- Including "USING COMPRESSED BACKUP" will be wise, to send the packets in a compressed format to save the bandwidth and transfer the packets faster through the network.

 
Ref: http://www.toadworld.com/platforms/oracle/w/wiki/10565.recovery-manager-rman-enhancements-in-oracle-database-12c.aspx

View Hardware & OS Statistics Using V$OSSTAT View

While I was checking the audit logs for a database I found a log tracking EM activities, I take a look inside and found it gathers some information on HW & OS using a view called v$osstat, I look at that view and found it will be helpful if  I include it in the database health check script.

In this view you can see the main server hardware info + Operating System statistics:

SQL> select stat_name,value from v$osstat;

STAT_NAME VALUE
----------- ------
NUM_CPUS 8 ->Available CPUs in the machine.
IDLE_TIME 1708663701 ->Idle time for ALL CPUs combined [Since the instance startup].
BUSY_TIME 11490541 ->Busy time for the CPUs combined [Since the instance startup].
USER_TIME 10565287 ->Time spent in User activities [Since the instance startup].
SYS_TIME 733707 ->Time spent in kernel activities [Since the instance startup].
IOWAIT_TIME 8517935 ->Time spent waiting for I/O to complete [Since the instance startup].
NICE_TIME 20455 ->Time spent in "low priority users"activities [Since the instance startup].
RSRC_MGR_CPU_WAIT_TIME 0 ->I don't know! -For the time being-
LOAD .009765625 ->Processes number waiting on the "run queue" = uptime command in Linux [Getting updated instantly]
NUM_CPU_CORES 8 ->Number of CPU cores.
NUM_CPU_SOCKETS 2 ->Number of Physical CPUs.
PHYSICAL_MEMORY_BYTES 8372563968 ->Physical memory size -in Bytes-.
TCP_SEND_SIZE_MIN 4096 ->>The rest are NICs related activities...
TCP_SEND_SIZE_DEFAULT 16384
TCP_SEND_SIZE_MAX 4194304
TCP_RECEIVE_SIZE_MIN 4096
TCP_RECEIVE_SIZE_DEFAULT 87380
TCP_RECEIVE_SIZE_MAX 4194304
GLOBAL_SEND_SIZE_MAX 1048576
GLOBAL_RECEIVE_SIZE_MAX 4194304

Fixing Migrated/Chained Rows

Definitions:

Row Chaining: A row is too large to fit into a single database block. For example, if you use 8 KB block size for your database, and you need to insert a row of 16 KB into it, Oracle will use 2 blocks and store the row in pieces.
Row Chaining is often unavoidable with tables have (LONG, large CHAR, VARCHAR2) columns.

Row Migration: A row will migrate when an update to that row would cause it to not fit on the block, the row will move to a new block leaving a link(forwarding address) in its original block pointing to the new block location.

The Harm of Migrated/Chained Rows:
Migrated/Chained rows can cause bad database performance by affecting index reads and full table scans.

How to fix:

You can use one of the following solutions the first one is 2 steps and the other is 10 steps :-)
This will depends on the number of chained rows and the downtime you can take for applying the fix:

Solution #1: 2 steps
========


Note: This solution will take longer downtime on the table. (preferred when chained rows are much)

1- Rebuild the table having chained rows:
SQL> alter table TEST move;
         -->This will invalidate all indexes that are associated with the table TEST.

Note: You can increase the PCTFREE within the move command to reduce row chaining possibility in the future:

      SQL> alter table TEST move pctfree 30;
               -->Note: PCTFREE default value is 10

2- Rebuild ALL Indexes associated with the table:
SQL> SELECT 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online ;'FROM DBA_INDEXES where TABLE_NAME='TEST';

Done.

Solution #2: 10 Steps
========

1- Create a table contains the chained rows result using this script:

$ORACLE_HOME/rdbms/admin/utlchain.sql

2- Collect information about migrated and chained rows:

SQL> ANALYZE TABLE TEST LIST CHAINED ROWS;

3- Query the output table:

SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME = 'TEST';

OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP
---------- ---------- -----... ------------------ ---------
SCOTT TEST ... AAAVdkAATAAAfpfAA1 25-JUL-11
SCOTT TEST ... AAAVdkAATAAAfpfAA9 25-JUL-11

If there are many migrated/chained rows you can go ahead through following steps:

4- Create an intermediate table holding chained rows with the same structure as org table:

SQL> CREATE TABLE int_TEST AS SELECT * FROM TEST WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'TEST');

Note: "Create Table As" statement will fail if the original table includes LONG datatype

5- Delete the migrated/chained rows from the original table:

SQL> DELETE FROM TEST
WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'TEST');

If it returned back ORA-02292: integrity constraint (xxx)violated, disable that referential constraint and try again.

6-Insert the rows back from the intermediate table into the original table:

SQL> INSERT INTO TEST SELECT * FROM int_TEST;

7-Drop the intermediate table:

SQL> DROP TABLE int_TEST;

8-Delete the information collected in step 1 from the output table:

SQL> DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'TEST';

9- Use the ANALYZE statement again:

SQL> ANALYZE TABLE TEST LIST CHAINED ROWS;

10- Check for chained rows: -Should be 0-

SQL> SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME = 'TEST';

no rows selected

Done.

Which is better for gathering statistics? DBMS_STATS or ANALYZE command

Use DBMS_STATS for gathering statistics that related to the optimizer,execution plans and so forth.
Cost-Based Optimizer (CBO) will only use the statistics been gathered by DBMS_STATS.

Use ANALYZE command to collect statistics NOT related to Cost-Based Optimizer (CBO)
Such statistics can be used by VALIDATE or LIST CHAINED ROWS clauses, can help in gathering accurate data such as empty blocks, average space, Freelist blocks and so forth.

For more information please check this link:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/general002.htm#ADMIN11525
Database Administrator's Guide(11.2)

Sharing the Same Read Only Tablespace between Multiple Databases

The Theory:
Separate databases can share the same read only datafiles on disk. The datafiles must be accessible by all databases.

The Method:
This will be implemented using transportable tablespaces.

Note: The Shared tablespace must remain read-only in all databases mounting it.

The Way:
-The Shared read only Tablespace is already belongs to a database (ORCL).

-Make the tablespace read only:
SQL> ALTER TABLESPACE USERS READ ONLY;

-Export tablespace metadata using expdp utility:

export ORACLE_SID=ORCL
expdp system dumpfile=expdat.dmp directory=data_pump_dir
transport_tablespaces=USERS logfile=ts_export.log

-Import tablespace metadata to the destination database using impdp utility:

export ORACLE_SID=FOO
impdp system dumpfile=expdat.dmp directory=data_pump_dir
transport_datafiles=/ORA11G/ORADATA/ORCL/USERS01.DBF logfile=ts_import.log

*I suppose that directory "data_pump_dir" have the same path on both databases (ORCL &FOO).

Note: Dropping the read only tablespace in some databases will not modify the datafiles for the tablespace.Thus, the drop operation does not corrupt the tablespace,
Sure you will not use -including contents and datafiles- clause to delete the shared tablespace on one database or this will delete the tablespace datafiles on the Filesystem.


Warning:
Switching the read-only shared tablespace to read-write mode will lead to database corruption if it's mounted in more than one database.