How to Add/Drop/Rename Standby Redolog file

 How to Drop/Rename Standby Redolog file in Oracle 11g
While performing the dataguard Broker, we need to drop the standby database while switchover the standby . As it seems an easy task but it is bit tricky . Below are the steps to drop the redolog file from standby database :

On Standby Database :
SQL> select member,type from v$logfile;
MEMBER                                                                     TYPE                    
----------------------------------                                         -----------
D:\APP\STANDBY\ORADATA\REDO03.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO02.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO01.LOG      ONLINE
D:\APP\STANDBY\ORADATA\REDO04.LOG      STANDBY  
D:\APP\STANDBY\ORADATA\REDO05.LOG      STANDBY

Here,we have to drop the two standby redolog file .

SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Now to solve this issue we have cancel the managed recovery session and set  "standby_file_management"  to manual and drop the standby redolog file  as

SQL> alter database recover managed standby database cancel ;
Database altered.

SQL> alter system set standby_file_management='MANUAL' ;
System altered.

SQL>alter database drop standby logfile group 4;
Database altered.

SQL>alter database drop standby logfile group 5;
Database altered.

If the status of standby redolog show the "clearing_current" then we cannot drop "clearing_current" status logs,and for that we have to sync with Primary and clear the log first before dropping as
SQL> alter database clear logfile group n;

Once the standby redologs are dropped then again back to recover the standby.

SQL>alter system set standby_file_management='AUTO' ;
System altered.

SQL> alter database recover managed standby database disconnect from session ;

 Adding a standby/Redo log file to standby database:

1. Check the member present in standby database.

SQL> select member from v$logfile;


MEMBER
----------------------------------
D:\STANDB1\REDO03.LOG
D:\STANDB1\REDO02.LOG
D:\STANDB1\REDO01.LOG

2. Cancel the recovery Process.

SQL> alter database recover managed standby database cancel;
Database altered.

3. Check the standby_file_management parameter and set the parameter to MANUAL.

SQL> show parameter standby_file_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
standby_file_management              string      AUTO

SQL> alter system set standby_file_management=manual scope=both;
System altered.

SQL> show parameter standby_file_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
standby_file_management              string      MANUAL

4. Add Redo Logfiles in Standby Database.

SQL> alter database add logfile group 4 'D:\STANDB1\REDO04.LOG' size 100M;
Database altered.

SQL> alter database add logfile group 5 'D:\STANDB1\REDO05.LOG' size 100M;
Database altered.

SQL> alter database add logfile group 6 'D:\STANDB1\REDO06.LOG' size 100M;

SQL> alter database add standby logfile group 7 'D:\STANDB1\STDREDO07.LOG' size 100M;
Database altered.

SQL> alter database add standby logfile group 8 'D:\STANDB1\STDREDO08.LOG' size 100M;
Database altered.

SQL> alter database add standby logfile group 9 'D:\STANDB1\STdREDO09.LOG' size 100M;
Database altered.

SQL> alter database add standby logfile group 10 'D:\STANDB1\STdREDO10.LOG' size 100M;
Database altered.

Oracle Active Data Guard Or Oracle GoldenGate

Oracle Active Data Guard and Oracle GoldenGate


Oracle Active Data Guard and Oracle Golden Gate are strategic capabilities within Oracle's software portfolio. While they generally fall into the category of replication technologies, each has a very different area of focus.
Oracle Active Data Guard provides the best data protection and availability for Oracle Database in the simplest most economical manner by maintaining an exact physical replica of the production copy at a remote location that is open read-only while replication is active. Active Data Guard eliminates compromise inherent in storage remote-mirroring or logical replication solutions though deep integration with Oracle Database and through the simplicity achieved by complete focus on providing real-time data protection and availability for Oracle data.
GoldenGate is an advanced logical replication product that supports multi-master replication, hub and spoke deployment and data transformation, providing customers very flexible options to address the complete range of replication requirements. GoldenGate also supports replication between a broad range of heterogeneous hardware platforms and database management systems. Unlike Active Data Guard, GoldenGate captures primary database changes by reading redo records from disk, transforming those records into a platform independent trail file format, and transmitting the trail file to the target database. GoldenGate maintains a logical replica by converting the trail file into SQL and applying SQL to the target database. The target database is open read-write while synchronization occurs.
When do I use Oracle Active Data Guard?

Use Active Data Guard where emphasis is on simplicity, best data protection, data availability and highest performance:

• Secure physical replication, standby database is open read-only, it is impossible to modify standby data independent of primary transactions
• Simplest, fastest, one-way replication of a complete Oracle Database. No requirements for supplemental logging or performance implications for tables having no primary key or unique index. Little if any performance tuning required at the standby database, the default configuration handles most workloads.
• No restrictions - Oracle Data Guard Redo Apply supports all Oracle features and transparently replicates all data and storage types, PL/SQL packages and DDL without special considerations
• Best data protection – Replication direct from memory isolates the standby from I/O corruption that can occur at the primary database. Detect silent lost-write corruption that can occur independently on primary or standby. Automatically detect and repair physical block corruption that can occur independently on primary or standby.
• Choice of synchronous with zero data loss, or asynchronous with near-zero data loss protection
• Simple to improve ROI by offloading read-only workload and/or backups to a synchronized physical standby
• Transparency of backups - an Oracle Data Guard primary and standby are physically exact copies of each other, RMAN backups are completely interchangeable
• Minimize planned downtime and risk using Data Guard standby; standby first patching, database rolling upgrades, and select platform migrations (see My Oracle Support Note 413484.1 for mixed platform combinations)
• A single command will convert a physical standby database as a test system open read-write. A second command will convert it back to a physical standby database and resynchronize it with the primary; primary data is protected at all times.
• Integrated management of a complete configuration - Oracle Data Guard Broker command line or Oracle Enterprise Manager Cloud Control, integrated automatic database and client failover

Active Data Guard is a superset of Data Guard capabilities included with Oracle Enterprise Edition and can be purchased as the Active Data Guard Option for Oracle Database Enterprise Edition. It is also included with every Oracle GoldenGate license, offering customers the ability to acquire the complete set of advanced Oracle replication capabilities with a single purchase. Basic Data Guard functionality does not require a separate license, it is included with Oracle Enterprise Edition.

When do I use Oracle GoldenGate?
Use Oracle GoldenGate when a replica database must be open read-write while replication is active, or for advanced replication requirements beyond what is addressed by Active Data Guard:

