# we have a script to re-create controlfile when the mount point of datafiles changed, however, the number of datafiles are increasing but the script is not catching up, so when the re-create controlfile is executed, it sometime missing the new added datafiles and leaving some MISSING000XXX files in dba_data_files dictionary.
#this script check if re-create controlfile script is missing any datafiles in it, if it does, the filenames will be displayed to the standard output.
1. unix shell script
for myfile in /redo_prod/PROD/* /orasys_prod/PROD/* /data_prod/PROD/* /index_prod/PROD/*
do
grep -q $myfile $HOME/dba/control_prod.sql
status=$?
if test $status -eq 1
then
echo $myfile "is missing in the re-create controlfile script"
fi
done
2. re-create controlfile script control_prod.sql
CREATE CONTROLFILE REUSE DATABASE "PRODUCTION" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 2949
LOGFILE
GROUP 1 (
'/redo_prod/PROD/redo01a.log',
'/orasys_prod/PROD/redo01b.log'
) SIZE 100M,
GROUP 2 (
'/redo_prod/PROD/redo02a.log',
'/orasys_prod/PROD/redo02b.log'
) SIZE 100M,
GROUP 3 (
'/redo_prod/PROD/redo03a.log',
'/orasys_prod/PROD/redo03b.log'
) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/orasys_prod/PROD/system01.dbf',
...
#this script check if re-create controlfile script is missing any datafiles in it, if it does, the filenames will be displayed to the standard output.
1. unix shell script
for myfile in /redo_prod/PROD/* /orasys_prod/PROD/* /data_prod/PROD/* /index_prod/PROD/*
do
grep -q $myfile $HOME/dba/control_prod.sql
status=$?
if test $status -eq 1
then
echo $myfile "is missing in the re-create controlfile script"
fi
done
2. re-create controlfile script control_prod.sql
CREATE CONTROLFILE REUSE DATABASE "PRODUCTION" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 2949
LOGFILE
GROUP 1 (
'/redo_prod/PROD/redo01a.log',
'/orasys_prod/PROD/redo01b.log'
) SIZE 100M,
GROUP 2 (
'/redo_prod/PROD/redo02a.log',
'/orasys_prod/PROD/redo02b.log'
) SIZE 100M,
GROUP 3 (
'/redo_prod/PROD/redo03a.log',
'/orasys_prod/PROD/redo03b.log'
) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/orasys_prod/PROD/system01.dbf',
...
Comments
Post a Comment