Configure Databse to Run in ARCHIVELOG Mode

Part I)

First of  all you must configure the following parameters related to database archiving:

You have two main options for define archive log destinations:

1) Flash Recovery Area (recommended)

In this part, first of all you must be set the located size of the FRA:

SQl> Alter system set DB_RECOVERY_FILE_DEST_SIZE=2G scope=both;

Note:

DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.

then specifies the location of the FRA, in this part you have three options for define the location

• Locally attached storage

SQL> alter system set DB_RECOVERY_FILE_DEST='/opt01/app/oracle/flash_recovery_area' scope=both;

• Oracle's Cluster File System (OCFS)


• Automatic Storage management (ASM)

SQL> alter system set DB_RECOVERY_FILE_DEST='+DATA' scope=both;

(DATA = ASM Disk volume)

Note: In a RAC database, all instances must have the same values for these parameters(DB_RECOVERY_FILE_DEST, DB_RECOVERY_FILE_DEST_SIZE).

2) ARCHIVELOG Destination Directories

• Directories

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/opt02/app/oracle/second_recovery_area mandatory' scope=both;

Note: If you want to use directories and FRA at the same time you must set  LOG_ARCHIVE_DEST_10 parameter as following command:

SQL> alter system set LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

• Network Directories

SQL> alter system set LOG_ARCHIVE_DEST_2='LOCATION=/opt03/mnt_drive_z optional' scope=both;

• Database Service Name (Standby Database/Data guard)

SQL> alter system set LOG_ARCHIVE_DEST_3='SERVICE=standby_db mandatory' scope=both;

Note: You can define up to 10 different archive log destinations and each location as a mandatory or optional location.

The other important parameters are:

LOG_ARCHIVE_MIN_SUCCEED_DEST

SQL> alter system set LOG_ARCHIVE_MIN_SUCCEED_DEST=2 scope=both;

Note: This parameter defines the minimum number of destinations that the log file must be copied to before it can be overwritten. This value should be greater than or equal to the number of MANDATORY destinations in LOG_ARCHIVE_DEST_n.

LOG_ARCHIVE_FORMAT

SQL> alter system set LOG_ARCHIVE_FORMAT ='LOG%s_%t_%r.ARC' scope=both;

Part II)

SQL> connect / as sysdba

Part III)

SQL> shutdown immediate

Part IV)

SQL> startup mount

Part V)

SQL>alter database archivelog;

Part VI)

SQL> alter database open;

Part VII)

To display the status of archiving:

SQL> select log_mode from v$database;

Or

SQL> archive log list;

Generate Workload CPU statistics using dbms_stats.gather_system_stats

In Oracle 9i and up, you can generate CPU cost information during a workload with a procedure of the dbms_stats package.



The dbms_stats.gather_system_stats packaged procedure can be used during a certain interval to measure the actual I/O and CPU usage during a workload, typically a days work.

SQL> exec dbms_stats.gather_system_stats('START')

PL/SQL procedure successfully completed.

SQL> -- days processing
SQL> exec dbms_stats.gather_system_stats('STOP')

PL/SQL procedure successfully completed.

Now, when you gathered workload CPU statistics, one can query the sys.aux_stats$ data dictionary table to see the actual values that will be used when generating your Sql plan:

select sname, pname, pval1
from sys.aux_stats$;

SNAME       	PNAME      PVAL1
-------------	---------  -------
SYSSTATS_INFO   STATUS
SYSSTATS_INFO   DSTART
SYSSTATS_INFO   DSTOP
SYSSTATS_INFO   FLAGS	    1
SYSSTATS_MAIN   CPUSPEEDNW  502.005
SYSSTATS_MAIN   IOSEEKTIM   10
SYSSTATS_MAIN   IOTFRSPEED  4096
SYSSTATS_MAIN   SREADTIM    7.618
SYSSTATS_MAIN   MREADTIM    14.348
SYSSTATS_MAIN   CPUSPEED    507
SYSSTATS_MAIN   MBRC 	    6
SYSSTATS_MAIN 	MAXTHR      32768
SYSSTATS_MAIN 	SLAVETHR      

13 rows selected.

CPUSPEEDNW, IOSEEKTIM, and IOTFRSPEED are noworkload statistics;

SREADTIM, MREADTIM, CPUSPEED, MBRC, MAXTHR, and SLAVETHR represent workload statistics.



When you have both workload and noworkload statistics, the optimizer will use workload statistics.

• SREADTIM - single block read time (msec): the average time Oracle takes to read a single block


