Oracle Table and index size

COLUMN TABLE_NAME FORMAT A32

COLUMN OBJECT_NAME FORMAT A32

COLUMN OWNER FORMAT A10

SELECT

owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg

FROM

(SELECT segment_name table_name, owner, bytes

FROM dba_segments

WHERE segment_type = ‘TABLE’

UNION ALL

SELECT i.table_name, i.owner, s.bytes

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND   s.owner = i.owner

AND   s.segment_type = ‘INDEX’

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND   s.owner = l.owner

AND   s.segment_type = ‘LOBSEGMENT’

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND   s.owner = l.owner

AND   s.segment_type = ‘LOBINDEX’)

WHERE owner in UPPER(‘&owner’)

GROUP BY table_name, owner

HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */

ORDER BY SUM(bytes) desc

;

Advertisements

Dataguard Monitoring Scripts

SET PAGESIZE 124

COL DB_NAME FORMAT A8

COL HOSTNAME FORMAT A12

COL LOG_ARCHIVED FORMAT 999999

COL LOG_APPLIED FORMAT 999999

COL LOG_GAP FORMAT 9999

COL APPLIED_TIME FORMAT A12

SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAPFROM(SELECT NAME DB_NAMEFROM V$DATABASE),(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.’),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,’.’)-1))))) HOSTNAMEFROM V$INSTANCE),(SELECT MAX(SEQUENCE#) LOG_ARCHIVEDFROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’),(SELECT MAX(SEQUENCE#) LOG_APPLIEDFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’),(SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIMEFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);

 

 

 

================================================================================

 

SQL> select status,instance_name,database_role fromv$instance,v$database; 

STATUS       INSTANCE_NAME    DATABASE_ROLE

——————————————–

OPEN         prim             PRIMARY

 

SQL> select max(sequence#) from v$archived_log;

 

MAX(SEQUENCE#)

————–

40

Check the status of the physical standby database and the latest sequence applied on the physcial standby database.

 

 

 

 

 

 

 

 

 

SQL> select status,instance_name,database_role fromv$database,v$instance; 

STATUS   INSTANCE_NAME DATABASE_ROLE

——————————————

MOUNTED  stnd          PHYSICAL STANDBY

 

SQL> select max(sequence#) from v$archived_log where applied=’YES’;

 

MAX(SEQUENCE#)

————–

40

 

 

Check if the Managed Recovery Process (MRP) is active on the physcial standby database.

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL> select process,status,sequence# from v$managed_standby; 

PROCESS   STATUS        SEQUENCE#

——————————-

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

RFS       IDLE          41

RFS       IDLE          0

RFS       IDLE          0

RFS       IDLE          0

MRP0      WAIT_FOR_LOG  41

 

9rows selected.

Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.

Step 4:

Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.

 

 

 

 

 

 

 

 

 

 

 

SQL> alter database recover managed standby database cancel; 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> select status,instance_name,database_role,open_mode fromv$database,v$instance;

 

STATUS INSTANCE_NAME  DATABASE_ROLE    OPEN_MODE

—————————————————

OPEN   stnd           PHYSICAL STANDBY READ ONLY

Step 6:

Now start the MRP on the physical standby database.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL> alter database recover managed standby database disconnectfrom session; 

Database altered.

 

SQL> select process,status,sequence# from v$managed_standby;

 

PROCESS   STATUS        SEQUENCE#

——————————-

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

ARCH      CONNECTED     0

RFS       IDLE          41

RFS       IDLE          0

RFS       IDLE          0

RFS       IDLE          0

MRP0      WAIT_FOR_LOG  41

 

9rows selected.

Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.

 

 

 

Managing dataguard – monitoring scripts

  LOGS             TIME—————- ——————

Last applied   :  16-JUL-09:09:24:16

Last received :  16-JUL-09:09:28:36

 

 

 

 

 

    select ‘Last applied  : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Timefrom v$archived_log

where sequence# = (select max(sequence#) from v$archived_log where applied=’YES’)

union

select ‘Last received : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time

from v$archived_log

where sequence# = (select max(sequence#) from v$archived_log);

 

 

 

 

 

 

 

 

 

 

NAME                   VALUE                  UNIT———————- ———————- ———————————–

apply finish time      +00 00:02:07.2         day(2) to second(1) interval

apply lag              +00 00:01:59           day(2) to second(0) interval

estimated startup time 16                     second

standby has been open  N

transport lag          +00 00:00:00           day(2) to second(0) interval

 

Time Computed: 16-JUL-2009 09:33:16

 

 

 

 

 

 

 

 

 

 

selectNAME Name,

VALUE Value,

UNIT Unit

from v$dataguard_stats

union

select null,null,’ ‘ from dual

union

select null,null,’Time Computed: ‘||MIN(TIME_COMPUTED)

from v$dataguard_stats;

  Redo onsite

——————–

16-JUL-2009 09:42:44

 

 

select to_char(max(last_time),’DD-MON-YYYY HH24:MI:SS’) “Redo onsite”from v$standby_log

 

11g: New Components

Following are new components which are available when installing Oracle 11g:

Oracle Application Express (APEX): APEX is a rapid application development tool for developing database centric web-based applications. In Oracle 11g, it is highly enhanced and available in the Oracle database CD.

Oracle SQL Developer: Oracle SQL Developer is a graphical tool for examining database objects and issuing SQL commands. It is automatically installed, if you create a template-based database.

Oracle Real Application Testing: Oracle Real Application Testing option is automatically installed with the Enterprise Edition installation. This option includes two solutions to test the effect of the system changes on the real-world applications: Database Reply and SQL Performance Analyzer. Both of those solutions will be discussed in later sections.

o Oracle Configuration Manager (OCM): OCM is an optional component and it collects information about software configuration in the Oracle home directories and uploads it to the Oracle configuration repository.

o Oracle Warehouse Builder: it is a business intelligence (BI) design tool and is automatically installed as part of the Oracle database software.

o Oracle Database Vault: Oracle Database Vault component enables you to secure business data even from DBAs. In Oracle 11g, it is a component available during installation and to install it, you must select the Custom installation option.

o Oracle Shadow Copy Service: When you install Oracle 11g on Windows 2003 servers, a service named as Volume Shadow Copy Service (VSS) is installed. This service is an infrastructure that enables the users to create snapshots called shadow copies.

Automatic Diagnostic Repository (ADR) : 11g

ADR is a single directory location for all error and trace data in the database. It replaces the traditional diagnostic directories such as bdump, cdump, and udump. ADR location is controlled by the new initialization parameter DIAGNOSTIC_DEST. Oracle recommends you choose the same ADR base for all Oracle products.