RMAN, Copy or Restore and Recover DB

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Check colored Alert-Logs , RAC instance

$ tail -1000f /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/alert_ORCL1.log | perl -pe 's/GREENword/\e[1;32;40m$&\e[0m/g,s/Completed: /\e[1;32;40m$&\e[0m/g,s/Archived/\e[1;32;40m$&\e[0m/g,s/Flashback/\e[1;32;40m$&\e[0m/g,s/started/\e[1;32;40m$&\e[0m/g,s/Opened/\e[1;32;40m$&\e[0m/g,s/Database mounted/\e[1;32;40m$&\e[0m/g,s/Media Recovery Canceled/\e[1;32;40m$&\e[0m/g,s/YELLOWword/\e[1;33;40m$&\e[0m/g,s/starting/\e[1;33;40m$&\e[0m/g,s/ORA-/\e[1;33;40m$&\e[0m/g,s/ALTER/\e[1;33;40m$&\e[0m/g,s/Shutting/\e[1;33;40m$&\e[0m/g,s/Stopping/\e[1;33;40m$&\e[0m/g,s/stopped/\e[1;33;40m$&\e[0m/g,s/Archiving is disabled/\e[1;33;40m$&\e[0m/g,s/Starting/\e[1;33;40m$&\e[0m/g,s/Media Recovery Start/\e[1;33;40m$&\e[0m/g,s/REDword/\e[1;31;40m$&\e[0m/g,s/ORA-0/\e[1;31;40m$&\e[0m/g,s/ERROR/\e[1;31;40m$&\e[0m/g,s/error/\e[1;31;40m$&\e[0m/g,s/Errors/\e[1;31;40m$&\e[0m/g,s/failed/\e[1;31;40m$&\e[0m/g,s/Instance shutdown complete/\e[1;31;40m$&\e[0m/g,s/Drop/\e[1;31;40m$&\e[0m/g,s/Deleted/\e[1;31;40m$&\e[0m/g,s/Deadlock/\e[1;31;40m$&\e[0m/g,s/BLUEword/\e[1;36;40m$&\e[0m/g,s/Media Recovery Log/\e[1;36;40m$&\e[0m/g,s/Media Recovery Waiting for thread/\e[1;36;40m$&\e[0m/g'

Size of the Database

SQL> SELECT SUM(BYTES/1024/1024/1024) GB FROM DBA_DATA_FILES;

Current DB state (Sequence, Archivlogs. Datafiles, Controlfiles and Tempfiles path)

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log where sequence#>88139 ORDER BY sequence#;

SQL> select thread#, max(sequence#) from v$log group by thread# order by thread#;

         THREAD# MAX(SEQUENCE#)
         ---------- --------------
         1     5891
         2     5854
         3     6009
         4     6206
         5     5875
         6     5857

SQL> ARCHIVE LOG LIST

SQL>
set linesize 200;
set pagesize 0;
col name format a60;
SELECT file#, name, status FROM v$datafile;

SQL> select name from v$CONTROLFILE;

SQL> show parameter LOG_ARCHIVE_DEST;

SQL> 
column SPACE_LIMIT_Gb format 999
column SPACE_USED_Gb format 999
select name, SPACE_LIMIT/1024/1024/1024 SPACE_LIMIT_Gb, SPACE_USED/1024/1024/1024 SPACE_USED_Gb, SPACE_RECLAIMABLE from v$recovery_file_dest;

SQL>
set linesize 250;
set pagesize 0;
col GROUP# format 99;
col MEMBER format a60;
col STATUS format a10;
col Gb format 999;
select a.group#, member, a.status, bytes/1024/1024 as "Gb"
from v$log a, v$logfile b
where a.group# = b.group#
order by 1;

SQL> select name from v$tempfile;

Backup current initialization settings

SQL> create pfile='/tmp/pfileORCL1.txt' from spfile;
SQL> create pfile='/u01/oracle/database/11.2/dbs/pfileORCL1.txt' from memory;

 

Tasks before update Test Database

1. Stop the DB

$ export NLS_LANG=AMERICAN_RUSSIA.AL32UTF8
$ env | grep SID

