Oracle

Oracle DBA table needs to know and maintenance job-29

Advertisements

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 15th @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

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: