Posts

Showing posts from May, 2013

Oracle 10g:Get Archives details in Mail

Image
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...
Post a Comment
Read more

Oracle 10g:Create/Migrate Database from Exported Datapump

Image
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...
Post a Comment
Read more

Oracle 10g:Get Stale statistics info in mail

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>:...
3 comments
Read more

Oracle 10g:Get RMAN Backup Completion Mail

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; '; }
Post a Comment
Read more