$ oraenv

Simple database

SQL> sqlplus / as sysdba
SQL> shu immediate

RAC database

$ srvctl stop database -d ORCLTEST

2. Delete TEST Database files

DANGER: Delete datafiles, undo, redo tempfiles and controlfile (don't delete spfile)

3. Mount NFS from the EMC DataDomain D860

# mkdir /backup

Add row like this with you DataDomain parameters (permanent NFS mount)

vi /etc/fstab
mount 192.168.10.3:/data /backup nfs rw,hard,tcp,vers=3,bg,nointr,rsize=32768,wsize=32768,actimeo=0 0 0

Note 1: If we don't add row like that into the fstab file, mount point will not be mounted after Server reboot
Note 2: Be careful. If we use wrong mount parameters, the Server may not boot after the next reboot

Manual mount (without Server reboot)

# mount 192.168.10.3:/data /backup nfs rw,hard,tcp,vers=3,bg,nointr,rsize=32768,wsize=32768,actimeo=0 0 0

 

Change permissions of the backup folder from backup owner Server 

$ chmod -R 777 /backup/ORCL_BACKUP/20160710_ORCL_FULL

 

Restore DB (ver. FROM BACKUP) the DB Names are same

$ rman target /

RMAN> startup nomount

RMAN> RESTORE CONTROLFILE FROM "/backup/ORCL_BACKUP/20160710_ORCL_FULL/20160710_ipr7p747_1_1_inc0_ctl.bkp";          -- it will take a path of the last backup from controlfile

RMAN> list backup

RMAN> change backuppiece 19 uncatalog;

We can set path to the backup folder manually

RMAN> CATALOG START WITH '/backup/ORCL_BACKUP/20160710_ORCL_FULL';

RMAN> alter database mount;

 

RMAN> CATALOG START WITH '/backup/ORCL_BACKUP/20160710_ORCL_FULL/';

run {
allocate channel disk1 device type disk;
allocate channel disk2 device type disk;
allocate channel disk3 device type disk;
allocate channel disk4 device type disk;
allocate channel disk5 device type disk;
allocate channel disk6 device type disk;
allocate channel disk7 device type disk;
allocate channel disk8 device type disk;
restore database;
sql 'alter database flashback off';
SET ARCHIVELOG DESTINATION TO '/backup/ORCL_BACKUP/20160710_ORCL_FULL/';
recover database;
release channel disk1;
release channel disk2;
release channel disk3;
release channel disk4;
release channel disk5;
release channel disk6;
release channel disk7;
release channel disk8;
}

 

Restore DB (ver. DUPLICATE TO from BACKUP)

$ rman auxiliary /

run 
{
DUPLICATE DATABASE TO ORCLTEST
backup location '/backup/ORCL_BACKUP/20160710_ORCL_FULL'
nofilenamecheck;
}

or

run 
{
DUPLICATE DATABASE TO CBS3F
backup location '/backup/ORCL_BACKUP/20160710_ORCL_FULL'
nofilenamecheck dorecover;
}

 

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/07/2016 13:53:56
RMAN-05541: no archived logs found in target database

run 
{
DUPLICATE DATABASE TO IMKPT
backup location '/backup/ORCL_BACKUP/20160710_ORCL_FULL' noredo
nofilenamecheck;
}

 

Restore DB (ver. DUPLICATE FOR STANDBY from BACKUP) 

$ srvctl stop database -d ORCLTEST
SQL> startup nomount 

$ rman auxiliary /

run
{
allocate auxiliary channel c1 type disk ;
allocate auxiliary channel c2 type disk ;
allocate auxiliary channel c3 type disk ;
allocate auxiliary channel c4 type disk ;
allocate auxiliary channel c5 type disk ;
allocate auxiliary channel c6 type disk ;
allocate auxiliary channel c7 type disk ;
allocate auxiliary channel c8 type disk ;
DUPLICATE DATABASE FOR STANDBY
backup location '/backup/ORCL_BACKUP/20160710_ORCL_FULL'
nofilenamecheck;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
}

 

Using script
from backup to new DB name
rename datafile location, ASM

 

SQL>
COLUMN NAME FORMAT a60
SPOOL  LOG '/tmp/db_filenames.out'
SELECT FILE# AS "File/Grp#", NAME 
FROM   V$DATAFILE
UNION
SELECT GROUP#,MEMBER 
FROM   V$LOGFILE;
SPOOL OFF 

cd
vi reco_orcltest.rman 

########################reco_orcltest.rman##############################
RUN
{
# allocate a channel to the disk device
allocate channel disk1 device type disk;
allocate channel disk2 device type disk;
allocate channel disk3 device type disk;
allocate channel disk4 device type disk;
allocate channel disk5 device type disk;
allocate channel disk6 device type disk;
allocate channel disk7 device type disk;
allocate channel disk8 device type disk;

# rename the data files and online redo logs
# SQL "ALTER DATABASE RENAME FILE ''/u01/oradata/orcl/redo01.log'' TO ''u01/oradata/orcltest/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''+DATA/orcl/onlinelog/group_1.1541.810559891'' TO ''+DATA/orcltest/onlinelog/group_1.1541.810559891'' ";

# SET NEWNAME FOR DATAFILE 1 TO '?/oradata/orcltest/system01.dbf';
SET NEWNAME FOR DATAFILE   1 TO '+DATA/orcltest/datafile/system.1535.810559829';

restore database;
SWITCH DATAFILE ALL;
sql 'alter database flashback off';
sql 'alter database disable block change tracking';
SET ARCHIVELOG DESTINATION TO '/backup/ORCL';
recover database;
release channel disk1;
release channel disk2;
release channel disk3;
release channel disk4;
release channel disk5;
release channel disk6;
release channel disk7;
release channel disk8;
}
EXIT
########################END##############################

 

cd
rman target /

RMAN> @reco_orcltest.rman

RMAN> alter database open resetlogs;

Restore archivelogs manually before "alter database open resetlogs"

Disconnect old archivelogs

RMAN> CHANGE ARCHIVELOG ALL UNCATALOG;

Restore all archivelogs from backup of the Production DB to the some folder

RUN { 
SET ARCHIVELOG DESTINATION TO '/backup/ORCL';
RESTORE ARCHIVELOG ALL;
}

RMAN> CHANGE ARCHIVELOG FROM LOGSEQ=87038 UNTIL LOGSEQ=87038 UNCATALOG;

Restore last archivelogs from backup of the Production DB to the some folder

RMAN>

RUN {
set archivelog destination to '/backup/ORCL';
restore archivelog from sequence 87038;
}

or 

RUN {
set archivelog destination to '/backup/ORCL';
restore archivelog from logseq 87038 until logseq 87038;
}

Add archivelogs in to the Catalog 

RMAN> CATALOG ARCHIVELOG '/backup/ORCL';

RMAN> CATALOG ARCHIVELOG '/backup/ORCL/6_87030_810170180.dbf';

Set path of the backup and archivelog files

RMAN> CATALOG START WITH '/backup/ORCL';

Check settings 

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log where sequence#>88139 ORDER BY sequence#; 

SQL> select thread#, max(sequence#) from v$log group by thread# order by thread#;

         THREAD# MAX(SEQUENCE#)
         ---------- --------------
         1     5891
         2     5854
         3     6009
         4     6206
         5     5875
         6     5857

SQL> ARCHIVE LOG LIST 

SQL> select status from v$instance; 

         STATUS
         ------------
         MOUNTED

SQL> select name,applied from v$archived_log where applied='YES';

Recover database using Archivelogs (with some options)

SQL> RECOVER DATABASE;
SQL> RECOVER AUTOMATIC DATABASE;

SQL> alter database open;

SQL> recover automatic database using backup controlfile until cancel;

SQL> recover database using backup controlfile until cancel; 

SQL> recover automatic database until time '2016-03-22:11:00:00' using backup controlfile;

SQL> recover database using backup controlfile until time 11-JAN-2010:04:32:00; --  use format 'YYYY-MM-DD:HH24:MI:SS'

SQL> alter database open RESETLOGS;

SQL> shu immediate

SQL> startup

 

Newsletter

Enter your email to join our mailing list

Quick Contact