Mostrando postagens com marcador Rman. Mostrar todas as postagens
Mostrando postagens com marcador Rman. Mostrar todas as postagens

How to clone a database using cold backup and restore it with different SID on a new host server



Objective :-We are cloning a database running on target server  and restoring it into clone server with a different sid.

Note- Make sure oracle binary is already installed on target db server.In this case the directory structure of target server is different than source server.

Database Version :- Oracle Enterprise Edition  11.2.0.1 on RHEL 6.4 on both server

Source  Server: 192.168.0.102
Database sid:- prim
Hostname:-server1.soumya.com


Destination Server: 192.168.0.104
database sid: newprim
Hostname:-server2.soumya.com


1.Take backup of controlfile as trace:[SOURCE DB]

SQL> alter database backup controlfile to trace as '/u01/app/oracle/control01.sql';
Database altered.

2. check the location of datafiles[SOURCE DB]

SQL>  select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/example01.dbf


SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/temp01.dbf


3. Shutdown the database:[SOURCE DB]
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


4. Copy the datafiles to the target db server
[oracle@server1 ]$cd /u01/app/oracle/oradata/prim

[oracle@server1 backup]$ scp *.dbf oracle@server2:/u01/
The authenticity of host 'server2 (192.168.0.104)' can't be established.
RSA key fingerprint is 0b:59:e4:8b:b1:e6:12:3a:38:4f:ba:74:ef:8a:ad:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server2,192.168.0.104' (RSA) to the list of known hosts.
oracle@server2's password:
example01.dbf                           100%  100MB  25.0MB/s   00:04   
sysaux01.dbf                            100%  500MB  16.7MB/s   00:30   
system01.dbf                            100%  670MB  19.7MB/s   00:34   
temp01.dbf                              100%   20MB   6.7MB/s   00:03   
undotbs01.dbf                           100%   55MB  27.5MB/s   00:02   
users01.dbf                             100% 5128KB   5.0MB/s   00:00   

Also copy the controlfile which we took backup at step 1 and transfer it into target server.

5.Make changes in the init file for target db:[TARGET DB]
We can copy the pfile from source db and the change the required parameters like DB_NAME and control_file localtion,audit_file_dest location,diag location etc.


[oracle@server2 dbs]$ cat initnewprim.ora

newprim.__db_cache_size=138412032
newprim.__java_pool_size=4194304
newprim.__large_pool_size=4194304
newprim.__oracle_base='/u01/newapp/oracle'#ORACLE_BASE set from environment
newprim.__pga_aggregate_target=167772160
newprim.__sga_target=247463936
newprim.__shared_io_pool_size=0
newprim.__shared_pool_size=92274688
newprim.__streams_pool_size=0
*.audit_file_dest='/u01/newapp/oracle/admin/newprim/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/newapp/oracle/oradata/newprim/control01.ctl','/u01/newapp/oracle/flash_recovery_area/newprim/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='newprim'
*.db_recovery_file_dest='/u01/newapp/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/newapp/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=newprimXDB)'
*.memory_target=414187520
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Create necessary directories as per new sid

