Friday, May 24, 2013

Oracle 10g:Get Archives details in Mail


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></tr>';

for log in
(select
  to_char(first_time,'YY-MM-DD') day,
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
  COUNT(*) TOT
from v$log_history
where trunc(first_time) =trunc(sysdate)
group by to_char(first_time,'YY-MM-DD')
)
loop
vmsg:= UTL_TCP.CRLF ||vmsg||'<tr><td>'||log.day||'</td><td>'||log."00"||'</td><td>'||log."01"||'</td><td>'||log."02"||'</td><td>'||log."03"||'</td><td>'||log."04"||'</td><td>'||log."05"||'</td><td>'||log."06"||'</td><td>'||log."07"||'</td><td>'||log."08"||'</td><td>'||log."09"||'</td><td>'||log."10"||'</td><td>'||log."11"||'</td><td>'||log."12"||'</td><td>'||log."14"||'</td><td>'||log."13"||'</td><td>'||log."15"||'</td><td>'||log."16"||'</td><td>'||log."17"||'</td><td>'||log."18"||'</td><td>'||log."19"||'</td><td>'||log."20"||'</td><td>'||log."21"||'</td><td>'||log."22"||'</td><td>'||log."23"||'</td><td>'||log."TOT"||'</td></tr>';
i:=log.TOT;
end loop;
vmsg:=UTL_TCP.CRLF ||vmsg||'</table><br />';
vmsg:=UTL_TCP.CRLF ||vmsg||'</html>';
if(i>=60) THEN
UTL_MAIL.SEND(sender=>'bhavesh.thakkar@de.co.in', recipients=>'bhavesh.thakkar@de.co.in', cc =>'' , subject=>'*** Generated Logs', message => vmsg,  mime_type => 'text/html');
END IF;
END;



2.Create the scheduler which mail info into mail.


Thursday, May 23, 2013

Oracle 10g:Create/Migrate Database from Exported Datapump

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.
startup mount reastrinct
Before that check all location of Datafiles,log and control file location.

select File_name from dba_data_files;
Select File_name from dba_temp_files;
Select MEMBER from v$logfile;
Select Name from v$controlfile;

6.Drop Database

SQL> select name from v$database;

NAME
---------------------------
****

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

7.Check the file exist on step 5.
If any file exist remove it manually because it will trouble to create new database.

8.Create New Blank Database with default users and Tablespaces.
Add the entry in oratab or use oradim utility.

9.Remove  or backup all old dump logs and trace files.

10.Start Database in nomount mode.
  Startup nomount restrict

11.Create Database with same directory structure as previous.

CREATE DATABASE *****
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY manager
   LOGFILE GROUP 1 ('/oralogs/oradata/*****/redo01.log') SIZE 50M,
           GROUP 2 ('/oralogs/oradata/***/redo02.log') SIZE 50M,
           GROUP 3 ('/oralogs/oradata/*****/redo03.log') SIZE 50M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET US7ASCII
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/oralogs/oradata/****/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/oralogs/oradata/****/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE testuser
      DATAFILE '/oralogs/oradata/****/testuser01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/oralogs/oradata/*****/temp0001.dbf'
      SIZE 200M REUSE
   UNDO TABLESPACE undotbs1
      DATAFILE '/oralogs/oradata/*****/undotbs01.dbf'
      SIZE 200M;






Check the status


Database created.

SQL> SQL> SQL> SQL>

Execute the procedure of catlog,catproc,catexp,urlrp

SQL>@$ORACLE_HOME/rdbms/admin/catalog
SQL>@$ORACLE_HOME/rdbms/admin/catproc
SQL>@$ORACLE_HOME/rdbms/admin/catexp
SQL>@$ORACLE_HOME/rdbms/admin/utlrp

Take Tea for 15 minutes.


12.Its time to full import the datapump export dump.Note that if you ignore indexes.It will much faster.Datapump much faster so no need to worry about down time when migration by datapump utility.


Create Directory for dump location.

connect as sysdba to access it without grant the privileges.

Create directory impdp as '/oracle7/impdp/';

Copy the full export dump this location.

 cp  *****_full.dmp /oracle7/impdp/

Execute this command to full import.It will take time longer then export.

impdp system/manager dumpfile=****_full.dmp directory=impdp logfile=*****_import.log full=y
13.Check Logs.There are number of errors come it.

Ignore errors like Object already exist.

Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"SYSAUX" already exists
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-02236: invalid file name

Failing sql is:
E_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists

ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists

ORA-31684: Object type ROLE:"OEM_MONITOR" already exists



ORA-39151: Table "SYSTEM"."DEF$_ERROR" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

ORA-39151: Table "SYSTEM"."DEF$_DESTINATION" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SYSTEM"."DEF$_CALLDEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "SYSTEM"."DEF$_DEFAULTDEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip



These are the errors of already created system and sysaux tablespaces that objects already resides in database.

Mainly Check the mandatory records of old count of particular schema.

You may query of dba_objects where all objects come in it.

Check export log where rows count displayed and also match with import logs.

Now, You may ready  to work on newly exported database with reset of high water marks and indexes.

Take whole database statistics immediately.

***All the best **



Tuesday, May 21, 2013

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>::Stale Table statistics info::</b></td></tr>';
--for table statistics
for tbl in
(select
      owner,TABLE_NAME,(num_rows*avg_row_len)/1024/1024 sizeinmb 
from dba_tab_statistics
where STALE_STATS='YES' and owner='PIN'
)
loop
t:=t+1;
vsql:='EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'''||tbl.owner||''',tabname=>'''||tbl.TABLE_NAME||''',CASCADE=>TRUE,DEGREE=>1,estimate_percent =>100);';
vmsg:=vmsg||'<tr><td>--'||tbl.TABLE_NAME||'::'||to_char(tbl.sizeinmb,'9,999')||' Mbs</td></tr>';
vmsg:=vmsg||'<tr><td>'||vsql||'</tr></td>';
end loop;
vmsg:=vmsg||'</table></html>';

if (i >0 or t>0) THEN
UTL_MAIL.SEND(sender=>'DatabaseExperts@de.com', recipients=>'List of Comma separated email', cc =>'' , subject=>'DatabaseName Stale statistics info.', message => vmsg,  mime_type => 'text/html');
End if;
end;

2.Schedule this procedure by DBMS_Scheduler.

BEGIN
    sys.DBMS_SCHEDULER.CREATE_SCHEDULE (
          
        repeat_interval  => 'FREQ=HOURLY;INTERVAL=4',    
        comments => 'This is job wil mail stale table/indexes info',
        schedule_name  => '"Stale_Schedule"');
       
END;

3.Create DBMS_JOB.

BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"SYSTEM"."Stale_info_job"',
            schedule_name => '"SYSTEM"."Stale_Schedule"',
            job_type => 'STORED_PROCEDURE',
            job_action => '"SYSTEM"."USP_STALE_STATS_INFO"',
            number_of_arguments => 0,
            job_class => '"SYS"."DEFAULT_JOB_CLASS"',
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'This job will mail table/index stale statistics info');

    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
             name => '"SYSTEM"."Stale_info_job"',
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);
 
    SYS.DBMS_SCHEDULER.enable(
             name => '"SYSTEM".""');
END;
/

4.Run the sql statement.

::Stale index statistics info::
EXEC dbms_stats.gather_index_stats('***','*****' , estimate_percent=>100);

::Stale Table statistics info::
--***_T:: 0 Mbs
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'***',tabname=>'**_T',CASCADE=>TRUE,DEGREE=>1,estimate_percent =>100);

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; ';
}