Posts

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

Database Experts:Last backup and Last Backup restore Information

For Finding latest backup details of all databases. SELECT      CONVERT ( CHAR ( 100 ),  SERVERPROPERTY ( 'Servername' ))  AS  Server ,     msdb.dbo.backupset.database_name ,      MAX ( msdb.dbo.backupset.backup_finish_date )  AS  last_db_backup_date FROM    msdb.dbo.backupmediafamily      INNER JOIN  msdb.dbo.backupset  ON  msdb.dbo.backupmediafamily.media_set_id  =  msdb.dbo.backupset.media_set_id  WHERE   msdb..backupset.type  =  'D' --'L' for transaction logs GROUP BY     msdb.dbo.backupset.database_name  ORDER BY      msdb.dbo.backupset.database_name For Finding latest restore backup for all databases. SELECT MAX(restore_date) AS LastRestore,COUNT(*) AS CountRestores ,destination_database_name FROM msdb.dbo.restorehistory GROUP BY destination_database_name ord...
Post a Comment
Read more

Database Experts:Copy Database on SQL Server 2008 R2

Image
Copy Database from One SQL Server to Another SQL Server using Copy Database Wizard Use this link to know more about Copy Database, Copy Database Wizard Source:SQL Server 2005 Destination:SQL Server 2008R2 1.Connect to Source database. 2.Right click on Databases-->DatabaseName-->Task-->Copy Database. Choose best method to suits you. Map Database to move or copy. Wait while for completion of Copy of Database.
Post a Comment
Read more