• MREADTIM - multiblock read time (msec): the average time taken to read sequentially


• MBRC - multiblock read count: the average amount of blocks read during multiblock sequential reads. This value is used instead of the db_ multiblock_read_count parameter during query optimization to compute costs for table and fast full index scans


• MAXTHR - maximum I/O system throughput: is captured only if the database runs parallel queries


• SLAVETHR - maximum slave I/O throughput: is captured only if the database runs parallel queries

ORA-02292: integrity constraint (%s.%s) violated - child record

You are trying to delete a record, but one or more child records were found, preventing the delete.
A (parent / child) relationship between 2 tables are defined by a foreign key constraint.
eg:

CREATE TABLE PARENT 
(
ID NUMBER PRIMARY KEY
);

Table created.

CREATE TABLE CHILD
(
ID NUMBER PRIMARY KEY,
P_ID NUMBER REFERENCES PARENT(ID)
);

Table created.

SQL> insert into parent values(1);

1 row created.

SQL> insert into parent values(2);

1 row created.

SQL> insert into child values(10, 1);

1 row created.

SQL> insert into child values(11, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete from parent where id=2;

1 row deleted.

SQL> delete from parent where id=1;
delete from parent where id=1
*
ERROR at line 1:
ORA-02292: integrity constraint (MYOWNER.SYS_C008616) violated - child record
found

We could delete parent record 2 because there were no child records for this record.
Parent record 1 however has 2 child records, preventing the record from being deleted.

You can query the all_cons_columns view to see what child columns matches the parent columns:

select parentcols.owner||'.'||parent.table_name||'.'||parentcols.column_name||' matches to '||
       childcols.owner||'.'||child.table_name||'.'||childcols.column_name
from
  all_cons_columns childcols
, all_cons_columns parentcols
, all_constraints child
, all_constraints parent
where childcols.owner='MYOWNER' and childcols.constraint_name='SYS_C008616'
and childcols.owner=child.owner
and childcols.constraint_name=child.constraint_name
and child.r_owner=parent.owner
and child.r_constraint_name=parent.constraint_name
and parent.owner=parentcols.owner
and parent.constraint_name=parentcols.constraint_name
and childcols.position=parentcols.position
order by childcols.position;


MYOWNER.PARENT.ID matches to MYOWNER.CHILD.P_ID

Solution is to first delete the child records before deleting the parent record, or to specify the on cascade delete option on the foreign key:

SQL> drop table child;

Table dropped.

CREATE TABLE CHILD
(
ID NUMBER PRIMARY KEY,
P_ID NUMBER REFERENCES PARENT(ID) on delete cascade
);

Table created.


SQL> insert into child values(10, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete from parent where id=1;

1 row deleted.

SQL> select * from child;

no rows selected

How to Safely Remove a Data Guard Broker Configuration

How to Safely Remove a Data Guard Broker Configuration
	Doc ID:	261336.1	Type:	REFERENCE
	Modified Date:	20-MAY-2009	Status:	PUBLISHED

PURPOSE
-------

How to safely remove a Data Guard Broker configuration.

SCOPE & APPLICATION
-------------------

This applies to all DBAs who wish to remove and desactive a
Data Guard Broker configuration.

Removing the Configuration
----------------------------

1) Remove the Data Guard Broker Configuration

a) Using the Commandline DGMGRL:

- Connect to the Primary Database
DGMGRL> connect sys/@
- Remove the Data Guard Broker Configuration
DGMGRL> remove configuration;

b) Using Data Guard Manager or Grid Control:

Follow the Wizard as mentioned in the Chapter
'Scenarios Using Oracle Enterprise Manager'
of the Data Guard Broker Guide of each Database Version.

2) On the primary database set dg_broker_start=false:

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      ?/dbs/dr1@.dat
dg_broker_config_file2               string      ?/dbs/dr2@.dat
dg_broker_start                      boolean     TRUE
SQL>
SQL> alter system set dg_broker_start=false scope=both;

System altered.

SQL>  show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      ?/dbs/dr1@.dat
dg_broker_config_file2               string      ?/dbs/dr2@.dat
dg_broker_start                      boolean     FALSE
SQL>

3) On the primary disable any archive destinations that are archiving to the standby:

SQL> select dest_id,destination,status
2  from v$archive_dest
3  where target='STANDBY';

DEST_ID
----------
DESTINATION
--------------------------------------------------------------------------------
STATUS
---------
2
(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=hasunclu2.us.oracle.co
m)(PORT=1521)))(CONNECT_DATA=(SID=DG920phy)(ORACLE_HOME=/u03/oracle/9.2.0)(SERVE
R=DEDICATED)))
VALID