• Any requirement where the replica database must be open read-write while synchronizing with the primary database
• Any advanced replication requirements such as: multimaster and bidirectional replication, subset replication, many to one replication, cross endian replication, and data transformations
• Maintenance and migrations where zero downtime using bi-directional replication is required
• Application upgrades that modify back-end database objects. GoldenGate maintains availability and reduces planned downtime by replicating between old and new versions of the database (the user implements mapping between differences in database objects in old and new versions of the database
• Any cross platform migration not supported by Data Guard (e.g. cross endian platform migration)
• Any replication requirement where you replicate from a more recent version of Oracle Database to an earlier version of Oracle Database (e.g. from Oracle Database 11g to Oracle Database 10g)

When do I use Active Data Guard and GoldenGate Together

Active Data Guard and GoldenGate are not mutually exclusive. The following examples of high availability architectures that include the simultaneous use of both technologies:

• An Active Data Guard standby is utilized for disaster protection and database rolling upgrades for a mission critical OLTP database. GoldenGate is used to extract data from the Data Guard primary database (or from the standby database using GoldenGate ALO mode) for ETL update of an enterprise data warehouse.
• GoldenGate subset replication is used extract, transforms, and aggregate data from numerous data sources into a central operational data store (ODS). The ODS supports mission critical application systems that generate significant revenue for the company. An Active Data Guard standby database is used to protect the ODS, providing optimal data protection and availability.
• GoldenGate multi-master replication is utilized to synchronize several databases, each located in different geographies. Each GoldenGate copy has its own local synchronous Data Guard standby database that enables zero data loss failover should an outage occur. GoldenGate capture and apply processes are easily restarted on the new primary database following a failover since primary and standby are an exact, up-to-date replica of each other.
• Reducing planned downtime for various planned maintenance activities not supported by Data Guard – such as cross-endian platform migration or application upgrades that modify back-end database objects. In many cases customers wish to isolate a current mission critical production environment having a Data Guard primary and standby database from being impacted by the maintenance activity. A parallel environment (new primary and standby) is deployed on the new release or new platform and GoldenGate one-way or bi-directional replication is used to keep old and new environments synchronized. Production is moved to the new environment once sufficiently tested. GoldenGate can continue to replicate to the previous environment for fast fall-back until the stability of the new version is no longer in question, at which time the old environment can be de-commissioned.

Reference links :
http://www.oracle.com/technetwork/database/features/availability/dataguardgoldengate-096557.html

TOAD USER SCRIPT

SCRIPT TO CREATE TOAD USER :

I really had to struggle a lot to get this script.Creating these objects as toad user will allow u to gathers stats for disk ,data file and table spaces growth and trending.

This scipt need to be run as toad user to create all the tables & ddl needed for toad user and space management graphs.



REM This script was created by version 10.6.1.3 of the TOAD Server Side Objects Wizard
DEFINE OWNER = 'TOAD'

Prompt ============================================================================
Prompt Creating the TOAD User
Prompt ============================================================================
Prompt Creating the TOAD User
CREATE USER TOAD IDENTIFIED BY toad1234
DEFAULT TABLESPACE TOAD
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON TOAD;

Prompt Granting System Privileges to TOAD
Grant UNLIMITED TABLESPACE to TOAD;
Grant ALTER SESSION to TOAD;
Grant CREATE SEQUENCE to TOAD;
Grant CREATE SESSION to TOAD;
Grant CREATE SYNONYM to TOAD;
Grant CREATE TRIGGER to TOAD;
Grant CREATE PUBLIC SYNONYM to TOAD;
Grant CREATE TABLE to TOAD;
Grant CREATE VIEW to TOAD;
Grant CREATE PROCEDURE to TOAD;



Prompt ============================================================================
Prompt Granting CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM
Prompt ============================================================================
Prompt Granting CREATE PUBLIC SYNONYM to TOAD
GRANT CREATE PUBLIC SYNONYM TO TOAD;

Prompt Granting DROP PUBLIC SYNONYM to TOAD
GRANT DROP PUBLIC SYNONYM TO TOAD;


Prompt ============================================================================
Prompt Adding necessary grants to TOAD
Prompt ============================================================================
Prompt Granting SELECT ANY DICTIONARY to TOAD
GRANT SELECT ANY DICTIONARY TO &&OWNER;


Prompt ============================================================================
Prompt Granting EXECUTE ANY PROCEDURE to %s (Used for the Profiler)
Prompt ============================================================================
Prompt Granting EXECUTE ANY PROCEDURE to TOAD (Used for the Profiler)
GRANT EXECUTE ANY PROCEDURE TO TOAD;


Prompt ============================================================================
Prompt Granting CREATE ROLE
Prompt ============================================================================
Prompt Granting CREATE ROLE to TOAD (Used for Team Coding)
GRANT CREATE ROLE TO TOAD;


Prompt ============================================================================
Prompt Granting CREATE VIEW
Prompt ============================================================================
Prompt Granting CREATE VIEW to TOAD
GRANT CREATE VIEW TO TOAD;


Prompt ============================================================================
Prompt Connecting as TOAD
Prompt ============================================================================
Prompt Connecting as TOAD
CONNECT TOAD/toad1234@QPDEV_BV
Prompt 


Prompt ============================================================================
Prompt Creating TOAD Profiler Objects in TOAD schema
Prompt ============================================================================
DEFINE tablespace_info = ' TABLESPACE TOAD'
DEFINE ALLT = DBA_TRIGGERS
DEFINE ALLS = DBA_SOURCE

Prompt Creating table PLSQL_PROFILER_RUNS
CREATE TABLE &&OWNER..plsql_profiler_runs
(
  runid           NUMBER primary key, -- unique run identifier, from plsql_profiler_runnumber
  related_run     NUMBER,             -- runid of related run (for client-server correlation)
  run_owner       VARCHAR2(32),       -- user that executed the procedure
  run_proc        VARCHAR2(256),      -- procedure that was executed
  run_date        DATE,               -- start time of run
  run_comment     VARCHAR2(2047),     -- user provided comment for this run
  run_total_time  NUMBER,             -- elapsed time for this run
  run_system_info VARCHAR2(2047),     -- currently unused
  run_comment1    VARCHAR2(256),      -- additional comment
  spare1          VARCHAR2(256)       -- unused
)
&&tablespace_info;

Prompt Adding comment to PLSQL_PROFILER_RUNS
COMMENT ON TABLE &&OWNER..plsql_profiler_runs IS
        'Run-specific information for the PL/SQL profiler';
       
Prompt Creating table PLSQL_PROFILER_UNITS
CREATE TABLE &&OWNER..plsql_profiler_units
(
  runid              NUMBER references &&OWNER..plsql_profiler_runs ON DELETE cascade,
  unit_number        NUMBER,             -- internally generated library unit #
  unit_type          VARCHAR2(32),       -- library unit type
  unit_owner         VARCHAR2(32),       -- library unit owner name
  unit_name          VARCHAR2(32),       -- library unit name
  unit_timestamp     DATE,
    -- timestamp on library unit, can be used to detect changes to unit between runs
  total_time         NUMBER DEFAULT 0 NOT NULL,
  spare1             NUMBER,             -- unused
  spare2             NUMBER,             -- unused
  primary key (runid, unit_number)
)
&&tablespace_info;

Prompt Adding comment to PLSQL_PROFILER_UNITS
COMMENT ON TABLE &&OWNER..plsql_profiler_units IS
        'Information about each library unit in a run';
       
Prompt Creating table PLSQL_PROFILER_DATA
CREATE TABLE &&OWNER..plsql_profiler_data
(
  runid           NUMBER,           -- unique (generated) run identifier
  unit_number     NUMBER,           -- internally generated library unit #
  line#           NUMBER NOT NULL,  -- line number in unit
  text            VARCHAR2(4000),   -- source for the line
  total_occur     NUMBER,           -- number of times line was executed
  total_time      NUMBER,           -- total time spent executing line
  min_time        NUMBER,           -- minimum execution time for this line
  max_time        NUMBER,           -- maximum execution time for this line
  spare1          NUMBER,           -- unused
  spare2          NUMBER,           -- unused
  spare3          NUMBER,           -- unused
  spare4          NUMBER,           -- unused
  primary key (runid, unit_number, line#),
  foreign key (runid, unit_number) references &&OWNER..plsql_profiler_units ON DELETE CASCADE)
&&tablespace_info;

Prompt Adding comment to PLSQL_PROFILER_DATA
COMMENT ON TABLE &&OWNER..plsql_profiler_data IS
        'Accumulated data from all profiler runs';
Prompt Creating package spec TOAD_PROFILER
CREATE OR REPLACE PACKAGE &&OWNER..toad_profiler is
  procedure rollup_unit(run_number IN number, UnitNumber IN number,
    UnitType IN varchar2, UnitOwner IN varchar2, UnitName IN varchar2);
  procedure rollup_run(run_number IN number);
  procedure rollup_all_runs;
end toad_profiler;
/
Prompt Creating package body TOAD_PROFILER
CREATE OR REPLACE PACKAGE BODY &&OWNER..toad_profiler is
  -- compute the total time spent executing this unit - the sum of the
  -- time spent executing lines in this unit (for this run)
  procedure rollup_unit(run_number IN number, UnitNumber IN number,
    UnitType IN varchar2, UnitOwner IN varchar2, UnitName IN varchar2) is
  TYPE TSourceTable IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
  SourceTable TSourceTable;
  TriggerBody long;
  FoundTriggerSource boolean;
  Cnt number;
  LnStart number;
  LnEnd   number;
  Pos number;
  vText varchar2(4000);
  IsWrapped boolean;
  TotalTime number;
  -- Select the lines for the unit to find source code
  cursor cLines(run_number number, UnitNumber number) is
    select line# from plsql_profiler_data
    where runid = run_number and unit_number = UnitNumber;
  begin
    select sum(total_time) into TotalTime
      from plsql_profiler_data
      where runid = run_number and unit_number = UnitNumber;
    if TotalTime IS NULL then
      TotalTime := 0;
    end if;
    update plsql_profiler_units set total_time = TotalTime
    where runid = run_number and unit_number = UnitNumber;
    -- Get trigger source into index-by table
    if UnitType = 'TRIGGER' then
      begin
        FoundTriggerSource := True;
        select trigger_body into TriggerBody
          from dba_triggers where owner = UnitOwner and trigger_name = UnitName;
      exception
        when NO_DATA_FOUND then
          FoundTriggerSource := False;
      end;
      if FoundTriggerSource then
        Cnt     := 1;
        LnStart := 1;
        loop
          LnEnd := INSTR(TriggerBody, CHR(10), 1, Cnt);
          if (LnEnd = 0) then
            SourceTable(Cnt) := SubStr(TriggerBody, LnStart);
          else
            SourceTable(Cnt) := Substr(TriggerBody, LnStart, (LnEnd-LnStart));
          end if;
          LnStart := LnStart + (LnEnd-LnStart)+1;
          Cnt := Cnt+1;
          exit when (lnEnd = 0);
        end loop;
      end if;
    -- see if the code is wrapped
    else
      begin
        select upper(text) into vtext from dba_source s
          where s.type = UnitType and s.owner = UnitOwner and
                s.name = UnitName and s.line = 1;
        IsWrapped := (INSTR(vText, ' WRAPPED') > 0);
      exception
        when NO_DATA_FOUND then
          IsWrapped := False;
      end;
    end if;

    -- Get the source for each line in unit
    Cnt := 1;
    for linerec in cLines(run_number, UnitNumber) loop
      if UnitType = 'TRIGGER' then
        if FoundTriggerSource then
          vText := SourceTable(linerec.line#);
        else
          if Cnt = 1 then
            vText := '<source unavailable>';
          else
            vText := null;
          end if;
        end if;
      else
        if IsWrapped then
          if Cnt = 1 then
            vText := '<wrapped>';
          else
            vText := null;
          end if;
        else
          begin
            select text into vtext from dba_source s
              where s.type = UnitType and s.owner = UnitOwner and
                    s.name = UnitName and s.line = linerec.line#;
          exception
            when NO_DATA_FOUND then
              vText := null;
          end;
        end if;
      end if;
      -- store the source line
      update plsql_profiler_data d set d.text = vText
      where d.runid = run_number and d.unit_number = UnitNumber and
            d.line# = linerec.line#;
      Cnt := Cnt+1;
    end loop;
  end rollup_unit;
  -- rollup all units for the given run
  procedure rollup_run(run_number IN number) is
    tabpos number;
    comment varchar2(2047);
    proc varchar2(256 );
    --
    -- only select those units which have not been rolled up yet
    cursor cunits(run_number number) is
      select unit_number, unit_type, unit_owner, unit_name
        from plsql_profiler_units
        where runid = run_number and total_time = 0
        order by unit_number asc;
  begin
    -- Fix Oracle's calling a 'PACKAGE' a 'PACKAGE SPEC'
    update plsql_profiler_units set unit_type = 'PACKAGE'
    where runid = run_number and unit_type like 'PACKAGE SPEC%';
    -- parse the RUN_COMMENT column to get the procedure name
      -- (note: this replaces the BI_PLSQL_PROFILER_RUNS trigger.
    select run_proc, run_comment into proc, comment
      from plsql_profiler_runs where runid = run_number;
    if proc is null then
      tabpos := INSTR(comment, CHR(8));
        if tabpos > 0 THEN
          proc := SUBSTR(comment, tabpos+1);
          comment := SUBSTR(comment, 1, tabpos-1);
        else
          proc := 'ANONYMOUS BLOCK';
        end if;
        update plsql_profiler_runs
          set run_owner = USER, run_proc = proc, run_comment = comment
          where runid = run_number;
    end if;
    for unitrec in cunits(run_number) loop
      rollup_unit(run_number, unitrec.unit_number, unitrec.unit_type,
                  unitrec.unit_owner, unitrec.unit_name);
    end loop;
  end rollup_run;
  -- rollup all runs
  procedure rollup_all_runs is
    cursor crunid is
      select runid from plsql_profiler_runs order by runid asc;
  begin
    for runidrec in crunid loop
      rollup_run(runidrec.runid);
    end loop crunid;
    commit;
  end rollup_all_runs;
end toad_profiler;
/
Prompt Creating sequence PLSQL_PROFILER_RUNNUMBER
CREATE SEQUENCE &&OWNER..plsql_profiler_runnumber START WITH 1 NOCACHE;


Prompt ============================================================================
Prompt Adding public synonyms for Profiler objects
Prompt ============================================================================

Prompt Creating public synonym PLSQL_PROFILER_DATA
CREATE OR REPLACE PUBLIC SYNONYM PLSQL_PROFILER_DATA FOR &&OWNER..PLSQL_PROFILER_DATA;

Prompt Creating public synonym PLSQL_PROFILER_UNITS
CREATE OR REPLACE PUBLIC SYNONYM PLSQL_PROFILER_UNITS FOR &&OWNER..PLSQL_PROFILER_UNITS;

Prompt Creating public synonym PLSQL_PROFILER_RUNS
CREATE OR REPLACE PUBLIC SYNONYM PLSQL_PROFILER_RUNS FOR &&OWNER..PLSQL_PROFILER_RUNS;

Prompt Creating public synonym PLSQL_PROFILER_RUNNUMBER
CREATE OR REPLACE PUBLIC SYNONYM PLSQL_PROFILER_RUNNUMBER FOR &&OWNER..PLSQL_PROFILER_RUNNUMBER;

Prompt Creating public synonym TOAD_PROFILER
CREATE OR REPLACE PUBLIC SYNONYM TOAD_PROFILER FOR &&OWNER..TOAD_PROFILER;


Prompt ============================================================================
Prompt Granting privileges to PUBLIC on Profiler objects
Prompt ============================================================================
Prompt Granting SELECT, INSERT, UPDATE, DELETE on PLSQL_PROFILER_DATA to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..PLSQL_PROFILER_DATA TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on PLSQL_PROFILER_UNITS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..PLSQL_PROFILER_UNITS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on PLSQL_PROFILER_RUNS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..PLSQL_PROFILER_RUNS TO PUBLIC;


Prompt Granting SELECT on PLSQL_PROFILER_RUNNUMBER to PUBLIC
GRANT SELECT ON &&OWNER..PLSQL_PROFILER_RUNNUMBER TO PUBLIC;


Prompt Granting EXECUTE on TOAD_PROFILER to PUBLIC
GRANT EXECUTE ON &&OWNER..TOAD_PROFILER TO PUBLIC;




Prompt ============================================================================
Prompt Creating TOAD Security table
Prompt ============================================================================
Prompt Creating table TOAD.TOAD_RESTRICTIONS
CREATE TABLE TOAD.TOAD_RESTRICTIONS (
  USER_NAME  VARCHAR2(32)  NOT NULL,
  FEATURE    VARCHAR2(20)  NOT NULL,
  CONSTRAINT TOAD_RES_PK
  PRIMARY KEY ( FEATURE, USER_NAME ) )
 TABLESPACE TOAD;



Prompt ============================================================================
Prompt Creating/Upgrading Team Coding objects
Prompt ============================================================================
DEFINE tablespace_info = ' TABLESPACE TOAD'
DEFINE tablespace_ind_info = ' TABLESPACE TOAD'
DEFINE TC_ADMIN = TC_ADMIN_ROLE
DEFINE TC_MGR = TC_MGR_ROLE
DEFINE TC_LDR = TC_LDR_ROLE
Prompt Creating role TC_ADMIN_ROLE
CREATE ROLE TC_ADMIN_ROLE;
Prompt Creating role TC_MGR_ROLE
CREATE ROLE TC_MGR_ROLE;
Prompt Creating role TC_LDR_ROLE
CREATE ROLE TC_LDR_ROLE;
/*
The table TC_MASK has a column called OBJECT_NAME, whose length is normally
set to 649 in order to accommodate a 2K block size on a pre-9i database.  If you 
have extremely long file paths, and your database allows you to increase the size of the
column, then you may wish to increase the size of this column after running this script,
by running:
ALTER TABLE TC_MASK MODIFY OBJECT_NAME VARCHAR2(2000);
*/
DEFINE path_length = 2000
Prompt Creating table TC_FILEEXT
CREATE TABLE &&OWNER..tc_fileext
(
    vcs_objtype               varchar2(20) NOT NULL,
    vcs_objext                varchar2(10) NOT NULL
)
    &&tablespace_info
;

Prompt Adding primary key to TC_FILEEXT
ALTER table &&OWNER..tc_fileext
    add primary key (vcs_objtype)
    using index 
    &&tablespace_ind_info
;

Prompt Inserting procedure extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('PROCEDURE', 'PRC');

Prompt Inserting trigger extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('TRIGGER', 'TRG');

Prompt Inserting function extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('FUNCTION', 'FNC');

Prompt Inserting package extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('PACKAGE', 'PKS');

Prompt Inserting package body extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('PACKAGE BODY', 'PKB');

Prompt Inserting type extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('TYPE', 'TYP');

Prompt Inserting type body extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('TYPE BODY', 'TPB');

Prompt Inserting view extension
INSERT INTO &&OWNER..tc_fileext
            (vcs_objtype, vcs_objext)
     VALUES ('VIEW', 'VW');

Prompt Creating table TC_OBJSTATUS
CREATE TABLE &&OWNER..tc_objstatus
(
    psl_object_id               NUMBER ,
    psl_object_type             VARCHAR2(12) NOT NULL,     
    psl_object_owner            VARCHAR2(30) NOT NULL,     
    psl_object_name             VARCHAR2(2000) NOT NULL,     
    psl_checked_out             VARCHAR2(1) NOT NULL,
    psl_checked_out_by          VARCHAR2(30) NOT  NULL,
    psl_check_out_timestamp     DATE NOT NULL,
    psl_check_in_timestamp      DATE,
    psl_frozen                  VARCHAR2(1) NOT NULL,
    psl_frozen_by               VARCHAR2(30),
    psl_frozen_timestamp        DATE,
    psl_comments                VARCHAR2(2000),
    constraint tc_objstatus_pk 
      primary key (psl_object_id) 
      using index &&tablespace_ind_info
)
    &&tablespace_info
;

Prompt Creating index TC_OBJSTATUS_NDX
CREATE UNIQUE index &&OWNER..tc_objstatus_ndx
    on tc_objstatus (psl_object_id, psl_checked_out, psl_frozen, psl_checked_out_by)
    &&tablespace_ind_info
;
Prompt Creating table TC_OBJSTATUS_VCS
CREATE TABLE &&OWNER..tc_objstatus_vcs
(
    psl_object_id NUMBER NOT NULL,
    project_id NUMBER,
    filename VARCHAR2(2000),
    locked_by VARCHAR2(255)
)
    &&tablespace_info
;
Prompt Adding primary key to TC_OBJSTATUS_VCS
ALTER table &&OWNER..tc_objstatus_vcs
    add constraint tc_objstatus_vcs_pk 
    primary key (psl_object_id)
    using index &&tablespace_ind_info
;
Prompt Creating sequence TC_SCRIPT_ID
CREATE SEQUENCE &&OWNER..tc_script_id
  INCREMENT BY -1
  MINVALUE -2147483648
  MAXVALUE -10
  NOCYCLE
  NOORDER
  NOCACHE
;
Prompt Creating table TC_GROUP
CREATE TABLE &&OWNER..tc_group
(
    project_id            NUMBER NOT NULL,
    project_name          VARCHAR2(255) NOT NULL,
    workdir               VARCHAR2(2000),
    creation_date         DATE NOT NULL,
    last_modified_date    DATE,
    author                VARCHAR2(30),
    checked_out           INTEGER,
    checked_out_timestamp DATE,
    checked_out_by        VARCHAR2(30),
    checked_in_timestamp  DATE,
    vcp_project           VARCHAR2(2000),
    vcs_db                VARCHAR2(2000),
    frozen                VARCHAR2(1) DEFAULT 'N',
    frozen_by             VARCHAR2(30),
    frozen_timestamp      DATE,
    version               VARCHAR2(6)
)
    &&tablespace_info
;

Prompt Adding primary key to TC_GROUP
ALTER table &&OWNER..tc_group 
    add primary key (project_id)
    using index &&tablespace_ind_info
;

Prompt Creating sequence TC_GROUP_ID
CREATE SEQUENCE &&OWNER..TC_GROUP_ID
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 2147483647
  NOCYCLE
  NOORDER
  NOCACHE
;
Prompt Creating table TC_MASK
CREATE TABLE &&OWNER..tc_mask
(
    project_id                 number NOT NULL,
    object_name                varchar2(&&path_length) NOT NULL,
    object_type                number NOT NULL,
    schema_name                varchar2(30) NOT NULL,
    exclude                    number NOT NULL,
    comments                   varchar2(1000)
)
    &&tablespace_info
;

Prompt Adding primary key to TC_MASK
ALTER table &&OWNER..tc_mask 
    add constraint tc_mask_pk
      primary key (project_id, object_name, object_type, schema_name, exclude)
      using index &&tablespace_ind_info;

Prompt Creating table TC_USERMAPPING
CREATE TABLE &&OWNER..tc_usermapping
(
    project_id  number NOT NULL,
    username    varchar2(30),
    schema      varchar2(30)
)
    &&tablespace_info
;
Prompt Creating table TC_CONFIG
CREATE TABLE &&OWNER..tc_config
(
    options                VARCHAR2(16),
    set_working_directory  VARCHAR2(255),
    script_ext             VARCHAR2(255),
    vcs_vcp_name           NUMBER,
    vcs_db                 VARCHAR2(255),
    vcs_sub_vcp            VARCHAR2(255),
    frozen                 VARCHAR2(1),
    frozen_by              VARCHAR2(30),
    frozen_timestamp       DATE,
    version                VARCHAR2(16)
)
    &&tablespace_info
;

Prompt Inserting Config defaults
INSERT INTO &&OWNER..tc_config
            (script_ext, vcs_vcp_name, frozen, version)
     VALUES ('*.sql;', -1, 'N', '0060TOAD08500000');

Prompt Creating table TC_TIMESTAMP
CREATE TABLE &&OWNER..tc_timestamp (
    tp_timestamp        date,
    project_timestamp   date,
    um_timestamp        date
)
    &&tablespace_info
;

Prompt Inserting null row into TC_TIMESTAMP
INSERT INTO &&OWNER..tc_timestamp
            (tp_timestamp, project_timestamp, um_timestamp)
     VALUES (NULL, NULL, NULL);
Prompt Creating trigger TC_TIMESTAMP_OBJSTATUS
CREATE OR REPLACE TRIGGER &&OWNER..tc_timestamp_objstatus
  AFTER INSERT OR DELETE OR UPDATE
  ON &&OWNER..tc_objstatus
  REFERENCING OLD AS OLD NEW AS NEW
BEGIN
  UPDATE &&OWNER..tc_timestamp
     SET tp_timestamp = SYSDATE;
END;
/

Prompt Creating trigger TC_TIMESTAMP_MASK
CREATE OR REPLACE TRIGGER &&OWNER..tc_timestamp_mask
  AFTER INSERT OR DELETE OR UPDATE
  ON &&OWNER..tc_mask
  REFERENCING OLD AS OLD NEW AS NEW
BEGIN
  UPDATE &&OWNER..tc_timestamp
     SET project_timestamp = SYSDATE;
END;
/

Prompt Creating trigger TC_TIMESTAMP_GROUP
CREATE OR REPLACE TRIGGER &&OWNER..tc_timestamp_group
  AFTER INSERT OR DELETE OR UPDATE
  ON &&OWNER..tc_group
  REFERENCING OLD AS OLD NEW AS NEW
BEGIN
  UPDATE &&OWNER..tc_timestamp
     SET project_timestamp = SYSDATE;
END;
/

Prompt Creating trigger TC_TIMESTAMP_USERMAPPING
CREATE OR REPLACE TRIGGER &&OWNER..tc_timestamp_usermapping
  AFTER INSERT OR UPDATE OR DELETE
  ON &&OWNER..tc_usermapping
  REFERENCING NEW AS NEW OLD AS OLD
BEGIN
  UPDATE &&OWNER..tc_timestamp
     SET um_timestamp = SYSDATE;
END;
/
Prompt Creating trigger TC_DELETE_GROUP
CREATE OR REPLACE TRIGGER &&OWNER..tc_delete_group
  BEFORE DELETE
  ON &&OWNER..tc_group
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
BEGIN
  DELETE FROM &&OWNER..tc_mask
        WHERE project_id = :OLD.project_id;

  DELETE FROM &&OWNER..tc_usermapping
        WHERE project_id = :OLD.project_id;

  DELETE FROM &&OWNER..tc_objstatus_vcs
      WHERE project_id = :OLD.project_id;
  /* the tc_delete_obj trigger (below) takes care of the tc_objstatus table */  
END;
/

Prompt Creating trigger TC_DELETE_OBJ
CREATE OR REPLACE TRIGGER &&OWNER..tc_delete_obj
  BEFORE DELETE
  ON &&OWNER..tc_objstatus_vcs
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
begin
  DELETE FROM &&OWNER..tc_objstatus
        WHERE psl_object_id = :OLD.psl_object_id;
end;
/

SET DEFINE ON
Prompt Granting SELECT, UPDATE on TC_FILEEXT to PUBLIC
GRANT SELECT, UPDATE ON &&OWNER..TC_FILEEXT TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS_VCS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS_VCS TO PUBLIC;


Prompt Granting SELECT on TC_GROUP_ID to PUBLIC
GRANT SELECT ON &&OWNER..TC_GROUP_ID TO PUBLIC;


Prompt Granting SELECT on TC_GROUP to PUBLIC
GRANT SELECT ON &&OWNER..TC_GROUP TO PUBLIC;


Prompt Granting SELECT on TC_MASK to PUBLIC
GRANT SELECT ON &&OWNER..TC_MASK TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_USERMAPPING to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_USERMAPPING TO PUBLIC;


Prompt Granting SELECT on TC_CONFIG to PUBLIC
GRANT SELECT ON &&OWNER..TC_CONFIG TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_TIMESTAMP to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_TIMESTAMP TO PUBLIC;


Prompt Granting SELECT on TC_SCRIPT_ID to PUBLIC
GRANT SELECT ON &&OWNER..TC_SCRIPT_ID TO PUBLIC;


DEFINE TC_ADM = TC_ADMIN_ROLE
Prompt Granting SELECT, UPDATE on TC_FILEEXT to &&TC_ADMIN
GRANT SELECT, UPDATE ON &&OWNER..TC_FILEEXT TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS_VCS to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS_VCS TO &&TC_ADMIN;


Prompt Granting SELECT on TC_GROUP_ID to &&TC_ADMIN
GRANT SELECT ON &&OWNER..TC_GROUP_ID TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_GROUP to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_GROUP TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_MASK to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_MASK TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_USERMAPPING to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_USERMAPPING TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_CONFIG to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_CONFIG TO &&TC_ADMIN;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_TIMESTAMP to &&TC_ADMIN
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_TIMESTAMP TO &&TC_ADMIN;


Prompt Granting SELECT on TC_SCRIPT_ID to &&TC_ADMIN
GRANT SELECT ON &&OWNER..TC_SCRIPT_ID TO &&TC_ADMIN;


DEFINE TC_MGR = TC_MGR_ROLE
Prompt Granting SELECT on TC_FILEEXT to &&TC_MGR
GRANT SELECT ON &&OWNER..TC_FILEEXT TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS_VCS to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS_VCS TO &&TC_MGR;


Prompt Granting SELECT on TC_GROUP_ID to &&TC_MGR
GRANT SELECT ON &&OWNER..TC_GROUP_ID TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_GROUP to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_GROUP TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_MASK to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_MASK TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_USERMAPPING to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_USERMAPPING TO &&TC_MGR;


Prompt Granting SELECT on TC_CONFIG to &&TC_MGR
GRANT SELECT ON &&OWNER..TC_CONFIG TO &&TC_MGR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_TIMESTAMP to &&TC_MGR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_TIMESTAMP TO &&TC_MGR;


Prompt Granting SELECT on TC_SCRIPT_ID to &&TC_MGR
GRANT SELECT ON &&OWNER..TC_SCRIPT_ID TO &&TC_MGR;


DEFINE TC_LDR = TC_LDR_ROLE
Prompt Granting SELECT on TC_FILEEXT to &&TC_LDR
GRANT SELECT ON &&OWNER..TC_FILEEXT TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE (PSL_FROZEN, PSL_FROZEN_BY, PSL_FROZEN_TIMESTAMP) on TC_OBJSTATUS to &&TC_LDR
GRANT SELECT, INSERT, UPDATE (PSL_FROZEN, PSL_FROZEN_BY, PSL_FROZEN_TIMESTAMP) ON &&OWNER..TC_OBJSTATUS TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS to &&TC_LDR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_OBJSTATUS_VCS to &&TC_LDR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_OBJSTATUS_VCS TO &&TC_LDR;


Prompt Granting SELECT on TC_GROUP_ID to &&TC_LDR
GRANT SELECT ON &&OWNER..TC_GROUP_ID TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE on TC_GROUP to &&TC_LDR
GRANT SELECT, INSERT, UPDATE ON &&OWNER..TC_GROUP TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_MASK to &&TC_LDR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_MASK TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_USERMAPPING to &&TC_LDR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_USERMAPPING TO &&TC_LDR;


Prompt Granting SELECT on TC_CONFIG to &&TC_LDR
GRANT SELECT ON &&OWNER..TC_CONFIG TO &&TC_LDR;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on TC_TIMESTAMP to &&TC_LDR
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..TC_TIMESTAMP TO &&TC_LDR;


Prompt Granting SELECT on TC_SCRIPT_ID to &&TC_LDR
GRANT SELECT ON &&OWNER..TC_SCRIPT_ID TO &&TC_LDR;


Prompt Creating public synonym QUEST_COM_TEAM_CODING
CREATE OR REPLACE PUBLIC SYNONYM QUEST_COM_TEAM_CODING FOR &&OWNER..TC_CONFIG;


Prompt ============================================================================
Prompt Creating/Upgrading CodeXpert objects
Prompt ============================================================================
DEFINE TSP_TABS = ' TABLESPACE TOAD'
DEFINE TSP_INDS = ' TABLESPACE TOAD'
Prompt Creating table CX_CATEGORYS
CREATE TABLE &&OWNER..CX_CATEGORYS
(
  CAT_ID       INTEGER                     NOT NULL,
  DESCRIPTION  VARCHAR2(20)                NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_CATEGORYS
ALTER TABLE &&OWNER..CX_CATEGORYS ADD (
  PRIMARY KEY
 (CAT_ID)
    USING INDEX
     &&TSP_INDS
); Prompt Creating table CX_TYPES
CREATE TABLE &&OWNER..CX_TYPES
(
  TYP_ID       INTEGER                     NOT NULL,
  DESCRIPTION  VARCHAR2(20)                NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_TYPES
ALTER TABLE &&OWNER..CX_TYPES ADD (
  PRIMARY KEY
 (TYP_ID)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_BASERULES
CREATE TABLE &&OWNER..CX_BASERULES
(
  RUL_ID      INTEGER                      NOT NULL,
  CAT_ID      INTEGER                      NOT NULL,
  SEV_ID      INTEGER                      NOT NULL,
  TYP_ID      INTEGER                      NOT NULL,
  DEFINITION  VARCHAR2(1000)               NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_BASERULES
ALTER TABLE &&OWNER..CX_BASERULES ADD (
  PRIMARY KEY
 (RUL_ID)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_SEVERITYS
CREATE TABLE &&OWNER..CX_SEVERITYS
(
  SEV_ID       INTEGER                     NOT NULL,
  DESCRIPTION  VARCHAR2(20)                NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_SEVERITYS
ALTER TABLE &&OWNER..CX_SEVERITYS ADD (
  PRIMARY KEY
 (SEV_ID)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_RULESETS
CREATE TABLE &&OWNER..CX_RULESETS
(
  TITLE     VARCHAR2(100)                  NOT NULL,
  AUTHOR    VARCHAR2(50),
  CREATED   DATE                           NOT NULL,
  MODIFIED  DATE
)
 &&TSP_TABS;
Prompt Adding primary key to CX_RULESETS
ALTER TABLE &&OWNER..CX_RULESETS ADD (
  PRIMARY KEY
 (TITLE)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_SETRULES
CREATE TABLE &&OWNER..CX_SETRULES
(
  RUL_ID  INTEGER                          NOT NULL,
  TITLE   VARCHAR2(100)                    NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_SETRULES
ALTER TABLE &&OWNER..CX_SETRULES ADD (
  PRIMARY KEY
 (RUL_ID, TITLE)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_XPERTLINES
CREATE TABLE &&OWNER..CX_XPERTLINES
(
  RUNNAME     VARCHAR2(100)                NOT NULL,
  ITEMNO      INTEGER                      NOT NULL,
  LINENO      INTEGER                      NOT NULL,
  LINEPOS     INTEGER                      NOT NULL,
  RUL_ID      INTEGER                      NOT NULL,
  TITLE       VARCHAR2(100)                NOT NULL
)
 &&TSP_TABS;
Prompt Creating table CX_XPERTITEMS
CREATE TABLE &&OWNER..CX_XPERTITEMS
(
  RUNNAME   VARCHAR2(100)                  NOT NULL,
  ITEMNO    INTEGER                        NOT NULL,
  INSTANCE  VARCHAR2(20)                   NOT NULL,
  "SCHEMA"  VARCHAR2(50)                   NOT NULL,
  OBJNAME   VARCHAR2(50)                   NOT NULL,
  OBJTYPE   VARCHAR2(100),
  SCRIPT    CLOB
)
 &&TSP_TABS;
Prompt Adding primary key to CX_XPERTITEMS
ALTER TABLE &&OWNER..CX_XPERTITEMS ADD (
  PRIMARY KEY
 (RUNNAME, ITEMNO)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_METRICS
CREATE TABLE &&OWNER..CX_METRICS
(
  RUNNAME      VARCHAR2(100)               NOT NULL,
  ITEMNO       INTEGER                     NOT NULL,
  SCORE        FLOAT(126)                  NOT NULL,
  MET_NAME     VARCHAR2(50)                NOT NULL,
  MET_MEANING  VARCHAR2(50)                NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_METRICS
ALTER TABLE &&OWNER..CX_METRICS ADD (
  PRIMARY KEY
 (RUNNAME, ITEMNO, MET_NAME, MET_MEANING)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating table CX_XPERTRUN
CREATE TABLE &&OWNER..CX_XPERTRUN
(
  RUN_ID      INTEGER                      NOT NULL,
  RUNNAME     VARCHAR2(100)                NOT NULL,
  RUNDATE     DATE                         NOT NULL,
  RUNCOMMENT  VARCHAR2(1000)
)
 &&TSP_TABS;
Prompt Adding primary key to CX_XPERTRUN
ALTER TABLE &&OWNER..CX_XPERTRUN ADD (
  PRIMARY KEY
 (RUNNAME)
    USING INDEX
     &&TSP_INDS
);
Prompt Creating sequence CX_XPERTRUN_ID
CREATE SEQUENCE &&OWNER..CX_XPERTRUN_ID
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;
Prompt Creating trigger CX_XPERTRUN_INSERT
CREATE TRIGGER &&OWNER..CX_XPERTRUN_INSERT
 BEFORE INSERT ON &&OWNER..CX_XPERTRUN
 FOR EACH ROW
DECLARE
   tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   SELECT &&OWNER..CX_XPERTRUN_ID.NEXTVAL INTO tmpVar FROM dual;

   :NEW.RUN_ID := tmpVar;

END CX_XPERTRUN_INSERT;
/
Prompt Creating table CX_METRICRANGES
CREATE TABLE &&OWNER..CX_METRICRANGES
(
  MET_NAME     VARCHAR2(50)                NOT NULL,
  MET_MEANING  VARCHAR2(50)                NOT NULL,
  RANGE_LO     FLOAT(126)                  NOT NULL,
  RANGE_HI     FLOAT(126)                  NOT NULL,
  COLOR        VARCHAR2(50)                NOT NULL
)
 &&TSP_TABS;
Prompt Adding primary key to CX_METRICRANGES
ALTER TABLE &&OWNER..CX_METRICRANGES ADD (
  PRIMARY KEY
 (MET_NAME, MET_MEANING)
    USING INDEX
     &&TSP_INDS
);
Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_CATEGORYS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_CATEGORYS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_SEVERITYS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_SEVERITYS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_TYPES to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_TYPES TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_BASERULES to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_BASERULES TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_RULESETS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_RULESETS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_SETRULES to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_SETRULES TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_XPERTRUN to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_XPERTRUN TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_XPERTITEMS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_XPERTITEMS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_XPERTLINES to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_XPERTLINES TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_METRICS to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_METRICS TO PUBLIC;


Prompt Granting SELECT, INSERT, UPDATE, DELETE on CX_METRICRANGES to PUBLIC
GRANT SELECT, INSERT, UPDATE, DELETE ON &&OWNER..CX_METRICRANGES TO PUBLIC;


Prompt Creating public synonym QUEST_COM_CODEXPERT
CREATE PUBLIC SYNONYM QUEST_COM_CODEXPERT FOR &&OWNER..CX_XPERTRUN;

Prompt Creating public synonym CX_XPERTRUN
CREATE PUBLIC SYNONYM CX_XPERTRUN FOR &&OWNER..CX_XPERTRUN;

Prompt Creating public synonym CX_XPERTLINES
CREATE PUBLIC SYNONYM CX_XPERTLINES FOR &&OWNER..CX_XPERTLINES;

Prompt Creating public synonym CX_XPERTITEMS
CREATE PUBLIC SYNONYM CX_XPERTITEMS FOR &&OWNER..CX_XPERTITEMS;

Prompt Creating public synonym CX_TYPES
CREATE PUBLIC SYNONYM CX_TYPES FOR &&OWNER..CX_TYPES;

Prompt Creating public synonym CX_SEVERITYS
CREATE PUBLIC SYNONYM CX_SEVERITYS FOR &&OWNER..CX_SEVERITYS;

Prompt Creating public synonym CX_SETRULES
CREATE PUBLIC SYNONYM CX_SETRULES FOR &&OWNER..CX_SETRULES;

Prompt Creating public synonym CX_RULESETS
CREATE PUBLIC SYNONYM CX_RULESETS FOR &&OWNER..CX_RULESETS;

Prompt Creating public synonym CX_METRICS
CREATE PUBLIC SYNONYM CX_METRICS FOR &&OWNER..CX_METRICS;

Prompt Creating public synonym CX_METRICRANGES
CREATE PUBLIC SYNONYM CX_METRICRANGES FOR &&OWNER..CX_METRICRANGES;

Prompt Creating public synonym CX_CATEGORYS
CREATE PUBLIC SYNONYM CX_CATEGORYS FOR &&OWNER..CX_CATEGORYS;

Prompt Creating public synonym CX_BASERULES
CREATE PUBLIC SYNONYM CX_BASERULES FOR &&OWNER..CX_BASERULES;




Prompt ============================================================================
Prompt Creating/Upgrading Data Generation objects
Prompt ============================================================================
Prompt Creating package spec TOAD_DATAGEN
@"C:\Program Files (x86)\Quest Software\Toad for Oracle 10.6\data_gen.pks"
Prompt Creating package body TOAD_DATAGEN
@"C:\Program Files (x86)\Quest Software\Toad for Oracle 10.6\data_gen.plb"
Prompt Granting EXECUTE on TOAD_DATAGEN to PUBLIC
GRANT EXECUTE ON &&OWNER..TOAD_DATAGEN TO PUBLIC;



Prompt Creating public synonym TOAD_DATAGEN
CREATE OR REPLACE PUBLIC SYNONYM TOAD_DATAGEN FOR &&OWNER..TOAD_DATAGEN;

Monitoring standby database made easy

we can use a simple trick to check if the standby database is up to date or not :

The trick is we are going to create a table and populate it every minute on primary server with a scheduled job using a  simple procedure.When we query the standby server we will see the table synced and if it is not the server is struck some where and has an issue.

Table DDL :


 CREATE TABLE "ORACLE"."DG_SYNC_STATUS"
   (    "TSTAMP" TIMESTAMP (6),
    "DB_UNIQUE_NAME" VARCHAR2(30 BYTE),
    "PRIMARY_SCN" NUMBER,
    "STANDBY_SCN" NUMBER,
    "PROTECTION_MODE" VARCHAR2(20 BYTE),
    "PROTECTION_LEVEL" VARCHAR2(20 BYTE)
   )  TABLESPACE "USERS" ;

 then we will create a simple procedure to populate the table every minute and this also will cleanup rows older than sysdate -3 so you don't have to worry about cleaning it up or space issues.

create or replace
PROCEDURE                                                        DG_SYNC_STATUS_CHECK_PRC
AS
BEGIN
  INSERT INTO DG_SYNC_STATUS (TSTAMP,DB_UNIQUE_NAME,PRIMARY_SCN,PROTECTION_MODE,PROTECTION_LEVEL)
  SELECT SYSTIMESTAMP, DB_UNIQUE_NAME, CURRENT_SCN, PROTECTION_MODE, PROTECTION_LEVEL
  FROM V$DATABASE ;

  DELETE FROM DG_SYNC_STATUS
  WHERE TSTAMP < SYSTIMESTAMP - 3;
  COMMIT;
END;


Once the table and procedure is created.schedule a job to execute this procedure to populate table every minute.here is sample screenshot for your scheduled job.

here is sample output of table being populated

That's it easy and simple way to check if the databases are in sync are not.Don't forget that you have to query the standby server to check if they are in sync or not.

Not a big solution but help full if you want to check daily.

ORA-00301: error in adding log file 'stdby02.log' - file cannot be created

SQL> alter database add standby logfile '/u02/oracle/oradata/stdby01.log' size 512M;

Database altered.

SQL> alter database add standby logfile '/u02/oracle/oradata/stdby02.log' size 512M;

Database altered.

SQL> alter database drop standby logfile '/u02/oracle/oradata/stdby01.log';

Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP#    THREAD#  SEQUENCE# ARC STATU
------ ---------- ---------- --- -----
     6          0          0 YES UNASS
                                 IGNED


SQL> alter database drop standby logfile '/u02/oracle/oradata//stdby02.log';

Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

no rows selected

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u02/oracle/oradata/stdby01.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u02/oracle/oradata/stdby01.log' size 512M
*
ERROR at line 1:
ORA-00301: error in adding log file '/u02/oracle/oradata/stdby01.log' - file cannot be created
ORA-27038: created file already exists
Additional information: 1


SQL> alter database add standby logfile '/u02/oracle/oradata/stdby03.log' size 512M;

Database altered.

SQL> alter database add standby logfile '/u02/oracle/oradata/stdby04.log' size 512M;

Database altered.