Clearing Log Files
Clearing the redo log files is usually the first remedy used in
repairing a damaged instance ; it's the equivalent of dropping and
re-adding each file.
SELECT * FROM v$log ;
ALTER DATABASE CLEAR LOGFILE 'filename' ;
/* if archive log mode is true, may need to force the clear */
ALTER DATABASE CLEAR UNARCHIVED LOGFILE 'filename' ;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2 ;
DB Verify
The DB Verify utiltity is the equivalent of Sybase's DBCC utility.
It basically goes through a given data file and checks the consistency,
verifying the pointers are all proper. This should be run on a regular basis,
to find potential disk problems. If it is not, the end users will likely
be the first to notice any problems which may materialize.
# run example
cd /apps/oracle/bin
./dbv /data2/ts23.dbf
Creating users
/* create profile */
CREATE PROFILE pr01
SESSIONS_PER_USER 1
IDLE_TIME 30
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_VERIFY_FUNCTION SYSUTILS.VERIF_PWD ;
PROFILE = name of the profile
SESSIONS_PER_USER = limits a user to integer concurrent sessions
CPU_PER_SESSION = limits the CPU time for a session, in hundredth of seconds
CPU_PER_CALL = limits the CPU time for a call
CONNECT_TIME = limits the total elapsed time of a session, in minutes
IDLE_TIME = limits periods of continuous inactive time during a session, in minutes
LOGICAL_READS_PER_SESSION = number of data blocks read in a session
LOGICAL_READS_PER_CALL = number of data blocks read for a call to process a SQL stmt
FAILED_LOGIN_ATTEMPTS = number of failed attempts to log in, before locking acct
PASSWORD_LIFE_TIME = limits the number of days the same password can be used
PASSWORD_REUSE_TIME = number of days before which a password cannot be reused
PASSWORD_REUSE_MAX = number of password changes required
PASSWORD_LOCK_TIME = number of days an account will be locked
PASSWORD_VERIFY_FUNCTION = a PL/SQL password complexity verification script
DEFAULT = omits a limit for this resource in this profile
COMPOSITE_LIMIT = specifies the total resources cost for a session, in service units
UNLIMITED = a user assigned this profile can use an unlimited amount of this resource
/* create user */
CREATE USER scott
IDENTIFIED BY tiger
DEFAULT TABLESPACE ts01
QUOTA 500M ON ts02
PASSWORD EXPIRE
ACCOUNT UNLOCK
PROFILE pr01 ;
/* user pwd change */
ALTER USER scott
IDENTIFIED BY lion ;
/* add tablespace */
ALTER USER scott
QUOTA 100M ON ts29 ;
/* user drop */
DROP user scott ;
Taking tablespaces offline
To perform a "hot backup", each tablespace needs to put
offline, and backed up using OS commands.
/* prepare for offline tablespace backup */
ALTER SYSTEM ARCHIVE LOG CURRENT;
/* take tablespace offline */
ALTER TABLESPACE users OFFLINE NORMAL;
/* run backup for datafiles */
/* bring tablespace online */
ALTER TABLESPACE users ONLINE;
Backing up data files
A "cold" backup is the most widely used method for backing up
Oracle data.
ALTER TABLESPACE ts1 BEGIN BACKUP;
<<>>
ALTER TABLESPACE ts1 END BACKUP;
/* control file backup */
ALTER DATABASE BACKUP CONTROLFILE TO 'filename' REUSE;