SQL> alter system set log_archive_dest_state_2=defer scope=both;

System altered.

SQL>

4) on the standby set the dg_broker_start parameter to false:

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      ?/dbs/dr1@.dat
dg_broker_config_file2               string      ?/dbs/dr2@.dat
dg_broker_start                      boolean     TRUE
SQL> alter system set dg_broker_start=false scope=both;

System altered.

SQL>

5) On both system rename the metadata files (if they exist):

oracle@hasunclu1:/u03/oracle/9.2.0/dbs> mv dr1DG920.dat dr1DG920.back
oracle@hasunclu1:/u03/oracle/9.2.0/dbs> mv dr2DG920.dat dr2DG920.back

RELATED DOCUMENTS
--------------------

Oracle® Data Guard Broker Guide

Finding specific data in Oracle Tables

Imagine, there are a few tables in your schema and you want to find a specific value in all columns within these tables. Ideally, there would be an sql function like

select * from * where any(column) = 'value';

Unfortunately, there is no such function.

However, a PL/SQL function can be written that does that. The following function iterates over all character columns in all tables of the current schema and tries to find val in them.

create or replace function find_in_schema(val varchar2) 
return varchar2 is
  v_old_table user_tab_columns.table_name%type;
  v_where     Varchar2(4000);
  v_first_col boolean := true;
  type rc     is ref cursor;
  c           rc;
  v_rowid     varchar2(20);

begin
  for r in (
    select
      t.*
    from
      user_tab_cols t, user_all_tables a
    where t.table_name = a.table_name
      and t.data_type like '%CHAR%'
    order by t.table_name) loop
 
    if v_old_table is null then
      v_old_table := r.table_name;
    end if;
 
    if v_old_table <> r.table_name then
      v_first_col := true;
 
      -- dbms_output.put_line('searching ' || v_old_table);
 
      open c for 'select rowid from "' || v_old_table || '" ' || v_where;
 
      fetch c into v_rowid;
      loop
        exit when c%notfound;
        dbms_output.put_line('  rowid: ' || v_rowid || ' in ' || v_old_table);
        fetch c into v_rowid;
      end loop;
 
      v_old_table := r.table_name;
    end if;
 
    if v_first_col then
      v_where := ' where ' || r.column_name || ' like ''%' || val || '%''';
      v_first_col := false;
    else
      v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''';
    end if;
 
  end loop;
  return 'Success';
end;
/

The function in action

Let's see the function in action. First, some tables are created:

create table test_find_1 (
  a number,
  b varchar2(10),
  c varchar2(20),
  d varchar2(30)
);

create table test_find_2 (
  e number,
  f varchar2(30),
  g varchar2(20),
  h varchar2(10)
);

create table test_find_3 (
  i number,
  j varchar2(15),
  k varchar2(15),
  l varchar2(15)
);

Then, the tables are filled:

insert into test_find_1 values (1, 'Orange' , 'Grape'     , 'Papaya' );
insert into test_find_1 values (2, 'Apple'  , 'Pear'      , 'Coconut');
insert into test_find_1 values (3, 'Mango'  , 'Lime'      , 'Banana' );

insert into test_find_2 values (1, 'Apricot', 'Kiwi'      , 'Lemon'  );
insert into test_find_2 values (2, 'Peach'  , 'Dates'     , 'Pear'   );
insert into test_find_2 values (3, 'Lime'   , 'Mango'     , 'Grape'  );

insert into test_find_3 values (1, 'Papaya' , 'Banana'    , 'Mango'  );
insert into test_find_3 values (2, 'Lime'   , 'Plum'      , 'Cherry' );
insert into test_find_3 values (3, 'Rhubarb', 'Pineapple' , 'Carrot' );

The function uses dbms_output. Therefore, we need to enable serveroutput in SQL*Plus.

set serveroutput on size 1000000 format wrapped

Executing the function:

select find_in_schema('Pear') from dual;

The output:

  rowid: AAACQNAAEAAAAHCAAB in TEST_FIND_1
  rowid: AAACQOAAEAAAAHKAAB in TEST_FIND_2

Now, these rowids can be used to find the rows:

select * from test_find_1 where rowid = 'AAACQNAAEAAAAHCAAB';
select * from test_find_2 where rowid = 'AAACQOAAEAAAAHKAAB';

Thanks

Thanks to Steve Stowers who found a bug and improved the code otherwise.