Oracle Database Restore From Backup & Apply To Last Archive Logs

################################
## RESTORE CONTROLFILE & SPFILE
##
################################

RMAN > connect target /
RMAN > set dbid 53241354
RMAN > startup nomount
RMAN > set controlfile autobackup format for device type disk to '/data/RESTORE/ORCL/COPIED_BACKUP/%F';
RMAN > restore spfile
        to pfile '/oracle/db_home/dbs/initORCL.ora'
        from autobackup;
## Edit PFILE (host directories )
vi initORCL.ora
%s/u01/oracle/g

RMAN> shutdown immediate;
RMAN> startup nomount pfile='/oracle/db_home/dbs/initORCL.ora';
RMAN> set controlfile autobackup format for device type disk to '/data/RESTORE/ORCL/COPIED_BACKUP/%F';
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> show all;

RMAN > catalog start with '/data/RESTORE/ORCL/COPIED_BACKUP';

####################################################################
##--NEW NAMES FOR DATAFILE AND REDOLOGS WHICH GENARATES AUTO SCRIPTS
##--select 'set NEWNAME for datafile ' || file# || ' to '  || '''' || '/data/oradata/ORCL/' || REGEXP_SUBSTR(name,'[^/]+',3,4)  || '''' || ';' from v$datafile;
##--select 'SQL "ALTER DATABASE RENAME FILE ' || '''' || '''' || member || '''' || '''' || ' TO ' || '''' || '''' || '/oradata1/oradata/ORCL/' || REGEXP_SUBSTR(member,'[[:alnum:]]+[^[:digit:]]+$') || '''' || '''' || ' ";'  from v$logfile order by member asc;
##!/bin/bash
####################################################################


####################################################################
## RECOVER DATABASE TO LAST BACKUP ARCHIVELOG
## RMAN> list backup of archivelog all;
#   List of Archived Logs in backup set 26950
#  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
#  ---- ------- ---------- --------- ---------- ---------
#
#
#  List of Archived Logs in backup set 26981
#  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
#  ---- ------- ---------- --------- ---------- ---------
#  1    43158   11085301507511 27-AUG-14 11085301553660 27-AUG-14
#  1    43159   11085301553660 27-AUG-14 11085301596363 27-AUG-14
##
##
####################################################################

export ORACLE_HOME=/oracle/db_home
export ORACLE_SID=ORCL

/oracle/db_home/bin/rman target= / << EOF
run {
allocate channel c1 type disk;
allocate channel c2 type disk;

set NEWNAME for datafile 1 to '/data/oradata/ORCL/system01.dbf';
set NEWNAME for datafile 2 to '/data/oradata/ORCL/undotbs01.dbf';
set NEWNAME for datafile 3 to '/data/oradata/ORCL/sysaux01.dbf';
set NEWNAME for datafile 4 to '/data/oradata/ORCL/users01.dbf';
set NEWNAME for datafile 5 to '/data/oradata/ORCL/DATAFILE01.DBF';
set NEWNAME for datafile 6 to '/data/oradata/ORCL/DATAFILE02.DBF';
set NEWNAME for datafile 7 to '/data/oradata/ORCL/DATAFILE03.DBF';
set NEWNAME for datafile 8 to '/data/oradata/ORCL/DATAFILE04.DBF';


SQL "ALTER DATABASE RENAME FILE ''/data2/ORCL/redolog/redo_file11.log'' TO ''/data/oradata/ORCL/redo_file11.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/data2/ORCL/redolog/redo_file12.log'' TO ''/data/oradata/ORCL/redo_file12.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/data2/ORCL/redolog/redo_file13.log'' TO ''/data/oradata/ORCL/redo_file13.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/data2/ORCL/redolog/redo_file14.log'' TO ''/data/oradata/ORCL/redo_file14.log'' ";


SET UNTIL SCN 11085301596363;
restore database;
switch datafile all;
recover database;

release channel c1;
release channel c2;

## --Commented For Archive Log apply
## --alter database open resetlogs;

}
EXIT;
EOF


##################################
### COPY MISSING ARCHIVE LOGFILES
###################################

select REGEXP_SUBSTR(name,'[^/]+',2,3)
    from v$archived_log
    where archived = 'YES' and NEXT_CHANGE# >11085301596363
# vi filelist.txt
arc_1_790985659_43161.dbf
arc_1_790985659_43162.dbf
arc_1_790985659_43163.dbf
arc_1_790985659_43164.dbf
arc_1_790985659_43160.dbf
arc_1_790985659_43165.dbf
arc_1_790985659_43166.dbf
arc_1_790985659_43167.dbf
arc_1_790985659_43168.dbf
arc_1_790985659_43169.dbf
arc_1_790985659_43170.dbf
arc_1_790985659_43171.dbf
arc_1_790985659_43172.dbf
arc_1_790985659_43173.dbf
arc_1_790985659_43174.dbf
arc_1_790985659_43175.dbf
arc_1_790985659_43176.dbf
arc_1_790985659_43177.dbf

## Write it & Quit

#tar cvf arch_files.tar --files-from filelist.txt

#scp arch_files.tar oracle@xxx.xx.xx.xxx:/data/RESTORE/ORCL/ARCH

#tar xvf arch_files.tar

##########################
## ARCHIVED LOGS RECOVERY
##########################

select name,sequence#
    from v$archived_log
    where archived = 'YES' and NEXT_CHANGE# > 11085301596363;

SQL > recover database using backup controlfile until cancel;


/data/RESTORE/ORCL/ARCH/arc_1_790985659_43160.dbf 43160
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43161.dbf 43161
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43162.dbf 43162
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43163.dbf 43163
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43164.dbf 43164
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43165.dbf 43165
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43166.dbf 43166
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43167.dbf 43167
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43168.dbf 43168
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43169.dbf 43169
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43170.dbf 43170
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43171.dbf 43171
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43172.dbf 43172
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43173.dbf 43173
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43174.dbf 43174
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43175.dbf 43175
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43176.dbf 43176
/data/RESTORE/ORCL/ARCH/arc_1_790985659_43177.dbf 43177   27.08.2014 12:25:18

######################
## QUICK CHECK !!!
## How to quickly check that Database is consistent after incomplete recovery (Point in Time Recovery) before OPEN RESETLOGS (Doc ID 1354256.1)
################################
SQL > select file#, substr(name, 1, 50), substr(tablespace_name, 1, 15) from v$datafile_header where fuzzy='YES' ;


SQL > alter database open resetlogs;

##############################################
###
###   RECOVERY COMPLETED
###
##############################################


####################
## -- TEMP FILE ADD
## -- select 'ALTER TABLESPACE ' || TABLESPACE_NAME ||  ' ADD TEMPFILE '   || '''' || '/oradata1/oradata/ORCL/' || REGEXP_SUBSTR(FILE_NAME,'[^/]+',3,4) || '''' || ' size 1G reuse autoextend on next 100M maxsize 5G;' from DBA_TEMP_FILES order by FILE_NAME asc;
####################

SQL> alter tablespace temp drop tempfile '/data2/ORCL/datafile/temp01.dbf';
SQL> alter tablespace temp drop tempfile '/data2/ORCL/datafile/temp02.dbf';
SQL> alter tablespace temp drop tempfile '/data2/ORCL/datafile/temp03.dbf';
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data/oradata/ORCL/temp01.dbf' size 1G reuse autoextend on next 100M maxsize 5G;
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data/oradata/ORCL/temp02.dbf' size 1G reuse autoextend on next 100M maxsize 5G;
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/data/oradata/ORCL/temp03.dbf' size 1G reuse autoextend on next 100M maxsize 5G;



SQL > select dbid from v$database;
SQL > select host_name from v$instance;

FTP File Copy Between Windows & Linux Machines

You can copy all files in a directory from Windows  to Linux  Server via below script.

/usr/bin/ftp -v -n 10.10.0.1 << EOF > /u01/Copy.log
user username password
cd copy_directory
bin
prompt
mget *
quit
EOF

Linux Bash Script Find Percentage Your Job (Copy , Restore ... )

If you got bored entering "du -sh" command when how much percantage done copying or restoring your database just copy below script your command prompt in that directory . And also don't forget to change Estimated_Total parameter which is your total size's of files.


 for i in {1..200} ;
do
 set -- `(du -h| sed 's/\([0-9]*\)\(.*\)/\1/')`;
 size=$1;
 Estimated_Total=375;
 echo "Estimated Percent %" $(((100 * $1)/Estimated_Total)) " Restored Size $1G";
 sleep 10;
done

Output ;

Estimated Percent % 77  Restored Size 289G
Estimated Percent % 77  Restored Size 289G
Estimated Percent % 77  Restored Size 290G

Find Specific Word in View Source Code

You can search a specific word in all views source code . And also you can configure this simple code  for package,procedure ... etc .

DECLARE
 CURSOR C1 IS  select VIEW_NAME,OWNER from dba_views where owner not in ('SYS','SYSTEM','DBSNMP','WMSYS','EXFSYS','CTXSYS','XDB','ORDSYS','ORDDATA','MDSYS','OLAPSYS','SYSMAN','RMAN','APEX_030200');
v_output CLOB :=NULL;
BEGIN
FOR C1_REC in C1 LOOP
 select (select * from ( select DBMS_METADATA.GET_DDL('VIEW' ,  C1_REC.VIEW_NAME, C1_REC.OWNER ) AS "TT"   from DUAL) where TT  like ('%@YOUR_WORD%') )   INTO v_output from DUAL ;
IF v_output is not null then
DBMS_OUTPUT.PUT_LINE(C1_REC.OWNER ||'.' || C1_REC.VIEW_NAME);
   v_output :=NULL;
END IF;  
END LOOP;
END;
/

12C Grid Control Manual Target Agent Delete

When deleting agent in 12C grid Console an error appering like that "Unknown error. java.sql.SQLException: ORA-20242: Target myhost.mydomain.com:3875:oracle_emd is monitoring other targets. It cannot be deleted

ORA-06512: at "SYSMAN.MGMT_ADMIN", line 484

ORA-06512: at "SYSMAN.MGMT_ADMIN", line 876

ORA-06512: at line 1"


You need clean manually target host from 12C Enterprise Manager.


cd $OMS_HOME/bin


[oracle@myhost bin]$ ./emcli login -username=sysman -password=<sysman password >


[oracle@myhost bin]$ ./emcli get_targets

[oracle@myhost bin]$ ./emcli sync

[oracle@myhost bin]$ ./emcli delete_target -name=<Target IP> -type="host"

Target "xx.xx.xx.xx:host" deleted successfully


[oracle@myhost bin]$ ./emcli delete_target -name=<Target IP>:3875 -type="oracle_emd"


Target "<Target IP>:3875:oracle_emd" deleted successfully


After deleting target host , you can configure this agent again ..

Oracle EM 12c Cloud Control Mail Configuration

For mail server configuration , you should set up  smtp server configuration.

Setup - > Notifications - > Notification Methods

Enter your smtp server ip and also enter user/pass if  it is authenticated by username/password your smtp server. 

I will show up alert configuration in my next blog ...

Oracle Enterprise Manager 12c Notes

Oracle Enterprise Manager 12c Cloud Control comes with up great features for database management. I decided to share my notes about EM 12c management , installation and configuration ...