Oracle DBA table needs to know and maintenance job-29

DBA Tables needs to know

 

To know the database properties and parameters.

Show parameter <Parameter name>

Select * from v$parameters; – Parameres

Select name from v$controlfile; – Control file

Select * from v$logfile; – Members info

Select * from v$log; – Log size

Archive log list; – Archive details

 

Sql> select * from v$sgastat – SGA size

Sql> select * from v$pgastat;  – PGA size

sql> select * from v$tablespace;  – Tablespace size

sql> select * from dba_tablespaces; – Tablespace size

sql> select * from v$datafile; – Datafile size

sql> select * from dba_data_files; – Datafile size

 

Rename data file	Code
Offline tablespace	alter tablespace <ts name> offline
CP to destination
Rename the tablespace	alter tablespace < ts name > rename datafile ‘old  location’ to  ‘new  location’;

 

Select * from database_Properties where property_name like ‘DEFAULT’; – To get the all default tablespace like, temporary & User.

DBA Jobs Tool:

Crontab –l – List

Crontab –e – edit

 

Field	Minute	Hour	Day of Month	Month		Day of Week		Command
Valid values	0-59	0-23	1-31	1-12	0-7		Command path/command

 

***** – There will be five star for the schedule.

1. Every minute
2. Every hour
3. Month of day (Date)
4. Month of Year (Month)
5. Day of week (0, 1, 2, 3, 4, 5, 6 – 0 Sunday & 1- Monday so on)

Example:

Every year on march 15^th @10:30

30 10 15 mar * – 15-mar-2015 @10:30

Every Month

30 10 12 * * – Every month 12th @10:30

Every day – Monday to Friday @02:00

00 2 * * 1-5

For a new script:

Vi add script and give permission chmod 777

Ex:

 

Crontab – is for scheduling

Nohub – is for direct run

 

Maintenance job:

1. Weekly full backup
2. Daily incremental
3. Hourly archive log
4. Update stats for schema