DBA

A daily refresh copy of the production database will be created on the mirror database server, The purpose is to offload the reporting activities from the production database.  This will also intended to alleviate the database locks for users that are performing non-reporting activities and to gain improved performance when generating the reports.  The following steps will outline how to create the automated database refresh process. Create the daily refresh database. 1.        Create the daily refresh database by restoring a backup of the Production database from \\s hared. 2.        Name the database, PROD_Refresh_Reports. Create a Linked Server 1.        Create a Linked Server to the primary database server .  This will be used to retrieve the backup information of the Prod database to verify if a backup file was created. Create daily refresh SQL Agent job. The job is scheduled to run Monday – Friday at 3:00am.  The job will check if the database bac

For historical reason, when one of the DSS system was upgrade from oracle 9i to 11g, the charactersets are kept as US7ASCII, which does not support french accent properly, this need to be corrected by converting the charactersets from US7ASCII to AL32UTF8. I use metalink document  Doc ID 260192.1  as a guide. Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (Doc ID 260192.1) The document has a lot of details, I only document the one that related to my environment. Note: I use csscan to find the lossy data, use DMU to convert the charactersets. step 1：run csscan to generate the report. csscan \"sys/syspassword as sysdba\" full=yes fromchar=us7ascii tochar=us7ascii log=dbcheck capture=y array=1000000 process=2 it took about 2 hours to run against 500GB database. This step generate the dbcheck.txt, dbcheck.out, and dbcheck.err report. step 2:  generate the lossy data query select 'select '||column_name||' from 

Oracle OEM show a chart of disk performance, as shown in the chart below. when the OEM target database is 10gr2, the number of average disk response time (ms) is 0.01 ms, which unreasonable low. when the target database is 11gr2, the number is about 10ms, which I think is reasonable. it's time to find out where the discrepency comes from. The backend query is this one: set linesize 200 col name format a9 col path format a19 --col MB_per_sec format select t3.name,t2.name,t2.path,t2.reads,t2.read_time,round(t2.read_time/t2.reads*1000,3) rd_rspd_ms,t2.writes,round(t2.write_time/t2.writes*1000,3) wr_rspd_ms, round((t2.read_time+t2.write_time)/(t2.reads+t2.writes)*1000,3) dsk_rspd_ms,round((t2.bytes_read+t2.bytes_written)/1024/1024/(t2.read_time+t2.write_time))  MB_per_sec from V$asm_disk t2, v$asm_diskgroup t3 where t3.group_number=t2.group_number order by 1,2 / NAME      NAME      PATH                     READS  READ_TIME RD_RSPD_MS     WRITES WR_RSPD_MS DSK_RSPD_M