Oracle - Administration

oracle
Unlock rows

http://www.youtube.com/watch?v=-1jW-IwjmkE - Database Change Tracking for Oracle
http://www.youtube.com/watch?v=e8iLgwvQFPo - How to Manage, Understand and Maintain Oracle RAC
http://www.youtube.com/watch?v=TI2nADjokfc - Migrating Oracle Single Instance to RAC

ORACLE_HOME
ORACLE_SID

How can we purge caches?

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

If you are running Linux and using cooked filesystems for your datafiles I think you can get your system admin to run the following to flush 
out the filesystem cache and help support the theory of the secondary SGA:

sync; echo 3 > /proc/sys/vm/drop_caches

....might be easier to get done than a reboot....

I guess there can also be caching at other layers that cannot be disregarded (SAN controllers etc).

How can we start Oracle:

sqlplus /nolog
SQL> connect sys as sysdba
SQL> startup
SQL> exit
lsnrctl start

How can we obtain information about current user?

SELECT osuser INFO FROM sys.v_$session WHERE sid = (SELECT sid FROM sys.v_$mystat WHERE rownum=1);

How can we use SQL Developer to copy objects from one user to another?

  1. Create database connection for the source database user
  2. Create database connection for the destination database user
  3. Click on Tools -> Database Copy
  4. Specify the source connection
  5. Specify the destination connection
  6. Check the checkbox "Proceed to summary" if we do not have any other requirements
  7. Click Next
  8. Click Next

How can we determine the size of the database?

select nvl(b.tablespace_name,
         nvl(a.tablespace_name,'UNKNOWN'))
         tablespace_name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) 
         size_alloc_bytes,
       round(((kbytes_alloc-nvl(kbytes_free,0))/
         kbytes_alloc)*200) used_chart,
       to_char(((kbytes_alloc-nvl(kbytes_free,0))/
         kbytes_alloc)*100,
         '999G999G999G999G999G999G990D00') ||'%' used,
       data_files
  from ( select sum(bytes)/1024/1024 Kbytes_free,
              max(bytes)/1024/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024 Kbytes_alloc,
              tablespace_name, count(*) data_files
       from sys.dba_data_files
       group by tablespace_name )b
 where a.tablespace_name (+) = b.tablespace_name;

select sum(bytes) Bytes,
round(sum(bytes)/power(1000,1)) KiloBytes,
round(sum(bytes)/power(1000,2)) MegaBytes,
round(sum(bytes)/power(1000,3)) GigaBytes,
round(sum(bytes)/power(1000,4)) TeraBytes,
round(sum(bytes)/power(1000,5)) PetaBytes,
round(sum(bytes)/power(1000,6)) ExaBytes,
round(sum(bytes)/power(1000,7)) ZettaBytes,
round(sum(bytes)/power(1000,8)) YottaBytes
from dba_data_files;

For the above SQL statement to work, we need to be connected using the SYS or SYSDBA user.

If DB is monitored in Grid Control, then, in emrep database execute this query (History of DB size):

SELECT DECODE(m.metric_column, 'ALLOCATED_GB', 'ALLOCATED_GB', 'USED_GB', 'USED_GB') AS bb,
  m.rollup_timestamp AS rollup_timestamp,
  SUM(m.average) AS value
FROM mgmt$metric_daily m,
  mgmt$target_type t
WHERE t.target_guid=
  (SELECT target_guid FROM mgmt$target WHERE target_name='ORCL' /* Your DB name */
  )
AND (t.target_type ='rac_database'
OR (t.target_type ='oracle_database'
AND t.TYPE_QUALIFIER3 != 'RACINST'))
AND m.target_guid =t.target_guid
AND m.metric_guid =t.metric_guid
AND t.metric_name ='DATABASE_SIZE'
AND (t.metric_column ='ALLOCATED_GB'
OR t.metric_column ='USED_GB')
AND m.rollup_timestamp >= '01.01.2010' /* Start date */
AND m.rollup_timestamp <= SYSDATE
AND DECODE(m.metric_column, 'ALLOCATED_GB', 'ALLOCATED_GB', 'USED_GB', 'USED_GB')='USED_GB'
GROUP BY DECODE(m.metric_column,'ALLOCATED_GB','ALLOCATED_GB','USED_GB','USED_GB'),
  m.rollup_timestamp
ORDER BY 2;

An oracle database consists of data files, redo log files, control files, temporary files. The size of the database actually means the total size of all these files:

select 
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

How can we determine the size of a table?

SELECT segment_name, segment_type, bytes / 1024 / 1024 MB
  FROM dba_segments
 WHERE segment_type = 'TABLE' AND segment_name = 'PROVIDER_INFO';

SELECT owner,
       segment_name,
       segment_type,
       tablespace_name,
       bytes / 1048576 MB,
       initial_extent,
       next_extent,
       extents,
       pct_increase
  FROM DBA_SEGMENTS
 WHERE     OWNER = 'SRM'
       AND SEGMENT_NAME = 'PROVIDER_INFO'
       AND SEGMENT_TYPE = 'TABLE';

What does SGA abbreviate for?

System Global Area (SGA) is a shared memory region that Oracle uses to store data and control information for one Oracle instance. Each oracle instance has its own SGA. The SGA is made up of database buffers, the redo log buffer, and the shared pool. Each has a fixed size and is created at startup.

What is the purpose of the MAXDATAFILES parameter?

The information for a single table can span many data files or many tables can share a set of data files. Spreading table spaces over many data files can have a significant positive affect on performance. The number of data files that can be configured is limited by the MAXDATAFILES parameter.

What is the purpose of the redo log?

Redo log files hold information for used for recovery in the event of a system failure. The redo log files must be protected against hardware failure.

What are the purpose of the control files?

Control files contain information used to start an instance such as location of data files and redo log files. Control files must be protected. Oracle provide a mechanism for storing multiple copies of control files.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License