Database Experts

 RMAN Backup restore Sometime RMAN Backup needs to restore other machine and check weather it is valid and if any issue come and need to restore to another server.For new test server creation by rman manual restore. RMAN Cloning also one of the finest way to make new cloned database. Follow this easy steps to rman backup restore. 1.Take full rman backup. 2.Map drive of rman backup to destination server or manually copy rman all backup to destination server.    There is one problem of found which files need to copy.If source database available then use this query and find the name of paryticular backup. select tag,to_char(completion_time,'DD-MON-YY') backup_date,'CATALOG BACKUPPIECE '''||fname||''';' fname,round(sum(bs_bytes)/1024/1024/1024,2) backup_size_gb from v$backup_files where trunc(completion_time)=trunc(sysdate) Group by tag,to_char(completion_time,'DD-MON-YY'),fname order by to_char(completi...

Hi, It is possible when sometimes we removed archives from archive destination and it requires for synchronize standby database. For this need to restore from one of the available backup like RMAN,hot backup etc. We here use RMAN backup script to get the deleted or removed archive logs. --For All Archive logs from RMAN backup. RUN  {  ALLOCATE CHANNEL C1 DEVICE TYPE DISK;  RESTORE ARCHIVELOG ALL;  RELEASE CHANNEL C1; } --For only single archive by sequence number. RUN  {  ALLOCATE CHANNEL C1 DEVICE TYPE DISK;  RESTORE ARCHIVELOG LOGSEQ 12345 ;  RELEASE CHANNEL C1; } --For range of sequence number of archives. RUN  {  ALLOCATE CHANNEL C1 DEVICE TYPE DISK;  RESTORE ARCHIVELOG FROM LOGSEQ 1 UNTIL 12345 ; RELEASE CHANNEL C1; }

Execute Unix/Linux commands by Java Source This is very easy with few steps. 1.CREATE JAVA SOURCE into oracle 10g database. Install jvm.Connect to SQL with sysdba priviledges. @$ORACLE_HOME/javavm/install/initjvm.sql   CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS import java.io.*; public class Host {   public static void executeCommand(String command) {     try {       String[] finalCommand;       if (isWindows()) {         finalCommand = new String[4];         // Use the appropriate path for your windows version.         //finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";    // Windows NT/2000         finalCommand[0] = "C:\\windows\\system32\\cmd.exe";    // Windows XP/2003     ...

1.Create the stored procedure generate mail. CREATE OR REPLACE PROCEDURE USP_Daily_Generated_Logs AS vmsg varchar2(10000); i int; begin vmsg:='<html><table border=1 align="center"><tr bgcolor="#8B6161"><td align="center" colspan=26><b>Daily Genereated Archive Logs</b></td></tr>' ; vmsg:= vmsg||'<tr><td>day</td><td>00</td><td>01</td><td>02</td><td>03</td><td>04</td><td>05</td><td>06</td><td>07</td><td>08</td><td>09</td><td>10</td><td>11</td><td>12</td><td>14</td><td>13</td><td>15</td><td>16</td><td>17</td><td>18</td><td>19</td><td>20</td><td>21</td><td>22</td><td>23</td><td>TOT</td...

Database Creation/Migration By Datapump Utility.   This is implemented on test environment. For this,Database creation with same server and all related mount points and directory created before import. 1.Use the Utility of Estimate for size of dump files and all table info. expdp system/manager directory=expdp logfile=***_exp.log FULL=Y ESTIMATE_ONLY=Y PARALLEL=4 ESTIMATE=BLOCKS JOB_NAME=ESTIMATE_ONLY_FULL_JOB 2.Take Full export dump. expdp system/manager directory=expdp  dumpfile=***_full.dmp logfile=****_exp.log FULL=Y  PARALLEL=4 JOB_NAME=full_export STATUS=100 Check the status of job FULL_EXPORT Worker 4 Status:   State: WORK WAITING Job "SYSTEM"."FULL_EXPORT" successfully completed at 11:23:35 3.Check all previous database files and control file completely removed from the server. 4.Test Server purpose remove the database and files .drop the whole database. 5.Start Database in mount restrict mode. startu...

We need to update the statistics of those statistics stale. For this,Schedule one procedure that mail the info of stale statistics in oracle. 1.Create Procedure for mail the info. CREATE OR REPLACE PROCEDURE USP_STALE_STATS_INFO as vmsg varchar2(10000); vsql varchar2(5000); i int; t int; begin vmsg:='<html><table><tr><td ><b>::Stale index statistics info::</b></td></tr>'; i:=0; t:=0; --index statistics.. for ind in (select owner,table_name,index_name from dba_ind_statistics where   STALE_STATS='YES' and owner='PIN' ) loop i:=i+1; vsql:='EXEC dbms_stats.gather_index_stats('''||ind.owner||''','''||ind.index_name||''' , estimate_percent=>100);'; vmsg:=vmsg||'<tr><td>'||vsql||'</tr></td>'; end loop; vmsg:=vmsg||'</table><br />'; vmsg:=vmsg||'<table><tr><td><b>:...

Follow this steps to get rman backup completion mail. 1.Create Procedure to for mail. CREATE OR REPLACE PROCEDURE usp_rman_backup_info as Begin UTL_MAIL.SEND(sender=>'DatabaseExperts@de.com', recipients=>'Comma separated email list',subject=>'DatabaseName RMAN Backup info', message => 'RMAN Backup completed @'||to_char(sysdate,'hh24:mi:ss')); end; 2.Update RMAN Script. SQL ' BEGIN SYS.usp_rman_backup_info; END; '; at end of script. delete noprompt obsolete; release channel ch1; release channel ch2; release channel ch3; release channel ch4; SQL ' BEGIN SYS.usp_rman_backup_info; END; '; }