Home » RDBMS Server » Backup & Recovery » Regarding Archive log files ......
( ) 1 Vote
| Regarding Archive log files ...... [message #174160] |
Fri, 26 May 2006 05:14  |
frank.svs
Messages: 162
Registered: February 2006
|
Senior Member |
|
|
Hi Team,
I need some real time stuff how does at Enterprise level one backup and restore archived log files?
What kind strategies do they follow.
I want know more on this topic. Can anyone please help me out.
Please dont say that we take the backup's of archives on tapes and CD's. What i want to know is that how exactly the Database Adminstrator's
restore the backup of the archives generated and how many sets of Archived log file Backups are generally maintained in the Production Environment.
I just want to know various strategies that would be implemented especially on Archive log files are concerned.
Best Regards
frank
|
|
|
|
| Re: Regarding Archive log files ...... [message #174356 is a reply to message #174160] |
Sun, 28 May 2006 07:56  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Here is what I do.
Each night the operators run a job. We have 5 versions, that is 5 nights of backups at any time. By changing the directory name we move directory 4 to directory 5, directory 3 to directory 4 and so on. Then we build a list of all table spaces in plsql, excluding temporary tablespaces. The job puts the tablespaces, one by one, into and out of backup mode and copies the tablespace to directory 1. Then we switch logfiles and include it in the directory 1, which is our 'destination directory' and backup the controlfile and logfiles as well. Then we compress all the files.
I have all the code for you if you wish. This code is for any unix running any version of Oracle. Let me know if you need the code.
The operator needs to be sure the backup completes because you do not want to leave a tablespace in backup mode any longer that necessary.
Restore is a little tricker. It requires Knowlege of tablespace and archivelog locations. You NEVER want to restore a controlfile unless it is your only option. The controlfile does not know anything that happened after the backup so you lose data.
We run live backups usually after the batch jobs run.
Another option is the export/import mode. I do not use it because of the limitations.
I am glad I have only had to restore 2 times in the last 10 years. Both times we because of a 'user' error. You have to do a 'point in time' recovery.
Good luck and let me know if you need the shell scripts for the backup.
Neil.
|
|
|
|
|
|
|
|
|
|
| Re: Regarding Archive log files ...... [message #174875 is a reply to message #174160] |
Tue, 30 May 2006 21:15 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Here is a start.
We use hotbackups via scripts, and version them to have 5 sets.
Don't copy the control file or you will lose everything done after the control file was taking.
Here is how we do it.
>cat hotbackup_start.ksh
#!/bin/ksh
#@(#)hotbackup_start -- starts S2000 hotbackup
###
# Korn script to start hot backup of the S2000 database
#
# Autor: Neil MacDannald
# Date: 8/10/2005
###
#
# Inform operator what job they have requested
# Ask if they wish to continue
#
echo " "
echo "*****"
echo Starting hot backup of S2000 database
echo "Continue? (Y/N)"
read answer
echo " "
if [[ "$answer" = [Yy]* ]]
then
df -k /u26
echo `date`
thedir=`pwd`
echo " "
echo Enter password for S2000 ORADBA
sttyops=`stty -g`
stty -echo
echo "Password: "
read password
echo " "
stty $sttyops
#
# Start the hot backup job
#
sqlplus ORADBA/$password << EOF
@$thedir/hotbackup_start
exit
EOF
sleep 20
`cp /u01/oradata/os2p/ctl/*.ctl /u05/oradata/os2p/budir`
`cp /u01/oradata/os2p/ts/*.log /u05/oradata/os2p/budir/`
df -k
echo `date`
fi
which calls:
>cat hotbackup_start.sql
rem========================================================================
rem
rem Filename: hotbackup.sql
rem Purpose: Hot backup of Oracle database
rem Created: 8/10/2005
rem
rem It assumes :
rem - An underlying UNIX system
rem - The use of the 'cp' command to backup the files to a special
rem backup directory the name of which is prompted for in the
rem script
rem
rem Because of the high importance of backup procedures, due care is
rem given to the handling of errors. Any SQL or operating system error
rem will cause the script to exit and return a non-0 value to the
rem operating system.
rem
rem IT IS EXTREMELY IMPORTANT IN SUCH A CASE TO RUN THE ASSOCIATED
rem hotbackup_cancel.sql AS SOON AS POSSIBLE TO CANCEL THE
rem POSSIBLE 'BACKUP' STATE OF A TABLESPACE.
rem
rem========================================================================
set linesize 600
set pause off
set pagesize 0
set echo off
set feedback off
--
-- Hot-backup requires our running in ARCHIVELOG mode.
-- This is tested from the outset.
--
whenever sqlerror exit sql.sqlcode
declare
dummy char(1);
begin
select 'x'
into dummy
from v$database
where log_mode = 'ARCHIVELOG';
exception
when no_data_found then
raise_application_error(-20000, 'Database not in ARCHIVELOG mode !');
end;
/
set scan off
--
-- Interactively input the name of the directory (or device)
-- where to backup. This kind of dialogue is likely to prove
-- a hindrance in automated, daily operations.
-- You can either hard-code the directory name by using
-- define backup_dir = ...
-- or suppose it is passed as an argument to the current
-- script, in which case you should replace &&backup_dir by
-- &1 everywhere in this script and comment the following
-- 'accept' command
--
-- accept backup_dir prompt 'Backup directory : '
define backup_dir = /u05/oradata/os2p/budir/
--
set recsep off
set scan on
set verify off
--
-- You must absolutely check that everything goes according
-- to plans! If the backup fails because of, say, a
-- 'file system full' error, the script must exit with a
-- non-0 return code.
--
whenever sqlerror exit sql.sqlcode
whenever oserror exit 99
--
-- To be certain we have a consistent set of files which
-- we shall be able to recover, we also want to backup
-- all the redo log files generated during the backup,
-- and the final state of the control file.
-- Note that when recovering you normally must use the
-- current (i.e. at crash time) control file. However,
-- we want to be ready for the worst case scenario and
-- be able to use the current backup as if it were a
-- cold backup.
-- In order to know which redo log files have been generated
-- during the backup, we first identify which is, before
-- backup, the (current - 1) redo log file.
--
variable last_log varchar2(255)
begin
--
-- Format %S and %T (left-padded with zeros)
-- are not supported because length is OS-dependent.
-- Easy to support for a specific machine
--
select replace(pd.value, '?', '$ORACLE_HOME') ||
replace(replace(pf.value, '%s', rtrim(to_char(l.sequence#))),
'%t', rtrim(to_char(l.thread#)))
into :last_log
from v$parameter pd,
v$parameter pf,
v$log l
where pd.name = 'log_archive_dest'
and pf.name = 'log_archive_format'
and l.sequence# = (select max(sequence#)
from v$log
where status != 'CURRENT');
end;
/
--
-- Generate the backup script
--
-- online$ = 3 means a tablespace which once
-- existed but was dropped.
--
column dummy noprint
spool hbu_doit.sql
select ts# dummy,
1 dummy,
'alter tablespace ' || name || ' begin backup;'
from sys.ts$
where online$ != 3
and name not like 'TEM%'
union
select f.ts#,
2,
'host cp ' || d.name || ' &&backup_dir'
from sys.file$ f,
v$datafile d
where f.file# = d.file#
union
select ts#,
3,
'alter tablespace ' || name || ' end backup;'
from sys.ts$
where online$ != 3
and name not like 'TEM%'
order by 1, 2
/
spool off
--
-- Run the backup script just generated
--
set echo on
set feedback on
@hbu_doit
--
-- Take a backup of the control file for the worst-case scenario
-- Do not worry, parameter replacement is not echoed but done.
--
alter database backup controlfile to '&&backup_dir./control.ctl' reuse;
--
-- Trigger a redo log switch to close the current redo log file
--
alter system switch logfile;
set echo off
--
-- Force the archival of all the redo log files
--
declare
Stmt varchar2(100);
cid number;
dummy number;
nothing_to_archive exception;
PRAGMA EXCEPTION_INIT(nothing_to_archive, -271);
begin
Stmt := 'alter system archive log all';
cid := dbms_sql.open_cursor;
begin
dbms_sql.parse(cid, Stmt, dbms_sql.native);
dummy := dbms_sql.execute(cid);
exception
when nothing_to_archive then
null;
--
-- Unexpected errors
--
when others then
if (dbms_sql.is_open(cid))
then
dbms_sql.close_cursor(cid);
end if;
raise_application_error(-20000, Stmt || chr(10) || SQLERRM);
end;
dbms_sql.close_cursor(cid);
end;
/
set feedback off
--
-- Backup all the redo log files generated during hot backup
-- in ksh script when this completes until later
set echo on
--
-- All done!
--
exit 0
|
|
|
|
| Re: Regarding Archive log files ...... [message #174882 is a reply to message #174160] |
Tue, 30 May 2006 22:50 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Here is the Oprmenu file:
(To start it do . ./oprmenu)
#!/bin/ksh
#@(#)oprmenu.ksh - S2000 database Operations menu
###
# Korn script to display a menu for our operators
# Ask operator which database maintenance job
# they would like to run.
#
# Autor: Neil MacDannald
# Date: 12/02/1999
# Modified: 01/08/2003 - add export and xfer jobs
###
#
# Ask operator what database job they would like to run
#
quit=n
clear
while test "$quit" = "n"
do
echo " "
echo " Menu"
echo "-----------------------------------------------"
echo " "
echo "1. Run S2000 hot backup "
echo "2. Run S2000 file compression "
echo "3. Run S2000 archive maintenance "
echo "4. Run S2000 export for Tracy "
echo "5. Run S2000 xfer export file to Tracy "
echo "6. Run S2000 hot backup cancel "
echo " "
echo "8. Run s2000 background process stop "
echo "9. Quit "
echo " "
echo "Enter choice: "
read choice
case $choice in
1) . ./hotbackup_start.ksh;;
2) . ./hotbackup_compress.ksh;;
3) . ./archivemaint.ksh;;
4) . ./export-s2000-database.ksh;;
5) . ./export-xfer-to-solarflare.ksh;;
6) . ./hotbackup_cancel.ksh;;
 . ./bgprockill.ksh;;
9) quit=y;;
*) echo "Invalid selection, enter 1, 2, 3, 4, 5, 6, 8, or 9 only \07\07"
sleep 5;;
esac
done
#!/bin/ksh
#@(#)hotbackup_start -- starts S2000 hotbackup
###
# Korn script to start hot backup of the S2000 database
#
# Autor: Neil MacDannald
# Date: 9/30/1999
# Modification: 04/26/2002 - multiplex archive files, copy from both locations
###
#
# Inform operator what job they have requested
# Ask if they wish to continue
#
echo " "
echo "*****"
echo Starting hot backup of S2000 database
echo "Continue? (Y/N)"
read answer
echo " "
if [[ "$answer" = [Yy]* ]]
then
df -k /u26
echo `date`
thedir=`pwd`
echo " "
echo Enter password for S2000 ORADBA
sttyops=`stty -g`
stty -echo
echo "Password: "\\c
read password
echo " "
stty $sttyops
#
# Start the hot backup job
#
sqlplus ORADBA/$password << EOF
@$thedir/hotbackup_start
exit
EOF
sleep 20
cp /u02/oradata/os2p/arch/*.ARC* /u26/oradata/os2p/budir/
cp /u24/oradata/os2p/arch/*.ARC* /u26/oradata/os2p/budir/u24arch/
df -k
echo `date`
fi
Here is the hotbackup.sql script:
rem========================================================================
rem
rem Filename: hotbackup.sql
rem Purpose: Hot backup of Oracle database
rem Created: 9/14/1999
rem Modified: 04/02/2002 move backup directory
rem
rem It assumes :
rem - An underlying UNIX system
rem - The use of the 'cp' command to backup the files to a special
rem backup directory the name of which is prompted for in the
rem script
rem
rem Because of the high importance of backup procedures, due care is
rem given to the handling of errors. Any SQL or operating system error
rem will cause the script to exit and return a non-0 value to the
rem operating system.
rem
rem IT IS EXTREMELY IMPORTANT IN SUCH A CASE TO RUN THE ASSOCIATED
rem hotbackup_cancel.sql AS SOON AS POSSIBLE TO CANCEL THE
rem POSSIBLE 'BACKUP' STATE OF A TABLESPACE.
rem
rem========================================================================
set linesize 600
set pause off
set pagesize 0
set echo off
set feedback off
--
-- Hot-backup requires our running in ARCHIVELOG mode.
-- This is tested from the outset.
--
whenever sqlerror exit sql.sqlcode
declare
dummy char(1);
begin
select 'x'
into dummy
from v$database
where log_mode = 'ARCHIVELOG';
exception
when no_data_found then
raise_application_error(-20000, 'Database not in ARCHIVELOG mode !');
end;
/
set scan off
--
-- Interactively input the name of the directory (or device)
-- where to backup. This kind of dialogue is likely to prove
-- a hindrance in automated, daily operations.
-- You can either hard-code the directory name by using
-- define backup_dir = ...
-- or suppose it is passed as an argument to the current
-- script, in which case you should replace &&backup_dir by
-- &1 everywhere in this script and comment the following
-- 'accept' command
--
-- accept backup_dir prompt 'Backup directory : '
define backup_dir = /u26/oradata/os2p/budir/
--
set recsep off
set scan on
set verify off
--
-- You must absolutely check that everything goes according
-- to plans! If the backup fails because of, say, a
-- 'file system full' error, the script must exit with a
-- non-0 return code.
--
whenever sqlerror exit sql.sqlcode
whenever oserror exit 99
--
-- To be certain we have a consistent set of files which
-- we shall be able to recover, we also want to backup
-- all the redo log files generated during the backup,
-- and the final state of the control file.
-- Note that when recovering you normally must use the
-- current (i.e. at crash time) control file. However,
-- we want to be ready for the worst case scenario and
-- be able to use the current backup as if it were a
-- cold backup.
-- In order to know which redo log files have been generated
-- during the backup, we first identify which is, before
-- backup, the (current - 1) redo log file.
--
variable last_log varchar2(255)
begin
--
-- Format %S and %T (left-padded with zeros)
-- are not supported because length is OS-dependent.
-- Easy to support for a specific machine
--
select replace(pd.value, '?', '$ORACLE_HOME') ||
replace(replace(pf.value, '%s', rtrim(to_char(l.sequence#))),
'%t', rtrim(to_char(l.thread#)))
into :last_log
from v$parameter pd,
v$parameter pf,
v$log l
where pd.name = 'log_archive_dest'
and pf.name = 'log_archive_format'
and l.sequence# = (select max(sequence#)
from v$log
where status != 'CURRENT');
end;
/
--
-- Generate the backup script
--
-- online$ = 3 means a tablespace which once
-- existed but was dropped.
--
column dummy noprint
spool hbu_doit.sql
select ts# dummy,
1 dummy,
'alter tablespace ' || name || ' begin backup;'
from sys.ts$
where online$ != 3
and name <> 'TEMP_01'
union
select f.ts#,
2,
'host cp ' || d.name || ' &&backup_dir'
from sys.file$ f,
v$datafile d
where f.file# = d.file#
union
select ts#,
3,
'alter tablespace ' || name || ' end backup;'
from sys.ts$
where online$ != 3
and name <> 'TEMP_01'
order by 1, 2
/
spool off
--
-- Run the backup script just generated
--
set echo on
set feedback on
@hbu_doit
--
-- Take a backup of the control file for the worst-case scenario
-- Do not worry, parameter replacement is not echoed but done.
--
alter database backup controlfile to '&&backup_dir./control.ctl' reuse;
--
-- Trigger a redo log switch to close the current redo log file
--
alter system switch logfile;
set echo off
--
-- Force the archival of all the redo log files
--
declare
Stmt varchar2(100);
cid number;
dummy number;
nothing_to_archive exception;
PRAGMA EXCEPTION_INIT(nothing_to_archive, -271);
begin
Stmt := 'alter system archive log all';
cid := dbms_sql.open_cursor;
begin
dbms_sql.parse(cid, Stmt, dbms_sql.native);
dummy := dbms_sql.execute(cid);
exception
when nothing_to_archive then
null;
--
-- Unexpected errors
--
when others then
if (dbms_sql.is_open(cid))
then
dbms_sql.close_cursor(cid);
end if;
raise_application_error(-20000, Stmt || chr(10) || SQLERRM);
end;
dbms_sql.close_cursor(cid);
end;
/
set feedback off
--
-- Backup all the redo log files generated during hot backup
-- in ksh script when this completes until later
set echo on
--
-- All done!
--
exit 0
******************
More to follow
[Updated on: Wed, 31 May 2006 18:32] Report message to a moderator |
|
|
|
| Re: Regarding Archive log files ...... [message #174883 is a reply to message #174160] |
Tue, 30 May 2006 22:51 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Hotbackup cancel:
set linesize 100
set pause off
set pagesize 0
set echo off
set feedback off
set recsep off
set scan off
spool hbu_cancel.sql
select 'alter tablespace ' || ts.name || ' end backup;'
from sys.ts$ ts
where ts.ts# in (select f.ts#
from sys.file$ f,
v$backup b
where f.file# = b.file#
and b.status = 'ACTIVE');
spool off
set echo on
set feedback on
@hbu_cancel
exit
|
|
|
|
| Re: Regarding Archive log files ...... [message #174884 is a reply to message #174160] |
Tue, 30 May 2006 22:52 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
hotbackp compress:
#!/bin/ksh
#@(#)hotbackup_compress -- compresses S2000 backup files before copy to tape
###
# Korn script to start compression of the 2000 database
# hot backup files. This should be run after running
# hotbackup_start.ksh
#
# Autor: Neil MacDannald
# Date: 10/01/1999
# Revision: 10/16/1999 Add remove of old Z files
# Modified: 04/26/2002 - support multiplex of archive files
###
#
# Inform operator what job they have requested
# Ask if they wish to continue
#
echo " "
echo "*****"
echo Starting S2000 hotbackup file compression
echo " "
echo This job should be run after the hotbackup completes and
echo before the hotbackup files are backed up to tape.
echo Please verify that the hotbackup completed properly.
echo " "
echo "Continue? (Y/N)"
read answer
echo " "
if [[ "$answer" = [Yy]* ]]
then
echo " "
echo `date`
echo " "
echo "Number of old files: " `ls /u26/oradata/os2p/budir/*.Z |wc -w`
echo "Last time this job was run: " `cat <compress_date`
echo " "
echo "Remove old compressed files?(Y/N)"
read answer
if [[ "$answer" = [Yy]* ]]
then
rm /u26/oradata/os2p/budir/*.Z
rm /u26/oradata/os2p/budir/u24arch/*.Z
`date >compress_date`
echo " "
echo "Old files removed, starting compression of new backup files"
echo " "
echo `date`
echo " "
compress -v /u26/oradata/os2p/budir/*.*[!Zh]
compress -v /u26/oradata/os2p/budir/u24arch/*.*[!Z]
echo " "
echo `date`
echo " "
fi
fi
|
|
|
|
| Re: Regarding Archive log files ...... [message #174885 is a reply to message #174160] |
Tue, 30 May 2006 22:54 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
start the database;
#!/bin/ksh
#@(#)dbasu -- start os2p
###
# Korn script to start os2p
#
# Autor: Neil MacDannald
# Date: 04/05/2002
###
#
# Inform operator what job they have requested
# Ask if they wish to continue
#
echo " "
echo "*****"
echo Starting os2p database
echo "Continue? (Y/N)"
read answer
echo " "
if [[ "$answer" = [Yy]* ]]
then
echo `date`
thedir=`pwd`
echo " "
echo Enter password for os2p ORADBA
sttyops=`stty -g`
stty -echo
echo "Password: "\\c
read password
echo " "
stty $sttyops
#
# Start os2p database
#
sqlplus /nolog << EOF
ORADBA/$password as sysdba
startup
exit
EOF
echo `date`
fi
|
|
|
|
| Re: Regarding Archive log files ...... [message #174886 is a reply to message #174160] |
Tue, 30 May 2006 22:55 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
some functions:
alias donhelp="fgrep -e '##' /usr/orap816/scripts/don-funcds |more"
#
# Helpful tips
## Show history of commands
## ESC K
## Show all files under currend directory(and sub) that contain a string
## find ./ -print |xargs grep -i <string>
##
## Show total disk usage for a directory
## du -s <directory>
##
# Move among directories
#
## cd to arch
## cdarch
alias cdarch='cd $DBA/$ORACLE_SID/arch'
## cd to bdump
## cdbdump
alias cdbdump='cd $ORACLE_HOME/trace/bdump'
## cd to udump
## cdudump
alias cdbdump='cd $ORACLE_HOME/trace/udump'
## cd to dbs
## cddbs
alias cddbs='cd $ORACLE_HOME/dbs'
##
##########################
## Oracle monitoring commands
#
## Start os2p database
## dbastartos2p
alias dbastartos2p='/usr/os2p816/scripts/dbasu.ksh'
#
## Show last 200 lines of database alert log
## showalert
alias showalert='tail -200 $ORACLE_HOME/trace/bdump/alert_$ORACLE_SID.log |more'
#
## Display current number of connected users
## showusrcnt
alias showusrcnt='ps -ef |grep $ORACLE_SID |grep -v grep |grep -v ora_ |wc -l'
##
##
##########################
## System monitoring commands
#
## Display current top CPU consumers
## showtopcpu
alias showtopcpu='ps auxgw |sort +2 |tail'
## Show active dedicated Oracle user
## showoraconnections
##
## Display vmstat 8 times
alias showvmstat1='vmstat -S 5 5'
##
## SAR area
## -b = Solaris buffer cache activity
## -w = Solaris swapping acitivity
## -u = CPU utilization
## -r = memory utilization
## -p = Solaris paging activity
##
## Display sar -p (paging)
## showsarp
alias showsarp='sar -p 5 5'
## Display sar -w (swapping activity)
## showsarw
alias showsarw='sar -w 5 5'
## Display sar -b (disk performance)
## showsarb
alias showsarb='sar -b 5 5'
## Display sar -u (look for %wio - % of cpu time spent waiting on io)
## showsaru
alias showsaru='sar -u 5 5'
##
##
# Display iostat area
##
alias showiostat='iostat 5 5'
##
## Display Swapfile data
## showswap
alias showswap='swap -l'
##
## Display mpstat data
## showmpstat
alias showmpstat='mpstat 5 5'
##
alias showconnections='ps -ef |grep $ORACLE_SID |grep -v |grep -v ora_ |wc -l'
## Show RAM size
## showram
alias showram='prtconf |grep -i mem'
## Show allocated memory segments
## showallocatedmemory
alias showallocatedmemory='ipcs -pmb'
## Show number of CPU's
## showcpucount
alias showcpucount='psrinfo -v |grep "Status of Processor" |wc -l'
## Show used Semaphores
## showsemaphores
alias showsemaphores='ipcs -sa'
## show users and privs
alias showusers.ksh='. ./showusers.ksh'
##
#
## - end
# Dangerous commands follow!!
|
|
|
|
|
|
| Re: Regarding Archive log files ...... [message #174888 is a reply to message #174160] |
Tue, 30 May 2006 22:58 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
more scripts:
### name: orafuncds
### type: UNIX function script (korn shell)
### func: define Oracle Server shortcut functions (database server)
### usge: $(anyuser) . [/opt/bin/]orafuncds
### parm: none
### call: none
### rtrn: standard
### outp: none
### inst: path:/opt/bin owner:orat mode:755
### note: assumes OFA-compliant product installations
### Copyright 1998 (c) by Oracle Corporation Redwood Shores California USA
### All rights reserved. Provided without warranty, liability or support.
### hist: 1.0.0 19970601 jsisodiya/sbays(ocs) - original script
### 1.1.0 19980601 sbays(ocs) - R11 baseline [OATSV1.0:GENUTILS]
echo "cdorabase : To change to Oracle Server ORACLE_BASE"
echo "cdoralocsql : To change to Oracle Server local/sql directory"
echo "cdorahome : To change to current Oracle Server version ORACLE_HOME"
echo "cdorapatch : To change to current Oracle Server version patch directory"
echo "cdoraadm : To change to current Oracle Server instance admin directory"
echo "showdb : To show active database instances"
echo "showlsnr : To show active listener UNIX processes"
echo "showprocdb : To show current instance database processes"
echo "showprocall : To show all current instance active UNIX processes"
echo "oprmenu : To run operator menu for S2000 database"
echo ""
cdorabase ()
{
cd $ORACLE_BASE
}
cdoralocsql ()
{
cd $ORACLE_BASE/local/sql
}
cdorahome ()
{
cd $ORACLE_HOME
}
cdorapatch ()
{
cd /u70/app/oracle/patchdir
}
cdoraadm ()
{
cd $ORACLE_BASE/admin/$ORACLE_SID
}
showdb ()
{
ps -ef|grep "pmon_"|grep -v "grep"|more
}
showlsnr ()
{
ps -ef|grep "LIST"|grep -v "grep"|more
}
showprocdb ()
{
ps -ef|grep $ORACLE_SID|grep "ora_"|grep -v "grep"|more
}
showprocall ()
{
ps -ef|grep $ORACLE_SID|grep -v "grep"|more
}
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Regarding Archive log files ...... [message #174896 is a reply to message #174160] |
Tue, 30 May 2006 23:36 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
#!/bin/ksh
#@(#)archivemaint -- removes old S2000 archive files & compresses others
###
# Korn script to do Database Archive file maintenance
# for the S2000 database. This should be run about once
# each week or so.
#
# Autor: Neil MacDannald
# Date: 10/25/1999
# Modified: 04/26/2002 to support multiplexing archive logfiles
###
#
# Inform operator what job they have requested
# Ask if they wish to continue
#
echo " "
echo "*****"
echo Starting S2000 Archive file maintenance
echo " "
echo This job should be run about once per week. It removes
echo S2000 database Archive files that are more than 2 weeks old.
echo Archive logs are located in both of the following directories:
echo /u02/oradata/os2p/arch
echo /u24/oradata/os2p/arch
echo " "
echo "Continue? (Y/N)"
read answer
echo " "
if [[ "$answer" = [Yy]* ]]
then
echo " "
echo `date`
echo " "
echo "Count Archive files /u02: " `ls /u02/oradata/os2p/arch/*.ARC* |wc -w`
echo "Count Archive files /u24: " `ls /u24/oradata/os2p/arch/*.ARC* |wc -w`
echo "Last time this job was run: " `cat <archmaint_date`
echo " "
echo "Remove Archive files more than 2 weeks old and compress "
echo " the newer ones? (Y/N)"
echo " "
read answer
if [[ "$answer" = [Yy]* ]]
then
`date >archmaint_date`
startdir=`pwd`
echo "starting dir= " $startdir
cd /u02/oradata/os2p/arch/
echo " temporarily changing to directory " `pwd`
`find ./ -name "*.ARC*" -mtime +14 -exec rm {} \;`
cd /u24/oradata/os2p/arch/
echo " temporarily changing to directory " `pwd`
`find ./ -name "*.ARC*" -mtime +14 -exec rm {} \;`
echo " "
echo "Old files removed, starting compression of new Archive files"
echo " "
echo `date`
echo " "
`compress -v /u02/oradata/os2p/arch/*.ARC`
`compress -v /u24/oradata/os2p/arch/*.ARC`
cd $startdir
echo " changed directory back to " `pwd`
echo " "
echo `date`
echo " "
fi
fi
|
|
|
|
| Re: Regarding Archive log files ...... [message #174907 is a reply to message #174160] |
Wed, 31 May 2006 00:21 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
spool ${DIR}/ocr_${ORACLE_SID}.userprivs.txt
set pagesize 2000
set echo off
set feed off
set verify off
-- Users list
ttitle 'All users'
select username "Users"
from dba_users
where username not in ('SYS','SYSTEM','OUTLN',
'DBSNMP','SCOTT','DB_CONTROL',
'OPS$ORACLE','ORADBA')
/
-- All user's granted
break on user skip 1 on user
col user format a15
col grant format a30
ttitle 'All users granted'
select grantee "User" ,granted_role "Grant", 'role' "Type"
from dba_role_privs
where grantee in (select username from dba_users
where username not in ('SYS','SYSTEM',
'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX'))
and granted_role not in ('CONNECT')
union all
select grantee "User", privilege "Grant", 'priv' "Type"
from dba_sys_privs
where grantee in (select username from dba_users
where username not in ('SYS','SYSTEM',
'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX'))
and privilege not in ('CONNECT')
union all
select grantee "User", owner||'.'||table_name "Grant", lower(privilege)
"Type"
from dba_tab_privs
where grantee in (select username from dba_users
where username not in ('SYS','SYSTEM',
'OUTLN','DBSNMP','SCOTT','DB_CONTROL','OPS$ORACLE','XXX'))
order by 1
/
-- All role's granted
|
|
|
|
|
|
| Re: Regarding Archive log files ...... [message #174909 is a reply to message #174160] |
Wed, 31 May 2006 00:25 |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
Export done in US7ASCII character set and WE8ISO8859P1 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
[Updated on: Wed, 31 May 2006 16:52] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Regarding Archive log files ...... [message #181544 is a reply to message #174160] |
Mon, 10 July 2006 08:20 |
mahi02
Messages: 11
Registered: July 2006
|
Junior Member |
|
|
Hi,
In the place iam working they want weekly,monthly and yearly backups of the whole data in such a way that after taking the backup of the whole year we want the whole old data to be removed from that system and be backedup to elsewhere but should be able to retrieve the data whenever required.
Thanks.
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 11:44:36 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
