Please check the link for all kind of information on STATSPACK, its really helpfull.
Regards,
~ Nv
Regards,
~ Nv
Wednesday, February 23, 2011
New Features in 11g on Backup and Recovery
- Block change tracking in standby.
Now incremental backups can be faster now in standby database by enabling the block change tracking in it.
- Active Database Duplication
Now in 11g we can directly duplicate a database over the network without having to back up and provide the source database files. This direct database duplication is called active database duplication. It can be done either with Database Control or through RMAN. Instance that runs the duplicated database is called auxiliary instance.
- Archived Redo Log Failover
When backing up archived redo logs RMAN only includes a single copy of each archived redo log, regardless of how many archive log destinations are being written to. The Oracle 11g archived redo log failover feature allows RMAN to complete a backup provided at least one valid copy of each archived redo log is present in one of the specified archive destinations. If RMAN finds a log file containing corrupt blocks, it searches the other archive destinations for a valid copy to back up.
- Archived Log Deletion Policy Enhancements
The extended syntax allows for configurations where logs are eligible for deletion only after being applied to, or transferred to, one or more standby database destinations.
ARCHIVELOG DELETION POLICY {CLEAR | TO {APPLIED ON [ALL] STANDBY |
BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |
NONE | SHIPPED TO [ALL] STANDBY}
[ {APPLIED ON [ALL] STANDBY | BACKED UP integer TIMES TO DEVICE TYPE deviceSpecifier |
NONE | SHIPPED TO [ALL] STANDBY}]...}
- Multisession Backups
A file section is defined as a contiguous range of blocks from a single file. The SECTION SIZE parameter in the BACKUP command tells RMAN to create a backup set where each backup piece contains the blocks from one file section, allowing the backup of large files to be parallelized across multiple channels.
The following example of a multisection backup sets the parallelism to 4, allowing a tablespace with a single 1000M datafile to be backed up in 4x250M sections.
The following example of a multisection backup sets the parallelism to 4, allowing a tablespace with a single 1000M datafile to be backed up in 4x250M sections.
# One-off configuration of device type and parallelism.
CONFIGURE DEVICE TYPE sbt PARALLELISM 4;
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
# Backup large tablespace in 4 sections.
RUN {
BACKUP SECTION SIZE 250M TABLESPACE my_1000M_ts;
}
Some points to remember about multisection backups include:
- If the section size is larger than the file size, RMAN does not use a multisection backup for the file.
- If the section size is so small that more than 256 sections would be produced, RMAN increases the section size such that 256 sections will be created.
- SECTION SIZE and MAXPIECESIZE cannot be used together.
- A backup set never contains a partial datafile, regardless of whether or not it is a multisection backup.
- Optimized Backing Up of Undo Data
In Oracle Database 11g, during a backup, the committed data isn’t backed up, thus leading to a saving of storage space as well as faster backups for large OLTP-type databases. Since the new optimized undo backup is automatically enabled, you don’t have to configure anything special to take advantage of this feature.
- Enhanced Block Media Recovery
In Oracle Database 11g, there is a new command to perform block media recovery, named the recover ... block command replacing the old blockrecover command. The new command is more efficient since because it searches the flashback logs for older uncorrupted versions of the corrupt blocks. This requires the database to work in archivelog mode and has the Database Flashback enabled.
If flashback logs are present, RMAN will use these in preference to backups during block media recovery (BMR), which can significantly improve BMR speed.
- Backup of Read-Only Transportable Tablespaces:
In previous versions of Oracle, transportable tablespaces could only be backed up if they were in read/write mode. It is now possible to backup read-only transportable tablespaces.
9. Data Recovery Advisor
Data Recovery Advisor is a built-in tool to automatically diagnose data failures and recommend repairs. You can repair failures manually or request that they be repaired automatically. Data Recovery Advisor supports the LIST FAILURE, CHANGE FAILURE, ADVISE FAILURE, and REPAIR FAILURE commands.
- Flashback data archive
A flashback data archive enables the database to automatically track and store all transactional changes to a table for the duration of its lifetime. Thus, you do not need to build this functionality into database applications.
A flashback data archive is especially useful for compliance, audit reports, data analysis, and DSS (Decision Support Systems). You can use some of the logical flashback features with a flashback data archive to access data from far in the past.
11. Data Block Corruption Prevention and Detection Parameters:
In Oracle Database 11g, several database components and utilities in addition to RMAN can detect a corrupt block and record it in the view "V$DATABASE_BLOCK_CORRUPTION". Oracle Database automatically updates this view when block corruptions are detected or repaired. The benefit is that the time it takes to discover block corruptions is shortened.
12. Online Patching:
Initially available for Linux, this allows certain diagnostic patches to be installed in a completely online manner, i.e. without requiring the database to be brought down and applications to be disconnected.
Regards
~ Nv
Tuesday, February 8, 2011
Sending mails using inbuilt package in oracle 10g
Below package used to scan backup logfile which is generated by RMAN and sends mail to the DBA or group. Three seperate blocks are created for 3 different types of backup.
1. Daily Hot backup
2. Weekly Hot Backup
3. Export/Import
All the logfile locations are mapped to a dicrectory object in the database and by using that the procedure reads the logifle and scans for the error message and executes the procedures based on the result. Please read all the procedures carefully before implementing. This has been impletemented in 10g and its sucessfully running for more than a year.
CREATE OR REPLACE PACKAGE BACKUPTEST.Pkg_Backup_status
as
procedure pr_Hotbackup_daily
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
);
procedure pr_Hotbackup_weekly
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
);
procedure pr_Export_daily
(
p_Exp_error out clob
);
procedure pr_mail
(
p_var varchar2
);
Function Templte(ip in number)
return varchar2;
end Pkg_Backup_status;
/
CREATE OR REPLACE PACKAGE BODY BACKUPTEST.Pkg_Backup_status
as
procedure pr_Hotbackup_daily
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
)
as
Dat1 varchar2(4000);
F UTL_FILE.FILE_TYPE;
fname varchar2(30);
check1 varchar2(4000);
check2 varchar2(4000);
err varchar2(1000);
F_exists boolean;
Flength Number;
Fblock Number;
error_text clob;
chk_status varchar2(20);
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
begin
select 'backup_daily_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
begin
select a,b,c into chk_status,bkpstart,bkpend from (select status a,to_char(START_TIME,'dd/mm/yyyy hh24:mi:ss') b,to_char(END_TIME,'dd/mm/yyyy hh24:mi:ss') c from sys.V_$RMAN_STATUS where operation='BACKUP' and to_char(start_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
and object_type like 'DB%' order by 2 desc)where rownum=1;
exception when no_data_found then
chk_status := null;
end;
UTL_FILE.fgetattr('BACKUPTEST',fname,F_exists,Flength,Fblock);
IF F_exists THEN
F:= utl_file.FOPEN('BACKUPTEST',fname,'R');
check1 := 'RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============' ;
check2 := 'Recovery Manager complete.';
loop
utl_file.GET_LINE(F,Dat1);
if (dat1 = check1 ) then
loop
begin
utl_file.get_line(F,Err);
error_text := error_text|| CRL || err;
exception
when no_data_found then
UTL_FILE.FCLOSE(F);
goto NV;
end;
end loop;
elsif (dat1 =check2) then
error_text := 'Backup completed successfully';
goto NV;
end if;
end loop;
<>
p_error_text := error_text;
else
p_error_text := 'The Hot backup is not scheduled today';
bkpstart := 'No backup';
bkpend := 'No backup';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(F);
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_Hotbackup_weekly
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
)
as
Dat1 varchar2(4000);
F UTL_FILE.FILE_TYPE;
fname varchar2(30);
check1 varchar2(4000);
check2 varchar2(4000);
err varchar2(1000);
F_exists boolean;
Flength Number;
Fblock Number;
error_text clob;
chk_status varchar2(20);
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
begin
select 'backup_complete_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
begin
select a,b,c into chk_status,bkpstart,bkpend from (select status a,to_char(START_TIME,'dd/mm/yyyy hh24:mi:ss') b,to_char(END_TIME,'dd/mm/yyyy hh24:mi:ss') c from sys.V_$RMAN_STATUS where operation='BACKUP' and to_char(start_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
and object_type like 'DB%' order by 2 desc)where rownum=1;
exception when no_data_found then
chk_status := null;
end;
UTL_FILE.fgetattr('BACKUPTEST_WEEKLY',fname,F_exists,Flength,Fblock);
IF F_exists THEN
F:= utl_file.FOPEN('BACKUPTEST_WEEKLY',fname,'R');
check1 := 'RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============' ;
check2 := 'Recovery Manager complete.';
loop
utl_file.GET_LINE(F,Dat1);
if (dat1 = check1 ) then
loop
begin
utl_file.get_line(F,Err);
error_text := error_text|| CRL || err;
exception
when no_data_found then
UTL_FILE.FCLOSE(F);
goto NV;
end;
end loop;
elsif (dat1 =check2) then
error_text := 'Backup completed successfully';
goto NV;
end if;
end loop;
<>
p_error_text := error_text;
else
p_error_text := 'The Hot backup is not scheduled today';
bkpstart := 'No backup';
bkpend := 'No backup';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(F);
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_Export_daily
(
p_Exp_error out clob
)
as
Dat2 varchar2(4000);
F1 UTL_FILE.FILE_TYPE;
F2 UTL_FILE.FILE_TYPE;
dat3 varchar2(4000);
err1 varchar2(1000);
fname varchar2(30);
mail_content clob;
F_exists boolean;
Flength Number;
Fblock Number;
CRL CHAR(2) := CHR(10);
exp_chk1 varchar2(100);
exp_chk2 varchar2(100);
Exp_error clob;
j number default 0;
n number default 0;
m number default 0;
begin
select 'backup_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
UTL_FILE.fgetattr('DTPUMP','Expdp.log',F_exists,Flength,Fblock);
IF F_exists THEN
F1:= utl_file.FOPEN('DTPUMP','Expdp.log','R');
exp_chk1 := 'EXP-00000: Export terminated unsuccessfully';
exp_chk2 := 'Export terminated successfully without warnings.';
loop
utl_file.GET_LINE(F1,Dat2);
j:=j+1;
if (dat2 = exp_chk2 ) then
Exp_error := dat2;
goto NV1;
end if;
end loop;
<>
p_Exp_error := Exp_error;
else
p_Exp_error := 'The export backup is not scheduled today';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
if (j < 4) then
m := j;
else
m:= 4;
end if;
F2 := utl_file.FOPEN('DTPUMP','Expdp.log','R');
loop
begin
utl_file.GET_LINE(F2,Err1);
N := N + 1;
if (N >= (j-m)) then
Exp_error := Exp_error || CRL || err1;
end if;
exception
when no_data_found then
UTL_FILE.FCLOSE(F2);
UTL_FILE.FCLOSE(F1);
p_Exp_error:= Exp_error;
exit;
end;
end loop;
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_mail
(
p_var varchar2
)
as
templt1 varchar2(200);
templt1a varchar2(200);
templt2 varchar2(200);
templt3 varchar2(200);
templt4 varchar2(200);
mail_content clob;
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
bkpstart varchar2(100);
bkpend varchar2(100);
error_text clob;
Exp_error clob;
F_exists boolean;
Flength Number;
Fblock Number;
begin
templt1 := Templte(23)||' DAILY BACKUP '||Templte(23);
templt1A := Templte(25)||' TIMINGS '||Templte(25);
templt2 := Templte(23)||' HOTBACKUP STATUS '||Templte(23);
templt3 := Templte(23)||' LOGICAL BACKUP STATUS '||Templte(23);
templt4 := Templte(23)||' WEEKLY BACKUP '||Templte(23);
if ( p_var='D') then
pr_Hotbackup_daily(bkpstart,bkpend,error_text);
elsif (p_var='W') then
pr_Hotbackup_weekly(bkpstart,bkpend,error_text);
end if;
pr_Export_daily (Exp_error);
if ( p_var='D') then
mail_content := 'Dear DBAs,'||CRLF||templt1||CRLF||templt1a||CRLF||' Hot Backup start_time:'||bkpstart||CRL||'Hot Backup End_time :'||bkpend||CRLF||''||templt2||''||CRLF||''|| Error_text ||''||CRLF||templt3||CRLF||''|| Exp_error ||''||CRLF||'Thank you,';
utl_mail.SEND('Databasename ','mailid@gmail.com',null,null,'DB BackupStatus',mail_content,null,null);
elsif (p_var='W') then
mail_content := 'Dear DBAs,'||CRLF||templt4||CRLF||templt1a||CRLF||' Hot Backup start_time:'||bkpstart||CRL||'Hot Backup End_time :'||bkpend||CRLF||''||templt2||''||CRLF||''|| Error_text ||''||CRLF||templt3||CRLF||''|| Exp_error ||''||CRLF||'Thank you,';
utl_mail.SEND('Databasename ','mailid@gmail.com',null,null,'DB BackupStatus',mail_content,null,null);
end if;
UTL_FILE.fgetattr('DTPUMP','Expdp.log',F_exists,Flength,Fblock);
IF F_exists THEN
utl_file.fremove('DTPUMP','Expdp.log');
end if;
end;
Function Templte
(ip in number)
return varchar2
as
Lne varchar2(30);
i number;
equls varchar2(30);
begin
for i in 1..ip
loop
Lne:=trim(Lne)||trim('=');
end loop;
return Lne;
end;
end Pkg_Backup_status;
/
1. Daily Hot backup
2. Weekly Hot Backup
3. Export/Import
All the logfile locations are mapped to a dicrectory object in the database and by using that the procedure reads the logifle and scans for the error message and executes the procedures based on the result. Please read all the procedures carefully before implementing. This has been impletemented in 10g and its sucessfully running for more than a year.
CREATE OR REPLACE PACKAGE BACKUPTEST.Pkg_Backup_status
as
procedure pr_Hotbackup_daily
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
);
procedure pr_Hotbackup_weekly
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
);
procedure pr_Export_daily
(
p_Exp_error out clob
);
procedure pr_mail
(
p_var varchar2
);
Function Templte(ip in number)
return varchar2;
end Pkg_Backup_status;
/
CREATE OR REPLACE PACKAGE BODY BACKUPTEST.Pkg_Backup_status
as
procedure pr_Hotbackup_daily
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
)
as
Dat1 varchar2(4000);
F UTL_FILE.FILE_TYPE;
fname varchar2(30);
check1 varchar2(4000);
check2 varchar2(4000);
err varchar2(1000);
F_exists boolean;
Flength Number;
Fblock Number;
error_text clob;
chk_status varchar2(20);
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
begin
select 'backup_daily_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
begin
select a,b,c into chk_status,bkpstart,bkpend from (select status a,to_char(START_TIME,'dd/mm/yyyy hh24:mi:ss') b,to_char(END_TIME,'dd/mm/yyyy hh24:mi:ss') c from sys.V_$RMAN_STATUS where operation='BACKUP' and to_char(start_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
and object_type like 'DB%' order by 2 desc)where rownum=1;
exception when no_data_found then
chk_status := null;
end;
UTL_FILE.fgetattr('BACKUPTEST',fname,F_exists,Flength,Fblock);
IF F_exists THEN
F:= utl_file.FOPEN('BACKUPTEST',fname,'R');
check1 := 'RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============' ;
check2 := 'Recovery Manager complete.';
loop
utl_file.GET_LINE(F,Dat1);
if (dat1 = check1 ) then
loop
begin
utl_file.get_line(F,Err);
error_text := error_text|| CRL || err;
exception
when no_data_found then
UTL_FILE.FCLOSE(F);
goto NV;
end;
end loop;
elsif (dat1 =check2) then
error_text := 'Backup completed successfully';
goto NV;
end if;
end loop;
<
p_error_text := error_text;
else
p_error_text := 'The Hot backup is not scheduled today';
bkpstart := 'No backup';
bkpend := 'No backup';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(F);
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_Hotbackup_weekly
(
bkpstart out varchar2,
bkpend OUT varchar2,
p_error_text out clob
)
as
Dat1 varchar2(4000);
F UTL_FILE.FILE_TYPE;
fname varchar2(30);
check1 varchar2(4000);
check2 varchar2(4000);
err varchar2(1000);
F_exists boolean;
Flength Number;
Fblock Number;
error_text clob;
chk_status varchar2(20);
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
begin
select 'backup_complete_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
begin
select a,b,c into chk_status,bkpstart,bkpend from (select status a,to_char(START_TIME,'dd/mm/yyyy hh24:mi:ss') b,to_char(END_TIME,'dd/mm/yyyy hh24:mi:ss') c from sys.V_$RMAN_STATUS where operation='BACKUP' and to_char(start_time,'dd/mm/yyyy') = to_char(sysdate,'dd/mm/yyyy')
and object_type like 'DB%' order by 2 desc)where rownum=1;
exception when no_data_found then
chk_status := null;
end;
UTL_FILE.fgetattr('BACKUPTEST_WEEKLY',fname,F_exists,Flength,Fblock);
IF F_exists THEN
F:= utl_file.FOPEN('BACKUPTEST_WEEKLY',fname,'R');
check1 := 'RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============' ;
check2 := 'Recovery Manager complete.';
loop
utl_file.GET_LINE(F,Dat1);
if (dat1 = check1 ) then
loop
begin
utl_file.get_line(F,Err);
error_text := error_text|| CRL || err;
exception
when no_data_found then
UTL_FILE.FCLOSE(F);
goto NV;
end;
end loop;
elsif (dat1 =check2) then
error_text := 'Backup completed successfully';
goto NV;
end if;
end loop;
<
p_error_text := error_text;
else
p_error_text := 'The Hot backup is not scheduled today';
bkpstart := 'No backup';
bkpend := 'No backup';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(F);
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_Export_daily
(
p_Exp_error out clob
)
as
Dat2 varchar2(4000);
F1 UTL_FILE.FILE_TYPE;
F2 UTL_FILE.FILE_TYPE;
dat3 varchar2(4000);
err1 varchar2(1000);
fname varchar2(30);
mail_content clob;
F_exists boolean;
Flength Number;
Fblock Number;
CRL CHAR(2) := CHR(10);
exp_chk1 varchar2(100);
exp_chk2 varchar2(100);
Exp_error clob;
j number default 0;
n number default 0;
m number default 0;
begin
select 'backup_'||to_char(sysdate,'mm')||'_'||to_char(sysdate,'dd')||'_'||to_char(sysdate,'yyyy')||'.log' into fname from dual;
UTL_FILE.fgetattr('DTPUMP','Expdp.log',F_exists,Flength,Fblock);
IF F_exists THEN
F1:= utl_file.FOPEN('DTPUMP','Expdp.log','R');
exp_chk1 := 'EXP-00000: Export terminated unsuccessfully';
exp_chk2 := 'Export terminated successfully without warnings.';
loop
utl_file.GET_LINE(F1,Dat2);
j:=j+1;
if (dat2 = exp_chk2 ) then
Exp_error := dat2;
goto NV1;
end if;
end loop;
<
p_Exp_error := Exp_error;
else
p_Exp_error := 'The export backup is not scheduled today';
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
if (j < 4) then
m := j;
else
m:= 4;
end if;
F2 := utl_file.FOPEN('DTPUMP','Expdp.log','R');
loop
begin
utl_file.GET_LINE(F2,Err1);
N := N + 1;
if (N >= (j-m)) then
Exp_error := Exp_error || CRL || err1;
end if;
exception
when no_data_found then
UTL_FILE.FCLOSE(F2);
UTL_FILE.FCLOSE(F1);
p_Exp_error:= Exp_error;
exit;
end;
end loop;
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
end;
procedure pr_mail
(
p_var varchar2
)
as
templt1 varchar2(200);
templt1a varchar2(200);
templt2 varchar2(200);
templt3 varchar2(200);
templt4 varchar2(200);
mail_content clob;
CRLF CHAR(2) := CHR(10) || CHR(13);
CRL CHAR(2) := CHR(10);
bkpstart varchar2(100);
bkpend varchar2(100);
error_text clob;
Exp_error clob;
F_exists boolean;
Flength Number;
Fblock Number;
begin
templt1 := Templte(23)||' DAILY BACKUP '||Templte(23);
templt1A := Templte(25)||' TIMINGS '||Templte(25);
templt2 := Templte(23)||' HOTBACKUP STATUS '||Templte(23);
templt3 := Templte(23)||' LOGICAL BACKUP STATUS '||Templte(23);
templt4 := Templte(23)||' WEEKLY BACKUP '||Templte(23);
if ( p_var='D') then
pr_Hotbackup_daily(bkpstart,bkpend,error_text);
elsif (p_var='W') then
pr_Hotbackup_weekly(bkpstart,bkpend,error_text);
end if;
pr_Export_daily (Exp_error);
if ( p_var='D') then
mail_content := 'Dear DBAs,'||CRLF||templt1||CRLF||templt1a||CRLF||' Hot Backup start_time:'||bkpstart||CRL||'Hot Backup End_time :'||bkpend||CRLF||''||templt2||''||CRLF||''|| Error_text ||''||CRLF||templt3||CRLF||''|| Exp_error ||''||CRLF||'Thank you,';
utl_mail.SEND('Databasename ','mailid@gmail.com',null,null,'DB BackupStatus',mail_content,null,null);
elsif (p_var='W') then
mail_content := 'Dear DBAs,'||CRLF||templt4||CRLF||templt1a||CRLF||' Hot Backup start_time:'||bkpstart||CRL||'Hot Backup End_time :'||bkpend||CRLF||''||templt2||''||CRLF||''|| Error_text ||''||CRLF||templt3||CRLF||''|| Exp_error ||''||CRLF||'Thank you,';
utl_mail.SEND('Databasename ','mailid@gmail.com',null,null,'DB BackupStatus',mail_content,null,null);
end if;
UTL_FILE.fgetattr('DTPUMP','Expdp.log',F_exists,Flength,Fblock);
IF F_exists THEN
utl_file.fremove('DTPUMP','Expdp.log');
end if;
end;
Function Templte
(ip in number)
return varchar2
as
Lne varchar2(30);
i number;
equls varchar2(30);
begin
for i in 1..ip
loop
Lne:=trim(Lne)||trim('=');
end loop;
return Lne;
end;
end Pkg_Backup_status;
/
Changing Database characterset using CSSCAN and CSALTER script
Using csscan to check compatibility and then change the character set as below
Step 1) Check the current character set of the target database.
SQL> Select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------
WE8MSWIN1252
Step 2) Configure CSSCAN
Csscan is a tool that allows you to see the impact of a character set change. This is tool is available from 8i and above. To install the tool follow the steps below
cd $ORACLE_HOME/rdbms/admin
set oracle_sid=
sqlplus
SQL>conn / as sysdba
SQL> spool csminst.log
SQL> START csminst.sql
Character set scanner creates a schema called CSMIG for maintaing its data in the database, and is created using the csminst.sql script.
The default tablespace for this user should be system you can change it.
alter user csmig default tablespace SYSTEM
Check file csminst.log for any errors after installing csscan.
Step 3) To check if csscan is working fine, run the command and check for the output as shown.
D:\Oracle10g\product\10.2.0\db_1\BIN>csscan TABLE=SYS.SQL_VERSION$ FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=d:\instchk CAPTURE=N P
ROCESS=1 ARRAY=1024000
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Tue Feb 8 14:47:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: sys as sysdba
Password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Enumerating table to scan...
. process 1 scanning SYS.SQL_VERSION$[AAAAIDAABAAAA/xAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
D:\Oracle10g\product\10.2.0\db_1\BIN>
Once the csscan is completed, then csalter script has to be run, It will change the character set to the desired one used in csscan TOCHAR variable.
Some conditions has to be met before running the script csalter.
Csalter the Csscan output needs to be
* changeless for all CHAR VARCHAR2, and LONG data (Data Dictionary and user)
* changeless for all USER CLOB
* convertible and changeless for all Data Dictionary CLOB
in order to run Csalter you need to see in the instchk.txt (logfile which is created in the previous run) file under [Scan Summary] .All character type application data remain the same in the new character set
and under [Data Dictionary Conversion Summary]
The data dictionary can be safely migrated using the CSALTER script
Step 4) Run the csscan command as provided below.
D:\Oracle10g\product\10.2.0\db_1\BIN>csscan FULL=Y FROMCHAR=WE8MSWIN1252 TOCHAR=utf8 ARRAY=1024000 LOG=d:\dbcharchng CAPTURE=Y
SUPPRESS=1000 PROCESS=4
. process 1 scanning SYSMAN.MGMT_DELTA_COMP_KEY_COLS[AAAMQiAADAAAGORAAA]
. process 2 scanning SYSMAN.MGMT_DELTA_IDS[AAAMP+AADAAAGJxAAA]
. process 3 scanning SYSMAN.MGMT_JOB_EXEC_LOCKS[AAAMS4AADAAAGgxAAA]
. process 1 scanning SYS.WRH$_SERVICE_WAIT_CLASS[AAAMjkAADAAABAhAAA]
. process 4 scanning SYSMAN.MGMT_HA_RAC_INTR_CONN[AAAMeDAADAAAHMRAAA]
. process 2 scanning SYS.WRH$_ROWCACHE_SUMMARY[AAAMhfAADAAABOpAAA]
. process 3 scanning SYS.WRH$_DLM_MISC[AAAMjMAADAAAA5RAAA]
. process 1 scanning SYS.WRH$_SQLSTAT[AAAMiWAADAAAAsJAAA]
. process 4 scanning SYS.WRH$_ACTIVE_SESSION_HISTORY[AAAMjUAADAAAA8RAAA]
. process 2 scanning SYS.WRH$_PARAMETER[AAAMjEAADAAAA1RAAA]
. process 3 scanning SYSTEM.LOGMNR_ATTRIBUTE$[AAABbdAADAAAAd5AAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
Step 5). Once the CSSCAN has been run then you can check the logfile , text file and the error file which is generated while running the CSSCAN in the previous step. there has to be no errors in the files, if yes then you can proceed with the next step by running CSALTER script as below.
D:\Oracle10g\product\10.2.0\db_1\BIN>sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Feb 8 15:31:44 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: sys/sys as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @D:\Oracle10g\product\10.2.0\db_1\RDBMS\ADMIN\csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validility...
begin converting system objects
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
4 rows in table MDSYS.SDO_XML_SCHEMAS are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
80 rows in table SYS.METASTYLESHEET are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
2308 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
4 rows in table SYS.RULE$ are converted
5 rows in table SYS.SCHEDULER$_EVENT_LOG are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
PL/SQL procedure successfully completed.
Alter the database character set...
CSALTER operation completed, please restart database
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1289484 bytes
Variable Size 75498228 bytes
Database Buffers 83886080 bytes
Redo Buffers 7098368 bytes
Database mounted.
Database opened.
SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
UTF8
SQL>
Note : please refer 2010 december post for Schema LEVEL characterset migration to another database using CSSCAN and EXPORT/IMPORT utility.
Step 1) Check the current character set of the target database.
SQL> Select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------
WE8MSWIN1252
Step 2) Configure CSSCAN
Csscan is a tool that allows you to see the impact of a character set change. This is tool is available from 8i and above. To install the tool follow the steps below
cd $ORACLE_HOME/rdbms/admin
set oracle_sid=
sqlplus
SQL>conn / as sysdba
SQL> spool csminst.log
SQL> START csminst.sql
Character set scanner creates a schema called CSMIG for maintaing its data in the database, and is created using the csminst.sql script.
The default tablespace for this user should be system you can change it.
alter user csmig default tablespace SYSTEM
Check file csminst.log for any errors after installing csscan.
Step 3) To check if csscan is working fine, run the command and check for the output as shown.
D:\Oracle10g\product\10.2.0\db_1\BIN>csscan TABLE=SYS.SQL_VERSION$ FROMCHAR=US7ASCII TOCHAR=US7ASCII LOG=d:\instchk CAPTURE=N P
ROCESS=1 ARRAY=1024000
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Tue Feb 8 14:47:15 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: sys as sysdba
Password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Enumerating table to scan...
. process 1 scanning SYS.SQL_VERSION$[AAAAIDAABAAAA/xAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
D:\Oracle10g\product\10.2.0\db_1\BIN>
Once the csscan is completed, then csalter script has to be run, It will change the character set to the desired one used in csscan TOCHAR variable.
Some conditions has to be met before running the script csalter.
Csalter the Csscan output needs to be
* changeless for all CHAR VARCHAR2, and LONG data (Data Dictionary and user)
* changeless for all USER CLOB
* convertible and changeless for all Data Dictionary CLOB
in order to run Csalter you need to see in the instchk.txt (logfile which is created in the previous run) file under [Scan Summary] .All character type application data remain the same in the new character set
and under [Data Dictionary Conversion Summary]
The data dictionary can be safely migrated using the CSALTER script
Step 4) Run the csscan command as provided below.
D:\Oracle10g\product\10.2.0\db_1\BIN>csscan FULL=Y FROMCHAR=WE8MSWIN1252 TOCHAR=utf8 ARRAY=1024000 LOG=d:\dbcharchng CAPTURE=Y
SUPPRESS=1000 PROCESS=4
. process 1 scanning SYSMAN.MGMT_DELTA_COMP_KEY_COLS[AAAMQiAADAAAGORAAA]
. process 2 scanning SYSMAN.MGMT_DELTA_IDS[AAAMP+AADAAAGJxAAA]
. process 3 scanning SYSMAN.MGMT_JOB_EXEC_LOCKS[AAAMS4AADAAAGgxAAA]
. process 1 scanning SYS.WRH$_SERVICE_WAIT_CLASS[AAAMjkAADAAABAhAAA]
. process 4 scanning SYSMAN.MGMT_HA_RAC_INTR_CONN[AAAMeDAADAAAHMRAAA]
. process 2 scanning SYS.WRH$_ROWCACHE_SUMMARY[AAAMhfAADAAABOpAAA]
. process 3 scanning SYS.WRH$_DLM_MISC[AAAMjMAADAAAA5RAAA]
. process 1 scanning SYS.WRH$_SQLSTAT[AAAMiWAADAAAAsJAAA]
. process 4 scanning SYS.WRH$_ACTIVE_SESSION_HISTORY[AAAMjUAADAAAA8RAAA]
. process 2 scanning SYS.WRH$_PARAMETER[AAAMjEAADAAAA1RAAA]
. process 3 scanning SYSTEM.LOGMNR_ATTRIBUTE$[AAABbdAADAAAAd5AAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
Step 5). Once the CSSCAN has been run then you can check the logfile , text file and the error file which is generated while running the CSSCAN in the previous step. there has to be no errors in the files, if yes then you can proceed with the next step by running CSALTER script as below.
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Feb 8 15:31:44 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: sys/sys as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @D:\Oracle10g\product\10.2.0\db_1\RDBMS\ADMIN\csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validility...
begin converting system objects
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
4 rows in table MDSYS.SDO_XML_SCHEMAS are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
80 rows in table SYS.METASTYLESHEET are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
2308 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
4 rows in table SYS.RULE$ are converted
5 rows in table SYS.SCHEDULER$_EVENT_LOG are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
PL/SQL procedure successfully completed.
Alter the database character set...
CSALTER operation completed, please restart database
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1289484 bytes
Variable Size 75498228 bytes
Database Buffers 83886080 bytes
Redo Buffers 7098368 bytes
Database mounted.
Database opened.
SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
UTF8
SQL>
Subscribe to:
Posts (Atom)