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