[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/admin/newprim/adump
[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/oradata/newprim/
[oracle@server2 dbs]$ mkdir -p /u01/newapp/oracle/flash_recovery_area/newprim/



6. Now Start the database in nomount stage:[TARGET DB]
[oracle@server2 dbs]$ export ORACLE_SID=newprim

[oracle@server2 dbs]$sqlplus / as sysdba
SQL> startup nomount pfile='/u01/newapp/oracle/product/11.2.0/db_1/dbs/initnewprim.ora';
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             268437496 bytes
Database Buffers          138412032 bytes
Redo Buffers                4308992 bytes



7. Re-recreate the controlfile [ TARGET DB ]

Open the controlfile and remove word REUSE and make it SET
remove word NORESETLOGS and make it RESETLOGS
remove or keep the word ARCHIVELOG depending upon our requirement
change database name from "prim" to "newprim"
change directory name everywhere from 'prim' to 'newprim'
:wq

The content should look like this

[oracle@server2 u01]$ vi control01.sql

CREATE CONTROLFILE SET DATABASE "NEWPRIM" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/newprim/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/newprim/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/newprim/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/newprim/system01.dbf',
  '/u01/app/oracle/oradata/newprim/sysaux01.dbf',
  '/u01/app/oracle/oradata/newprim/undotbs01.dbf',
  '/u01/app/oracle/oradata/newprim/users01.dbf',
  '/u01/app/oracle/oradata/newprim/example01.dbf'
CHARACTER SET WE8MSWIN1252
;


8.Create the controlfile at target db.

SQL>@/u01/control01.sql
Control file created.

9.Open the database in resetlog mode:

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

10.Create the temp files.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/newapp/oracle/oradata/newprim/temp_01.dbf' SIZE 50m autoextend on next 10m maxsize unlimited;
Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
newprim

How to clone a database using user managed hot backup in oracle database 11g

How to clone a database using user managed hot backup in oracle database 11g

Objective :-We are cloning a database running on target server  and restoring it into clone server.


Database Version :- Oracle Enterprise Edition  11.2.0.1 on RHEL 6.4

Target Server: 192.168.0.101
Database sid:- prim

Clone Server: 192.168.0.104
database sid: newprim


****At target Database

Take online backup of database

Before taking the online backup we need to put the database into archivelog mode.


$ export ORACLE_SID=prim

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>alter database begin backup;
Database altered.

SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> /

1 row created.

SQL> /
SQL> commit;
SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                1067890 01-MAY-18
         2 ACTIVE                1067890 01-MAY-18
         3 ACTIVE                1067890 01-MAY-18
         4 ACTIVE                1067890 01-MAY-18
         5 ACTIVE                1067890 01-MAY-18

SQL> exit


Now switch into the datafile directory and copy all the .dbf and .log files into the backup directory

[oracle@server1]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ cp -rpf *.log /home/oracle/bkup/
[oracle@server1 prim]$ cp -rpf *.dbf /home/oracle/bkup/


SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE            1067890 01-MAY-18
         2 NOT ACTIVE            1067890 01-MAY-18
         3 NOT ACTIVE            1067890 01-MAY-18
         4 NOT ACTIVE            1067890 01-MAY-18
         5 NOT ACTIVE            1067890 01-MAY-18

Now take backup of controlfile
SQL> alter database backup controlfile to trace as '/home/oracle/control.sql';

Database altered.

Now edit the content of control file as per below.

[oracle@server1 ~]$vi /home/oracle/control.sql
Remove word REUSE and make it SET
remove word NORESETLOGS and make it RESETLOGS
remove or keep the word ARCHIVELOG depending upon our requirement
change database name from "prim" to "newprim"
change folder name everywhere from 'prim' to 'newprim'
:wq

The content should look like this


CREATE CONTROLFILE SET DATABASE "NEWPRIM" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/newprim/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/newprim/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/newprim/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/oradata/newprim/system01.dbf',
  '/u01/app/oracle/oradata/newprim/sysaux01.dbf',
  '/u01/app/oracle/oradata/newprim/undotbs01.dbf',
  '/u01/app/oracle/oradata/newprim/users01.dbf',
  '/u01/app/oracle/oradata/newprim/example01.dbf'
CHARACTER SET WE8MSWIN1252
;



Now transfer all the datafiles, controlfile, redolog files into clone server using scp.

[oracle@server1 ~]$ scp control.sql oracle@192.168.0.104:/u01/app/oracle/oradata/newprim/
The authenticity of host '192.168.0.104 (192.168.0.104)' can't be established.
RSA key fingerprint is 0b:59:e4:8b:b1:e6:12:3a:38:4f:ba:74:ef:8a:ad:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.104' (RSA) to the list of known hosts.
oracle@192.168.0.104's password:
control.sql                                 100%  698     0.7KB/s   00:00   
[oracle@server1 ~]$ cd bkup/
[oracle@server1 bkup]$ ll
total 1534024
-rw-r-----. 1 oracle oinstall 104865792 May  1 20:23 example01.dbf
-rw-r-----. 1 oracle oinstall  52429312 May  1 20:33 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 May  1 20:23 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 May  1 20:23 redo03.log
-rw-r-----. 1 oracle oinstall 524296192 May  1 20:29 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 May  1 20:33 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 May  1 19:58 temp01.dbf
-rw-r-----. 1 oracle oinstall  57679872 May  1 20:31 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 May  1 20:33 users01.dbf
[oracle@server1 bkup]$ scp * oracle@192.168.0.104:/u01/app/oracle/oradata/newprim/
oracle@192.168.0.104's password:
example01.dbf                                100%  100MB  50.0MB/s   00:02   
redo01.log                                   100%   50MB  50.0MB/s   00:01   
redo02.log                                   100%   50MB  25.0MB/s   00:02   
redo03.log                                   100%   50MB  25.0MB/s   00:02   
sysaux01.dbf                                 100%  500MB  19.2MB/s   00:26   
system01.dbf                                 100%  670MB  12.6MB/s   00:53   
temp01.dbf                                   100%   20MB  20.0MB/s   00:01   
undotbs01.dbf                                100%   55MB  55.0MB/s   00:00   
users01.dbf                                  100% 5128KB   5.0MB/s   00:00   
[oracle@server1 bkup]$ cd
[oracle@server1 ~]$ ll



****At clone server

Create necessary directory structure for clone database.
mkdir -p /u01/app/oracle/oradata/newprim/
mkdir -p /u01/app/oracle/admin/newprim/adump


export ORACLE_SID=newprim
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initnewprim.ora';
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             268437496 bytes
Database Buffers          138412032 bytes
Redo Buffers                4308992 bytes

Now recreate control file.
SQL> @/u01/app/oracle/oradata/newprim/control.sql;

Control file created.


Now recover the database.

sql> recover database using backup controlfile until cancel;
here one by one apply all archivelog files
apply all 3 redo log files with full path until we get the msg that recovery is done.

Here its better to notedown the current redo log file and the pending archivelogs from target db at the time of backup. We need to apply only those
archives and only one current redo log file.


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1067890 generated at 05/01/2018 20:23:49 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.
arc
ORA-00280: change 1067890 for thread 1 is in sequence #7


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/NEWPRIM/archivelog/2014_12_28/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/newprim/redo01.log
Log applied.
Media recovery complete.


Open the database with resetlogs

SQL> alter database open resetlogs;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
newprim

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Now the process of cloning a database is complete into a new server using user managed backup.
Thanks

RMAN Full Backup script for Windows

Make sure all the directories mentioned in the script is present.Just copy the following lines mentioned below and save it as .bat file and schedule it in task scheduler.


@ECHO OFF
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%b-%%a)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
set mytime=%mytime: =0%

set ORACLE_SID=orcl
set RUNDIR=C:\TEMP
set LOGDIR=C:\oracle\log
set ORACLE_HOME=C:\app\Oracle11G\product\11.2.0\dbhome_1
set ORACLE_BASE=C:\app\Oracle11G\
set PATH=%ORACLE_HOME%\bin
rem set NLS_DATE_FORMAT='DD-MM-YY HH24:MI:SS'
set BACKUPDIR=c:\oracle\oradata\backup\%mydate%_%mytime%
set LOGFILE=%LOGDIR%\%ORACLE_SID%_%mydate%_%mytime%.log
set ADDR='job@jobacle.nl'
set TMPLOG=%LOGDIR%\tmplog.$$
mkdir "c:\oracle\oradata\backup\%mydate%_%mytime%""

set CMDFILE=%RUNDIR%\%ORACLE_SID%.rman
echo run { > %CMDFILE%
echo CONFIGURE RETENTION POLICY TO REDUNDANCY 2; >> %CMDFILE%
echo CONFIGURE BACKUP OPTIMIZATION ON;  >> %CMDFILE%
echo CONFIGURE DEFAULT DEVICE TYPE TO DISK; >> %CMDFILE%
echo CONFIGURE CONTROLFILE AUTOBACKUP ON;  >> %CMDFILE%
echo CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%BACKUPDIR%\ora_cf%%F'; >> %CMDFILE%
echo CONFIGURE DEVICE TYPE DISK PARALLELISM 2;  >> %CMDFILE%
echo CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;  >> %CMDFILE%
echo CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;  >> %CMDFILE%
echo CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '%BACKUPDIR%\ora_df%%t_s%%s_s%%p'; >> %CMDFILE%
echo CONFIGURE MAXSETSIZE TO UNLIMITED;  >> %CMDFILE%
echo CONFIGURE SNAPSHOT CONTROLFILE NAME TO '%BACKUPDIR%\snapcf_%ORACLE_SID%_%mydate%_%mytime%.f'; >> %CMDFILE%
echo CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '%BACKUPDIR%\ora_df%%t_s%%s_s%%p'; >> %CMDFILE%
echo SHOW ALL; >> %CMDFILE%
echo sql "alter system archive log current"; >> %CMDFILE%
echo DELETE FORCE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-2'; >> %CMDFILE%
echo BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL=0 DATABASE INCLUDE CURRENT  CONTROLFILE PLUS ARCHIVELOG; >> %CMDFILE%
echo sql "alter database backup controlfile to trace as ''%BACKUPDIR%\ctrlfile_%ORACLE_SID%_%mydate%_%mytime%''"; >> %CMDFILE%
echo CROSSCHECK BACKUP; >> %CMDFILE%
echo REPORT OBSOLETE; >> %CMDFILE%
echo DELETE FORCE NOPROMPT OBSOLETE; >> %CMDFILE%
echo }  >> %CMDFILE%

echo LIST BACKUP OF DATABASE; >> %CMDFILE%
echo LIST BACKUP OF ARCHIVELOG ALL; >> %CMDFILE%
echo LIST BACKUP; >> %CMDFILE%

echo Starting the script > %LOGFILE%
%ORACLE_HOME%\bin\rman target / @%CMDFILE% msglog=%TMPLOG% >>%LOGFILE%

type %TMPLOG% >> %LOGFILE%

copy "%TMPLOG%" "%BACKUPDIR%\%mydate%_%mytime%.log"
@echo on

How to take RMAN INCREMENTAL LEVEL 1 Cumulative Backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/inc_level_1_cum
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/


Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;


[oracle@server1 ~]$  vi /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh

#!/bin/bash
export PS1="`/bin/hostname s`> " 
export ORACLE_SID=prim
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/inc_level_1_cum
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
su - oracle -c "$ORACLE_HOME/bin/rman target / " <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
backup incremental level 1 cumulative database FORMAT '$path1/$date1/inc_level_1_cum_%d_%T_%t_%s_%p'; 
eof



[oracle@server1 ~]$ chmod 755 /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh

Now schedule the script using crontab from oracle user:-
The  script will run every 6 hours

[oracle@server1 ~]$ crontab -e

0 */6 * * * /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh > /dev/null

How to take RMAN INCREMENTAL LEVEL 1 Backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/inc_level_1
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/


Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;


[oracle@server1 ~]$ vi  /u01/backups/scripts/IncLevel_1_rman_backup.sh

#!/bin/bash
export PS1="`/bin/hostname s`> " 
export ORACLE_SID=prim
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/inc_level_1
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
$ORACLE_HOME/bin/rman target / <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
backup incremental level 1 database FORMAT '$path1/$date1/inc_level_1_%d_%T_%t_%s_%p'; 

eof

cd $path1
file1=`ls -ltrh | tail -1 | awk '{print $9}'`
tar -zcvf $file1.tar.gz $file1


:wq (save & exit)


Now schedule the script using crontab from oracle user:-
#The  script will run every 30mins

[oracle@server1 ~]$ chmod 755 /u01/backups/scripts/IncLevel_1_rman_backup.sh
[oracle@server1 ~]$ crontab -e
15,45 * * * * /u01/backups/scripts/IncLevel_1_rman_backup.sh > /dev/null

How to take RMAN Full backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/full_backup
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/


Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;


[oracle@server1 ~]$ vi  /u01/backups/scripts/full_backup.sh

#!/bin/bash
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/full_backup
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
$ORACLE_HOME/bin/rman target / <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
run
{
backup incremental level 0 database FORMAT '$path1/$date1/full_%d_%T_%t_%s_%p';
backup archivelog all FORMAT '$path1/$date1/archive_%d_%T_%t_%s_%p' ;
}
eof

cd $path1
file1=`ls -ltrh | tail -1 | awk '{print $9}'`
tar -zcvf $file1.tar.gz $file1


:wq (save & exit)


Now schedule the script using crontab from oracle user:-
#The  script will run everynight at 12 A.M


[oracle@server1 ~]$ crontab -e
0 0 * * * /u01/backups/scripts/full_backup.sh > /dev/null




RMAN throwing error no AUTOBACKUP found of controlfile

RMAN throwing error no AUTOBACKUP found of controlfile

Error:- RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Reason:-
Basically we get to see above error comes generally in two cases.
1. Either the controlfile autobackup is not present in os level
2. Or if the controlfiles are not present in Flash recovery area, because by default RMAN looks for controlfile autobackup in $ORACLE_HOME/dbs or if FRA is enabled then in\flash recovery
area location.


Database Version:- Oracle 11g R2
Database SID:- prim

So, here i'm trying to produce a test case to demonstrate the error.

[oracle@server1 u01]$ rman target sys/sys@prim

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:05:13 2015

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

connected to target database: PRIM (DBID=4130009889)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default


RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default

Now lets drop the controlfile from system and recover it from the backup.

[oracle@server1 ~]$ sqlplus / as sysdba
SQL> select dbid from v$database;

      DBID
----------
4130009889

SQL> show parameter control_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/prim/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 flash_recovery_area/prim/contr
                                                 ol02.ctl
                                               
[root@server1 2015_01_08]# cd /u01/app/oracle/oradata/prim/
[root@server1 prim]# ll
total 1543848
-rw-r-----. 1 oracle oinstall  10076160 Jan  9 00:07 control01.ctl
-rw-r-----. 1 oracle oinstall 104865792 Jan  8 23:46 example01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Jan  9 00:07 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jan  8 23:46 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jan  8 23:46 redo03.log
-rw-r-----. 1 oracle oinstall 524296192 Jan  9 00:06 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 Jan  9 00:06 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jan  8 22:24 temp01.dbf
-rw-r-----. 1 oracle oinstall  57679872 Jan  9 00:06 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jan  8 23:4

[root@server1 prim]# rm -rf control01.ctl                                               

[root@server1] cd /u01/app/oracle/flash_recovery_area/prim
[root@server1 prim]# ll
total 9840
-rw-r-----. 1 oracle oinstall 10076160 Jan  9 00:08 control02.ctl
[root@server1 prim]# rm -rf control02.ctl                                                     

NOW After deleting the controlfiles from multiplexed location , do a log switch .
[oracle@server1] sqlplus / as sysdba

SQL> alter system switch logfile;

System altered.

Now, lets try to restore the controlfile from autobackup.

Bringing the instance in nomount mode for the recovery purpose.

SQL> shut immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/prim/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> startup nomount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes


[oracle@server1 u01]$ rman target sys/sys@prim

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:19:56 2015

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

connected to target database: PRIM (not mounted)

RMAN> set dbid=4130009889

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 08-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150105
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150104
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150103
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150102
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/08/2015 23:20:55
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece


So to overcome the above issue we will point out the exact autobackup location to rman
RMAN> set controlfile autobackup format for device type disk to '/u01/app/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 08-JAN-15
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/c-4130009889-20150108-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/c-4130009889-20150108-00

channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/prim/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/prim/control02.ctl
Finished restore at 08-JAN-15

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

So using the following way we can restore the controlfile from autobackup.



Please share your ideas and opinions about this topic.

If you like this post, then please share it with others.
Please subscribe on email for every updates on mail.

RECOVER DATABASE USING BACKUP CONTROLFILE fails due to no archivelog present

Scenario:- So here i am producing a test case where I will need to recover the database but the last archivelog is not present in system which required for the database recovery.

Database Version:- Oracle 11g R2
Database SID:- prim

First before the doing the activity i'am taking a full database backup along with archivelogs.

[oracle@server1 u01]$ rman target sys/sys@prim

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:05:13 2015

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

connected to target database: PRIM (DBID=4130009889)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default


RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default


RMAN> backup database plus archivelog;


Starting backup at 08-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=4 STAMP=868489636
input archived log thread=1 sequence=2 RECID=5 STAMP=868489684
input archived log thread=1 sequence=3 RECID=6 STAMP=868490020
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_annnn_TAG20150108T231340_bbxjgdwc_.bkp tag=TAG20150108T231340 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JAN-15

Starting backup at 08-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prim/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_nnndf_TAG20150108T231341_bbxjggsm_.bkp tag=TAG20150108T231341 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:57
Finished backup at 08-JAN-15

Starting backup at 08-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=7 STAMP=868490079
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_annnn_TAG20150108T231439_bbxjj7oj_.bkp tag=TAG20150108T231439 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JAN-15

Starting Control File and SPFILE Autobackup at 08-JAN-15
piece handle=/u01/app/c-4130009889-20150108-00 comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-15

RMAN> exit


Now once the backups have been done, lets make some changes inside the database.
SQL> create user soumya identified by soumya;

User created.

SQL> grant connect , resource to soumya;

Grant succeeded.

SQL> conn soumya/soumya
Connected.
SQL> create table xyz ( id number);

Table created.

SQL> insert into xyz values(1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

        ID
----------
         1
         1
         1
         1
         1

Now lets drop the controlfile from system and recover it from the backup.

SQL> show parameter control_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/prim/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 flash_recovery_area/prim/contr
                                                 ol02.ctl

[root@server1 2015_01_08]# cd /u01/app/oracle/oradata/prim/
[root@server1 prim]# ll
total 1543848
-rw-r-----. 1 oracle oinstall  10076160 Jan  9 00:07 control01.ctl
-rw-r-----. 1 oracle oinstall 104865792 Jan  8 23:46 example01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Jan  9 00:07 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jan  8 23:46 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jan  8 23:46 redo03.log
-rw-r-----. 1 oracle oinstall 524296192 Jan  9 00:06 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 Jan  9 00:06 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jan  8 22:24 temp01.dbf
-rw-r-----. 1 oracle oinstall  57679872 Jan  9 00:06 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jan  8 23:4

[root@server1 prim]# rm -rf control01.ctl

[root@server1] cd /u01/app/oracle/flash_recovery_area/prim
[root@server1 prim]# ll
total 9840
-rw-r-----. 1 oracle oinstall 10076160 Jan  9 00:08 control02.ctl
[root@server1 prim]# rm -rf control02.ctl                                              

NOW After deleting the controlfiles from multiplexed location , do a log switch .
[oracle@server1] sqlplus / as sysdba

SQL> alter system switch logfile;

System altered.

Now, lets try to restore the controlfile from autobackup.

Bringing the instance in nomount mode for the recovery purpose.

SQL> shut immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/prim/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> startup nomount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes


[oracle@server1 u01]$ rman target sys/sys@prim

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:19:56 2015

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

connected to target database: PRIM (not mounted)

RMAN> set dbid=4130009889

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 08-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150105
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150104
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150103
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150102
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/08/2015 23:20:55
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece


The above error comes generally in two cases,
1. Either the controlfile autobackup is not present in os level
2. Or if the controlfiles are not present in Flash recovery area, because by default RMAN looks for controlfile autobackup in $ORACLE_HOME/dbs or if FRA is enabled then in\
flash recovery area location.


So to overcome the above issue we can will point out the exact autobackup location to rman
RMAN> set controlfile autobackup format for device type disk to '/u01/app/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 08-JAN-15
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/c-4130009889-20150108-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/c-4130009889-20150108-00

channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/prim/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/prim/control02.ctl
Finished restore at 08-JAN-15

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

[oracle@server1] sqlplus / as sysdba
SQL> alter database open;
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/prim/system01.dbf'

so while opening the database using resetlogs we found the above error.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1032424 generated at 01/08/2015 23:14:39 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_5_bbxjoph
7_.arc
ORA-00280: change 1032424 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

So when trying to do a cancel based recovery here we see the database report an error that its missing '/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.ar
c' archivelog file.

So i checked at os level and couldnt find the archivelog file.
Since we did shutdown immediate, we need to apply the current online redolog when it prompts for.Alternatively, we can supply the names of each of the online redo logs that we have and
Oracle will reject the one(s) that do not correspond to Sequence#6.


SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1          4   52428800        512          1 YES ACTIVE                 1032376 08-JAN-15      1032415 08-JAN-15
         3          1          3   52428800        512          1 YES INACTIVE               1031899 08-JAN-15      1032376 08-JAN-15
         2          1          5   52428800        512          1 NO  CURRENT                1032415 08-JAN-15   2.8147E+14


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1033111 generated at 01/08/2015 23:17:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.arc
ORA-00280: change 1033111 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/prim/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select * from soumya.xyz;

        ID
----------
         1
         1
         1
         1
         1

So we see we have the table as well which we created after taking the full backup.




Please share your ideas and opinions about this topic.

If you like this post, then please share it with others.
Please subscribe on email for every updates on mail.


Duplicate a database in a new host/server where only incremental level 0 backup is available in oracle 11g

Oracle Version :-11g
Target Database : prim
Target Database Server : server1.soumya.com
IP: 192.168.2.102

Oracle Version :-11g
Auxiliary Database : stand
Auxiliary Database Server : server3.soumya.com
IP:192.168.2.12

Step 1:
Take the incremental level 0 backup of the Target database using RMAN.
In my case, I had the backup of my target database (prim) taken at the location '/u01/bkp'
[oracle@server1 bkp]$ pwd
/u01/bkp

[oracle@server1 bkp]$ ls -ltrh
total 1.1G
-rw-r-----. 1 oracle oinstall 9.4M Feb 17 15:06 o1_mf_ncsnf_TAG20150217T150631_bg62x0o8_.bkp
-rw-r-----. 1 oracle oinstall  13M Feb 17 15:08 o1_mf_annnn_TAG20150217T150811_bg63043x_.bkp
-rw-r-----. 1 oracle oinstall 1.1G Feb 17 15:09 o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp
-rw-r-----. 1 oracle oinstall 9.4M Feb 17 15:09 o1_mf_ncsnf_TAG20150217T150813_bg632lxo_.bkp
-rw-r-----. 1 oracle oinstall 8.0K Feb 17 15:09 o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp


Step 2:
Copy these backup pieces from the Target server (location /u01/bkp) to the auxiliary server (location /u01/bkp)
Also, copy the pfile (initprim.ora) of the Target database to the Auxiliary server.

[oracle@server1 ]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@server1 ]$ cp initprim.ora /u01/bkp/

[oracle@server1 $ scp -r *.* oracle@192.168.2.12:/u01/

oracle@192.168.2.12's password:
initprim.ora  100% 912Kb 0.9KB/s  00:00    100% 911KB 9.0MB/s  00.00
o1_mf_ncsnf_TAG20150217T150631_bg62x0o8_.bkp                                                                  100% 9600KB   9.4MB/s   00:00  
o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp                                                                  100% 8192     8.0KB/s   00:00  
o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp                                                                  100% 1060MB  20.0MB/s   00:53  
o1_mf_annnn_TAG20150217T150811_bg63043x_.bkp                                                                  100%   12MB  12.2MB/s   00:00  
o1_mf_ncsnf_TAG20150217T150813_bg632lxo_.bkp                                                                  100% 9600KB   9.4MB/s   00:00  

Step 3:

On the Auxiliary server, edit the pfile that was copied earlier to the desired entries (dump locations, control file location, datafile locations,
if using ASM then specify the desired disk group) and rename it to the desired instance name file (init<SID>.ora). Below is the sample I had it done.

vi initprim.ora

stand.__db_cache_size=16777216
stand.__java_pool_size=20971520
stand.__large_pool_size=4194304
stand.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stand.__pga_aggregate_target=142606336
stand.__sga_target=272629760
stand.__shared_io_pool_size=0
stand.__shared_pool_size=213909504
stand.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/stand/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/stand/control01.ctl','/u01/app/oracle/flash_recovery_area/stand/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='stand'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.memory_target=415236096
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/prim/','/u01/app/oracle/oradata/stand/'
*.log_file_name_convert='/u01/app/oracle/oradata/prim/','/u01/app/oracle/oradata/stand/'

:wq

Make sure db_file_name_convert and log_file_name_convert parameters are present in pfile else it would
throw error while creating auxiliary database .
Once changes are done rename the pfile with your instance sid and copy it inside ORACLE_HOME/dbs
[oracle@server3] mv initprim.ora initstand.ora
[oracle@server3] cp initstand.ora $ORACLE_HOME/dbs

Step 4:

Create a password file for the Auxiliary Database using the ORAPWD utility.

orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstand password=redhat


Step 5:

Start the auxiliary instance using the modified by pfile(initstand.ora)

[oracle@uat ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
[oracle@uat ~]$ export ORACLE_SID=stand

Keep in mind that the auxiliary instance doesnt have any instance running on, only oracle binaries are installed on this.

Step 6 :-
Create the required directory structures are mentioned in pfile.
oracle@server3 ~]$ mkdir -p /u01/app/oracle/admin/stand/adump
[oracle@server3 ~]$ mkdir -p /u01/app/oracle/oradata/stand/
[oracle@server3  ]$ mkdir -p /u01/app/oracle/flash_recovery_area/stand/

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora'
ORACLE instance started.
Total System Global Area  754974720 bytes
Fixed Size                  2928968 bytes
Variable Size             637537976 bytes
Database Buffers          109051904 bytes
Redo Buffers                5455872 bytes


Step 6:

Connect the auxiliary instance through RMAN and start the duplication.
The duplication is done by specifying the location of the backup pieces. The command to be used is
DUPLICATE DATABASE TO '<auxiliary dbname>' BACKUP LOCATION '<location of the backup pieces on the auxiliary server>'

[oracle@server3 dbs]$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Sat Jul 11 18:04:20 2015

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

connected to auxiliary database: stand (not mounted)

RMAN> duplicate database to 'stand' backup location '/u01/bkp' NOFILENAMECHECK;

Starting Duplicate Db at 08-JAN-15

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRIM'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''STAND'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/2015_02_17/o1_mf_ncsnf_TAG20150217T150813_bg632lxo_.bkp';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PRIM'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''STAND'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes

Starting restore at 08-JAN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/stand/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/stand/control02.ctl
Finished restore at 08-JAN-15

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   set until scn  58614791;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/stand/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/stand/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/stand/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/stand/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/stand/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-JAN-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/stand/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/stand/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/stand/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/stand/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/stand/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/2015_02_17/o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/2015_02_17/o1_mf_nnndf_TAG20150217T150813_bg6305j3_.bkp tag=TAG20150217T150813
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:07
Finished restore at 08-JAN-15

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=868491262 file name=/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf

contents of Memory Script:
{
   set until scn  58614791;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 08-JAN-15
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_AUX_DISK_1: reading from backup piece /u01/2015_02_17/o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/2015_02_17/o1_mf_annnn_TAG20150217T150932_bg632nml_.bkp tag=TAG20150217T150932
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/STAND/archivelog/2015_01_08/o1_mf_1_16_bbxko71g_.arc thread=1 sequence=16
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/STAND/archivelog/2015_01_08/o1_mf_1_16_bbxko71g_.arc RECID=1 STAMP=868491263
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-JAN-15

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''STAND'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes

sql statement: alter system set  db_name =  ''STAND'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     413372416 bytes

Fixed Size                     2213896 bytes
Variable Size                394266616 bytes
Database Buffers              12582912 bytes
Redo Buffers                   4308992 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "STAND" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/stand/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/stand/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/stand/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/stand/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/stand/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/stand/sysaux01.dbf",
 "/u01/app/oracle/oradata/stand/undotbs01.dbf",
 "/u01/app/oracle/oradata/stand/users01.dbf",
 "/u01/app/oracle/oradata/stand/example01.dbf",
 "/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf",
 "/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/stand/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/sysaux01.dbf RECID=1 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/undotbs01.dbf RECID=2 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/users01.dbf RECID=3 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/example01.dbf RECID=4 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf RECID=5 STAMP=868491274
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf RECID=6 STAMP=868491274

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/GHHSTORE_DATA_TBS.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=868491274 file name=/u01/app/oracle/oradata/stand/GHHSTORE_IDX_TBS.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 08-JAN-15

RMAN>

SQL> select name from v$database;

NAME
---------
STAND

Done..!!!




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Related Posts Plugin for WordPress, Blogger...