Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for the ‘Oracle10gR2’ Category

UDE-00008: operation generated ORACLE error 31626

Posted by Hendry chinnapparaj on June 12, 2012

Problem:- During Oracle Datapump export in oracle 10.2.0.4 encounters the errors below

. . exported “MZS_OWNER”.”READING”                       3.269 GB 12277747 rows

 

UDE-00008: operation generated ORACLE error 31626

ORA-31626: job does not exist

ORA-39086: cannot retrieve job information

ORA-06512: at “SYS.DBMS_DATAPUMP”, line 2772

ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3886

ORA-06512: at line 1

 

Solution:– it’s mentioned in Oracle MOS.

DataPump Export (EXPDP) Client Gets UDE-8 ORA-31626 ORA-39086 [ID 549781.1]

check the expdp logfile first, if it’s successfully completed as below, then no issue

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
H:\ORACLE\ORAEXP\MZSTWCP\DPDUMP\PROD_MZS_SCHEMAS_20120612.DMP
Job “SYS”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 14:12:39

——————–

However, reviewing the log file shows that the “job successfully completed”

Cause

This issue has been discussed in Bug 5969934 EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK

Solution

The expdp client makes calls to DBMS_DATAPUMP package to start and monitor export job. Once the export job is underway, the client just monitors the job status by issuing DBMS_DATAPUMP.GET_STAUS. Therefore, if the export logfile says “job successfully completed”, the dump file generated by the job should be fine.

You can simply ignore the errors, since the dump file is still valid for an import.

In the 10.2.0.2 release, there were a number of problems that caused the expdp and impdp clients to exit prematurely, interpreting a nonfatal error as a fatal one, giving the appearance that the job had failed when it hadn’t. In fact, inspection of the log file, if one was specified for the job, showed that the job ran successfully to completion. Often a trace file written by one of the Data Pump processes would provide more detail on the error that had been misinterpreted as a fatal one. Many of these errors involved the queues used for communication between the Data Pump processes, but there were other issues as well.

With each subsequent release, these problems have been addressed, and the client has become more robust and rarely, if ever, runs into situations like this. However, this is the result of many bug fixes in subsequent releases, some in Data Pump and some in supporting layers. It’s impossible to know, at this point, what combination of bug fixes would address this specific failure, and even if that was possible, it wouldn’t address other possible failures that look very similar on the client side.

Posted in Datapump10g | Leave a Comment »

Reorganize Audit Tables, indexes to release space

Posted by Hendry chinnapparaj on September 9, 2011

Solution:-

Assuming you have your audit table, indexes on a non-system tablespace and have already deleted the rows but the disk space is not released. The following is a guide to reorganize your audit table , indexes and release the disk space back to the system.

 

Table, indexes DDL extracted from TOAD Tool

DROP TABLE SYS.AUD$ CASCADE CONSTRAINTS;

 

CREATE TABLE SYS.AUD$

(

SESSIONID        NUMBER                       NOT NULL,

ENTRYID          NUMBER                       NOT NULL,

STATEMENT        NUMBER                       NOT NULL,

TIMESTAMP#       DATE,

USERID           VARCHAR2(30 BYTE),

USERHOST         VARCHAR2(128 BYTE),

TERMINAL         VARCHAR2(255 BYTE),

ACTION#          NUMBER                       NOT NULL,

RETURNCODE       NUMBER                       NOT NULL,

OBJ$CREATOR      VARCHAR2(30 BYTE),

OBJ$NAME         VARCHAR2(128 BYTE),

AUTH$PRIVILEGES  VARCHAR2(16 BYTE),

AUTH$GRANTEE     VARCHAR2(30 BYTE),

NEW$OWNER        VARCHAR2(30 BYTE),

NEW$NAME         VARCHAR2(128 BYTE),

SES$ACTIONS      VARCHAR2(19 BYTE),

SES$TID          NUMBER,

LOGOFF$LREAD     NUMBER,

LOGOFF$PREAD     NUMBER,

LOGOFF$LWRITE    NUMBER,

LOGOFF$DEAD      NUMBER,

LOGOFF$TIME      DATE,

COMMENT$TEXT     VARCHAR2(4000 BYTE),

CLIENTID         VARCHAR2(64 BYTE),

SPARE1           VARCHAR2(255 BYTE),

SPARE2           NUMBER,

OBJ$LABEL        RAW(255),

SES$LABEL        RAW(255),

PRIV$USED        NUMBER,

SESSIONCPU       NUMBER,

NTIMESTAMP#      TIMESTAMP(6),

PROXY$SID        NUMBER,

USER$GUID        VARCHAR2(32 BYTE),

INSTANCE#        NUMBER,

PROCESS#         VARCHAR2(16 BYTE),

XID              RAW(8),

AUDITID          VARCHAR2(64 BYTE),

SCN              NUMBER,

DBID             NUMBER,

SQLBIND          CLOB,

SQLTEXT          CLOB

)

LOB (SQLBIND) STORE AS (

TABLESPACE HD_AUDIT

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

LOB (SQLTEXT) STORE AS (

TABLESPACE HD_AUDIT

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

TABLESPACE HD_AUDIT

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

INITIAL          56K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

 

 

CREATE INDEX SYS.I_AUD2 ON SYS.AUD$

(SESSIONID, SES$TID)

LOGGING

TABLESPACE HD_AUDIT

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

INITIAL          56K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

NOPARALLEL;

 

 

GRANT SELECT ON SYS.AUD$ TO HD_MAINT;

 

 

 

DROP TABLE HD_MAINT.HD_AUD_HIST CASCADE CONSTRAINTS;

 

CREATE TABLE HD_MAINT.HD_AUD_HIST

(

SESSIONID        NUMBER                       NOT NULL,

ENTRYID          NUMBER                       NOT NULL,

STATEMENT        NUMBER                       NOT NULL,

TIMESTAMP#       DATE,

USERID           VARCHAR2(30 BYTE),

USERHOST         VARCHAR2(128 BYTE),

TERMINAL         VARCHAR2(255 BYTE),

ACTION#          NUMBER                       NOT NULL,

RETURNCODE       NUMBER                       NOT NULL,

OBJ$CREATOR      VARCHAR2(30 BYTE),

OBJ$NAME         VARCHAR2(128 BYTE),

AUTH$PRIVILEGES  VARCHAR2(16 BYTE),

AUTH$GRANTEE     VARCHAR2(30 BYTE),

NEW$OWNER        VARCHAR2(30 BYTE),

NEW$NAME         VARCHAR2(128 BYTE),

SES$ACTIONS      VARCHAR2(19 BYTE),

SES$TID          NUMBER,

LOGOFF$LREAD     NUMBER,

LOGOFF$PREAD     NUMBER,

LOGOFF$LWRITE    NUMBER,

LOGOFF$DEAD      NUMBER,

LOGOFF$TIME      DATE,

COMMENT$TEXT     VARCHAR2(4000 BYTE),

CLIENTID         VARCHAR2(64 BYTE),

SPARE1           VARCHAR2(255 BYTE),

SPARE2           NUMBER,

OBJ$LABEL        RAW(255),

SES$LABEL        RAW(255),

PRIV$USED        NUMBER,

SESSIONCPU       NUMBER,

NTIMESTAMP#      TIMESTAMP(6),

PROXY$SID        NUMBER,

USER$GUID        VARCHAR2(32 BYTE),

INSTANCE#        NUMBER,

PROCESS#         VARCHAR2(16 BYTE),

XID              RAW(8),

AUDITID          VARCHAR2(64 BYTE),

SCN              NUMBER,

DBID             NUMBER,

SQLBIND          CLOB,

SQLTEXT          CLOB

)

LOB (SQLBIND) STORE AS (

TABLESPACE HD_AUDIT_ARC

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT_ARC

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

LOB (SQLTEXT) STORE AS (

TABLESPACE HD_AUDIT_ARC

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT_ARC

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

TABLESPACE HD_AUDIT_ARC

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

 

 

 

The Objects are found in two Tablespaces

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

Time Start: 09/09/2011 7:13:03 PM

 

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE            BYTES    EXTENTS TABLESPACE_NAME

—————————— —————————— —————— ———- ———- ——————————

SYS                            AUD$                           TABLE              1693073408      15898 HD_AUDIT

HD_MAINT                       HD_AUD_HIST                    TABLE               858251264       8059 HD_AUDIT_ARC

SYS                            SYS_IL0000055838C00040$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            SYS_IL0000055838C00041$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            I_AUD2                         INDEX               948240384       8904 HD_AUDIT

HD_MAINT                       SYS_IL0000093888C00040$$       LOBINDEX               106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_IL0000093888C00041$$       LOBINDEX               106496          1 HD_AUDIT_ARC

SYS                            SYS_LOB0000055838C00040$$      LOBSEGMENT             106496          1 HD_AUDIT

SYS                            SYS_LOB0000055838C00041$$      LOBSEGMENT             106496          1 HD_AUDIT

HD_MAINT                       SYS_LOB0000093888C00040$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_LOB0000093888C00041$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

 

11 rows selected.

Time End: 09/09/2011 7:13:04 PM

Elapsed Time for Script Execution: 766 msecs

 

 

Shutdown the  primary and standby database

Login to primary database server hd_primary

Sqlas

Shutdown immediate

Login to hd_standby

Sqlas

Shutdown immediate

 

 

House Keep AUD$ Table

 

1.Set the database in restricted mode and make sure that no auditable users
are connected. If necessary kill these sessions.

Login as oracle in hd_primary

connect / as sysdba

startup restrict

alter system enable restricted session;

Verify the column LOGINS in V$INSTANCE.

 

LOGINS = RESTRICTED indicates that the database is in restricted mode.

Select logins from v$instance;

 

Check if sessions are still connected :

select sid, serial#, username from v$session;

If necessary kill these sessions with:

alter system kill session ‘sid , serial#’;

make a backup of current aud$, hd_aud_hist tables in the same tablespace

create table sys.aud$_backup tablespace hd_audit as select * from sys.aud$;

create table hd_main.hd_aud_hist_backup tablespace hd_audit_arc as select * from hd_maint.hd_aud_hist;

2. Move SYS.AUD$ to a different tablespace

select count(*) from SYS.AUD$;

 

create tablespace HD_AUD datafile ‘/u02/oradata/zoeoa1/hd_aud_01.dbf’ size 500m autoextend on;

alter table sys.aud$ move tablespace hd_aud;

Alter table sys.aud$ move lob (SQLBIND) STORE as (tablespace hd_aud);

Alter table sys.aud$ move lob (SQLTEXT) STORE as (tablespace hd_aud);

 

 

3

Rebuild the index / move to new tablespace

Alter index sys.i_aud2 rebuild tablespace hd_aud;

All extents are now optimally filled again.

 

————–

House Keep HD_AUD_HIST Table

Select count(*) from HD_MAINT.HD_AUD_HIST;

create tablespace HD_AUD_ARC datafile ‘/u02/oradata/zoeoa1/hd_aud_arc01.dbf’ size 500m autoextend on;

alter user hd_maint quota unlimited on HD_AUD_ARC;

 

alter table hd_maint.hd_aud_hist move tablespace hd_aud_arc;

Alter table hd_maint.hd_aud_hist move lob (SQLBIND) STORE as (tablespace hd_aud_arc);

Alter table hd_maint.hd_aud_hist move lob (SQLTEXT) STORE as (tablespace hd_aud_arc);

 

 

All extents are now optimally filled again.
Ensure the tablespace is empty except the backedup objects by running the script below before dropping the objects

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

Now drop the backedup objects

Drop table sys.aud$_backup purge;

Drop table hd_maint.hd_aud_hist_backup purge;

And finally drop the old tablespaces hd_audit and hd_audit_arc

 

Drop tablespace hd_audit including contents and datafiles ;

Drop tablespace hd_audit_arc including contents and datafiles;

 

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

no rows selected.

 

Restart the database in normal mode

Shutdown immediate

Startup

 

Startup zoeoa1 dr database

Login as oracle in hd_standby

Sqlas

Startup mount

check data guard broker and Log Gap

dgmgrl sys/******

show configuration

Expected result: success

 

Run the SC01 job from hd_primary

Log Gap=0

Also check alert log and ensure for no errors

Posted in DATABASE AUDIT | Leave a Comment »

SETUP Database Audit and House Keeping in Oracle10G

Posted by Hendry chinnapparaj on September 9, 2011

Database Audit Solution:- The following turns on auditing and places the AUD$ table in a non-system tablespace HD_AUDIT and also creates an index I_AUD2 on AUD$ table. The AUD$ table is archived to HD_AUD_HIST table and housekeeping is done on a weekly basis driven by Job scheduler.

 

Enable Auditing in Oracle Database and store the data in AUD$ Table

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE sid=’*’;

 

System altered.

 

SQL> SHUTDOWN

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

 

Total System Global Area  289406976 bytes

Fixed Size                  1248600 bytes

Variable Size              71303848 bytes

Database Buffers          213909504 bytes

Redo Buffers                2945024 bytes

Database mounted.

Database opened.

 

SQL>

SQL> show parameter audit_trail

 

NAME                                 TYPE        VALUE

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

audit_trail                          string      DB

 

Create the Tablespace to store the Audit History Table

Login as sys user

CREATE TABLESPACE HD_AUDIT_ARC DATAFILE

‘/u02/oradata/cpstest/HD_audit_arc01.dbf’ SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 500m

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 104K

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO;

 

 

 

Create HD_MAINT Database user and Grant Privileges

CREATE USER HD_MAINT

IDENTIFIED BY oracle10g

DEFAULT TABLESPACE HD_AUDIT_ARC

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

GRANT CONNECT TO HD_MAINT;

GRANT CREATE TABLE TO HD_MAINT;

GRANT ALTER SESSION TO HD_MAINT;

grant select on sys.aud$ to HD_MAINT;

grant select on dba_audit_session to HD_MAINT;

grant select on dba_audit_object to HD_MAINT;

ALTER USER HD_MAINT QUOTA UNLIMITED ON HD_AUDIT_ARC;

 

 

Create HD_AUD_HIST Table

 

alter session set current_schema =HD_MAINT;

CREATE TABLE HD_MAINT.HD_AUD_HIST AS SELECT * from sys.aud$ WHERE 1=2;

 

 

Create Procedure HD_AUD_MAINT_WEEKLY

 

alter session set current_schema =SYS;

CREATE OR REPLACE PROCEDURE SYS.HD_AUD_MAINT_WEEKLY AS

BEGIN

— REM INSERT WEEKLY RECORDS ALONE TO HD_AUD_HIST TABLE FROM AUD$

INSERT INTO HD_MAINT.HD_AUD_HIST SELECT * FROM SYS.AUD$ WHERE LOGOFF$TIME > (SELECT MAX(LOGOFF$TIME) FROM HD_MAINT.HD_AUD_HIST);

COMMIT;

— REM DELETE FROM AUD$ THE OLDER RECORDS AND LEAVE ONLY THE RECENT 60 DAYS RECORD

DELETE FROM SYS.AUD$ WHERE NTIMESTAMP# <= SYSDATE – 60;

commit;

— REM DELETE FROM HIST TABLE RECORDS WHICH ARE OLDER THAN 7 Months

DELETE FROM HD_MAINT.HD_AUD_HIST where NTIMESTAMP# < last_day(add_months(sysdate, -7));

commit;

end;

/

 

 

 

 

Insert the existing Aud$ Records to History Table

 

alter session set current_schema =SYS;

insert into HD_maint.HD_aud_hist select * from aud$;

 

Create Scheduled Job to run for every 15 Minutes

BEGIN

sys.dbms_scheduler.create_job(

job_name => ‘”SYS”.”HD_AUD_HK_JOB”‘,

job_type => ‘STORED_PROCEDURE’,

job_action => ‘”SYS”.”HD_AUD_MAINT_WEEKLY”‘,

repeat_interval => ‘FREQ=MINUTELY;INTERVAL=15’,

start_date => systimestamp at time zone ‘Australia/Perth’,

job_class => ‘”DEFAULT_JOB_CLASS”‘,

comments => ‘CPSOA1 Audit Tables House Keeping Job’,

auto_drop => FALSE,

enabled => FALSE);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘job_priority’, value => 4);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘max_run_duration’, value

=> numtodsinterval(60, ‘minute’));

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘raise_events’, value =>

dbms_scheduler.job_failed + dbms_scheduler.job_broken + dbms_scheduler.job_disabled);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘logging_level’, value =>

DBMS_SCHEDULER.LOGGING_FULL);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘instance_stickiness’, value

=> FALSE);

sys.dbms_scheduler.enable( ‘”SYS”.”HD_AUD_HK_JOB”‘ );

END;

/

 

 

Test if the Job was successful in OEM Grid Control

 

Monitor the Records retention Period in Audit Tables using the SQL’s

below

 

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

select max(LOGOFF$TIME) as max_logofftime from AUD$;

select min(LOGOFF$TIME) as min_logofftime from AUD$;

 

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

select max(LOGOFF$TIME) as max_logofftime from HD_MAINT.HD_AUD_HIST;

select min(LOGOFF$TIME) as min_logofftime from HD_MAINT.HD_AUD_HIST;

 

Amend the Scheduled Job to run once in a Week

BEGIN

sys.dbms_scheduler.disable( ‘”SYS”.”HD_AUD_HK_JOB”‘ );

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘repeat_interval’, value =>

‘FREQ=WEEKLY;BYDAY=SUN;BYHOUR=5;BYMINUTE=0;BYSECOND=0’);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘start_date’, value =>

systimestamp at time zone ‘Australia/Perth’);

sys.dbms_scheduler.enable( ‘”SYS”.”HD_AUD_HK_JOB”‘ );

END;

/

 

The following objects are created in the two tablespaces

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

 

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE            BYTES    EXTENTS TABLESPACE_NAME

—————————— —————————— —————— ———- ———- ——————————

SYS                            AUD$                           TABLE              1693073408      15898 HD_AUDIT

HD_MAINT                       HD_AUD_HIST                    TABLE               858251264       8059 HD_AUDIT_ARC

SYS                            SYS_IL0000055838C00040$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            SYS_IL0000055838C00041$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            I_AUD2                         INDEX               948240384       8904 HD_AUDIT

HD_MAINT                       SYS_IL0000093888C00040$$       LOBINDEX               106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_IL0000093888C00041$$       LOBINDEX               106496          1 HD_AUDIT_ARC

SYS                            SYS_LOB0000055838C00040$$      LOBSEGMENT             106496          1 HD_AUDIT

SYS                            SYS_LOB0000055838C00041$$      LOBSEGMENT             106496          1 HD_AUDIT

HD_MAINT                       SYS_LOB0000093888C00040$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_LOB0000093888C00041$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

 

Posted in DATABASE AUDIT | Leave a Comment »

Restore Database Validate for READ ONLY in Oracle10g

Posted by Hendry chinnapparaj on August 22, 2011

Problem

The standard syntax works only for oracle11g, not for oracle10g with regard to Restore Validation of READONLY Tablespaces. It actually skips the readonly tablespaces (validates only read  write)

RESTORE DATABASE VALIDATE FORCE;

Solution

This will validate the read write and then readonly tablespaces.

Run

{

RESTORE DATABASE VALIDATE FORCE;

RESTORE DATABASE VALIDATE FORCE CHECK READONLY;

}

 

 

Posted in RMAN10G | Leave a Comment »

ORA-01113: file 1 needs media recovery during open reset logs

Posted by Hendry chinnapparaj on May 16, 2011

Problem

———–

When you’re cloning a database, get the ora error below during open resetlogs

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: ‘E:\ORADATA\LN3D\SYSTEM01.DBF’

Solution

———

Copy the redolog from the source database where the cold backup was done to the target database redo directory

And then

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 23735596903 generated at 05/16/2011 14:35:45 needed for

thread 1

ORA-00289: suggestion : D:\ORACLE\ORA92\RDBMS\ARC52958.001

ORA-00280: change 23735596903 for thread 1 is in sequence #52958

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

d:\oracle\oradata\ln3d\redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

Posted in Clone or Duplicate | Leave a Comment »

Clone Oracle9i Database on same Windows Server

Posted by Hendry chinnapparaj on May 10, 2011

Summary :-

                      
The following steps must be on Source -Production instance/Server

Step 1. Shutdown database in normal mode and start it up in restricted mode.

                        Step 2. Take the backup of control file

                        Step 3. Shutdown database again  in normal mode.                    

Step 4. Copy init parameter file ,control file script  and all the database file on the destination server/location, once all the files are successfully copied, you may startup the database normally.

                        The following step must be on destination – Clone instance/server

                        Step 5. Edit init parameter file and control file script.

                        Step 6.  New Environment setup .

                        Step 7. Connect with sqlplus and recreate control file

                        Step 8. Open the database in resetlogs.

                        Step 9. Shutdown the database in normal mode

                        Step 10. Take the cold backup and start the database in archive/non archive mode.

Details :-


Step 1 – 3 on the Source Server ( Server A)

SVRMGR> connect internal/oracle

Connected.

SVRMGR> shutdown ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SVRMGR> startup restrict

ORACLE instance started.

Total System Global Area                         57124108 bytes

Fixed Size                                          70924 bytes

Variable Size                                    40198144 bytes

Database Buffers                                 16777216 bytes

Redo Buffers                                        77824 bytes

Database mounted.

Database opened.

SVRMGR> alter database backup controlfile to trace;

Statement processed.

SVRMGR>

SVRMGR> show parameter user_dump_dest

NAME                                TYPE    VALUE

———————————– ——- ————————-

user_dump_dest                      string E:\ora816\admin\ora816\udump

In Windows

D:\oracle\admin\ora816\udump

Note :- Backup control file will generate in user dump destination as above. Check for the latest Ora<xxxxx>.trc .  Rename this file to Ctrl.sql

 

SVRMGR> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Step 4: Copy all the parameter files (initsid.ora, configsid.ora ),  Control files,  Ctrlprod.sql ( created as above) and all data files  to clone server . It is good to follow the OFA. If you are  cloning a  database on the same machine then  create a different directory structure ( as OFA) for cloning instance and copied all the required files of primary instance here.

 

    Step 4a : On Unix  ( Let us say your cloning instance name is clone)

If you had followed OFA , your initsid.ora  parameters files would be in  $ORACLE_BASE/admin/clone/pfile/initclone.ora

and configsid.ora would be in  $ORACLE_BASE/admin/clone/pfile/configclone.ora.

Create a symbolic link

Change the working directory to $ORACLE_HOME/dbs and create a symbolic link

$ ln –s $ORACLE_BASE/admin/clone/pfile/initclone.ora  initclone.ora

 

Step 5 : On destination server

Change the following parameter in the initsid.ora of the cloning instance/server

 

InitSid.ora (initora816.ora) of the Source/production server.
InitSid.ora (initclone.ora) of the

destination/clone server.

db_name = “ora816”instance_name = ora816

service_names = ora816

control_files = (“e:\ora816\oradata\ora816\control01.ctl”, “f:\ora816\oradata\ora816\control02.ctl”, “g:\ora816\oradata\ora816\control03.ctl”)

Db_name= “clone”Instance_name= clone

Service_name = clone

Control_file = (“C:\clone\control01.ctl”,”D:\clone\control02.ctl”)

The other parameter which is required to change is user_dump_dest, background_dump_dest,log_archive_dest

 

 

Oraxxxx.trc is copied from source/production instance to destination/clone instance and renamed it as ctrlclone.sql .

 

 Edit ctrlclone.sql as follows.

 

Oraxxxx.trc in user dump dest of Source/Production instance Ctrlclone.sql
STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “ORA816” NORESETLOGS ARCHIVELOG

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 16

MAXLOGHISTORY 1815

LOGFILE

GROUP 1 ‘E:\ORA816\ORADATA\ORA816\REDO03.LOG’  SIZE 1M,

GROUP 2 ‘E:\ORA816\ORADATA\ORA816\REDO02.LOG’  SIZE 1M,

GROUP 3 ‘E:\ORA816\ORADATA\ORA816\REDO01.LOG’  SIZE 1M

DATAFILE

‘E:\ORA816\ORADATA\ORA816\SYSTEM01.DBF’,

‘E:\ORA816\ORADATA\ORA816\RBS01.DBF’,

‘E:\ORA816\ORADATA\ORA816\TEMP01.DBF’,

‘E:\ORA816\ORADATA\ORA816\TOOLS01.DBF’,

‘E:\ORA816\ORADATA\ORA816\INDX01.DBF’,

‘E:\ORA816\ORADATA\ORA816\DR01.DBF’,

‘E:\ORA816\DATABASE\NGAR5ORA81601.DBF’,

‘E:\ORA816\DATABASE\RBSTEST01.DBF’

CHARACTER SET WE8ISO8859P1

;

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

RECOVER DATABASE

# All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

# Database can now be opened normally.

ALTER DATABASE OPEN;

/* Delete everything upto startup nomount */STARTUP NOMOUNT pfile=f:\clone\initclone.ora

CREATE CONTROLFILE set DATABASE “CLONE” resetlogs noarchivelog

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 16

MAXLOGHISTORY 1815

LOGFILE

GROUP 1 ‘F:\CLONE\ORA816\REDO03.LOG’

SIZE 1M,

GROUP 2 ‘F:\CLONE\ORA816\REDO02.LOG’

SIZE 1M,

GROUP 3 ‘F:\CLONE\ORA816\REDO01.LOG’

SIZE 1M

DATAFILE

‘F:\CLONE\ORA816\SYSTEM01.DBF’,

‘F:\CLONE\ORA816\RBS01.DBF’,

‘F:\CLONE\ORA816\TEMP01.DBF’,

‘F:\CLONE\ORA816\TOOLS01.DBF’,

‘F:\CLONE\ORA816\INDX01.DBF’,

‘F:\CLONE\ORA816\DR01.DBF’,    ‘F:\CLONE\DATABASE\NGAR5ORA81601.DBF’,

‘F:\CLONE\DATABASE\RBSTEST01.DBF’

CHARACTER SET WE8ISO8859P1

;

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

#RECOVER DATABASE

# All logs need archiving and a log switch is needed.

#ALTER SYSTEM ARCHIVE LOG ALL;

# Database can now be opened normally.

#ALTER DATABASE OPEN;

STEP 6 :-

 

ON NT :- You have to create a windows NT services as follows.

 

C:\>oradim -new -sid clone -srvc oracleserviceClone -intpwd oracle -startmode auto -pfile f:\clone\initclone.ora

 

ON Unix :-

         

(A)   (A)    Change the working directory to /etc and edit the oratab file  to put the entry for CLOBE instance.

 (B)   Setup login profile for the Oracle user having dba group.

                        ORACLE_SID=CLONE

                         Export ORACLE_SID

         Or  .oraenv

               

           

 

 

STEP 7 :- Create controlfile as follows :-
 
Check in the initclone.ora REMOTE_LOGIN_PASSWORDFILE is set to
   exclusive or shared.  If this is set, then a valid passwordfile

   should exist in ORACLE_HOME/dbs or created using orapwd  as

 

orapwd file=d:\oracle\ora92\database\pwdclone.ora password=oracle entries=1 
 

  sqlplus /nolog

connect sys/** as sysdba

or

SVRMGR> @f:\clone\ctrlclone.sql

ORACLE instance started.

Total System Global Area                         57123804 bytes

Fixed Size                                          70620 bytes

Variable Size                                    40198144 bytes

Database Buffers                                 16777216 bytes

Redo Buffers                                        77824 bytes

Statement processed.

D:\ORACLE\ORA92\DATABASE

STEP 8 :- Open the database in resetlog mode as follows.

SVRMGR> alter database open resetlogs;

Statement processed.

STEP 9 :- Shutdown the database in Normal mode

SVRMGR> connect internal/oracle

Connected.

SVRMGR> shutdown ;

Database closed.

Database dismounted.

ORACLE instance shut down.

STEP 10 :- Take the cold backup and start the database in archive/non archive mode.

Posted in Clone or Duplicate | Leave a Comment »

REINSTATE STANDBY – DATAGUARD BROKER – ISSUES

Posted by Hendry chinnapparaj on March 1, 2011

REINSTATE STANDBY  – DATAGUARD BROKER – ISSUES

Problem:-

The Primary database / network is not available, so you did a Successful failover to Standby using Dataguard Broker – DR Site – is the PRIMARY NOW.

Then you try to reinstate the old primary as standby when you encounter errors

Errors

Dataguard error

ORA-16795: database resource guard detects that database re-creation is required

Primary alert log error

ORA-16009: remote archive log destination must be a STANDBY database

Tue Mar  1 11:38:11 2011

Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

LGWR: Failed to archive log 3 thread 1 sequence 9 (16009)

Standby alert log error

ORA-16009: remote archive log destination must be a STANDBY database

For failover:

DGMGRL> failover to zoeprd_js;

Performing failover NOW, please wait…

Failover succeeded, new primary is “zoeprd_js”

DGMGRL> failover to ‘ZOEPRD_js’;

Performing failover NOW, please wait…

Failover succeeded, new primary is “ZOEPRD_js”

DGMGRL> show configuration;

Configuration

Name:                ZOEPRD_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database (disabled)

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

SUCCESS

Then you try to reinstate original primary as standby

In primary server – after backup alive

DGMGRL> connect sys/*****

Connected.

DGMGRL> reinstate database ‘ZOEPRD_fc’

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> show configuration

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> enable configuration

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> show configuration

Configuration

Name:                ZOEPRD_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

Warning: ORA-16607: one or more databases have failed

New Primary Error in Alert log

Tue Mar  1 11:38:11 2011

LGWR: Error 16009 disconnecting from destination LOG_ARCHIVE_DEST_2                                  standby host ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOS                                 T=prdu009n2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ZOEPRD_fc_XPT)(                                 INSTANCE_NAME=zoeprd)(SERVER=dedicated)))’

LGWR: Error 16009 creating archivelog file ‘(DESCRIPTION=(ADDRESS_LI                                 ST=(ADDRESS=(PROTOCOL=tcp)(HOST=prdu009n2)(PORT=1521)))(CONNECT_DATA                                 =(SERVICE_NAME=ZOEPRD_fc_XPT)(INSTANCE_NAME=zoeprd)(SERVER=dedicated                                 )))’

Tue Mar  1 11:38:11 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_lgwr_3670184.tr                                 c:

ORA-16009: remote archive log destination must be a STANDBY database

Tue Mar  1 11:38:11 2011

Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

LGWR: Failed to archive log 3 thread 1 sequence 9 (16009)

New Standby  Error in Alert log

Redo Shipping Client Connected as PUBLIC

— Connected User is Valid

RFS[11]: Assigned to RFS process 9895980

RFS[11]: Database mount ID mismatch [0xa0162732:0xa015e92e]

RFS[11]: Not using real application clusters

Tue Mar  1 11:43:15 2011

Errors in file /u01/oracle/admin/zoeprd/udump/zoeprd_rfs_9895980.trc:

ORA-16009: remote archive log destination must be a STANDBY database

WORKAROUND Solution:-

Pre-requisite:- Flashback has to be ON for Primary & DR for this Solution to Work

Disable the Broker

DGMGRL> disable configuration

1.1.1.1        Steps as per oracle10g doc Step 4.2.10 – Establish Original Primary as Standby Using Flashback (Optional)

Once access to the failed production site is restored and if you had flashback database enabled, you can reinstate the original primary database as a physical standby of the new primary database.

On the standby site, note the SCN when the database became primary:

select to_char(standby_became_primary_scn) from v$database;

On the original production site, flashback and start managed recovery:

shutdown immediate;
startup mount;
flashback database to scn <standby_became_primary_scn>;
alter database convert to physical standby;
shutdown immediate;
startup mount;
alter database recover managed standby database using current logfile disconnect;

NEW PRIMARY SERVER

SQL> select to_char(standby_became_primary_scn) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)

—————————————-

29609886505

ORIGINAL PRIMARY –

shutdown immediate;

startup mount;

flashback database to scn 29609886505;

alter database convert to physical standby;

shutdown immediate;

startup mount;

NEW PRIMARY SERVER

DGMGRL> show configuration

Configuration

Name:                ZOEPRD_dgb

Enabled:             NO

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

DISABLED

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration

Name:                ZOEPRD_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

SUCCESS

SQL> select protection_mode from v$database;

PROTECTION_MODE

——————–

MAXIMUM PERFORMANCE

zoeprd:/u01/oracle> dgrmgrl sys/*****

ksh: dgrmgrl:  not found

zoeprd:/u01/oracle> dgmgrl sys/****

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Succeeded.

Check in primary & dr

SQL> select protection_mode from v$database;

PROTECTION_MODE

——————–

MAXIMUM AVAILABILITY

Posted in Data Guard and DR | Leave a Comment »

ORA-00328 archived log ends at change 29604569404, need later change 29605086441

Posted by Hendry chinnapparaj on February 21, 2011

ORA-00328 archived log ends at change 29604569404, need later change 29605086441

Problem:-

The Dataguard / standby database has these errors when the MRP0 – Recover session was initiated.

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session

Sun Feb 20 14:04:25 2011

Media Recovery Log /u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc

Errors with log /u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc

MRP0: Background Media Recovery terminated with error 328

Sun Feb 20 14:04:25 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_mrp0_790780.trc:

ORA-00328: archived log ends at change 29604569404, need later change 29605086441

ORA-00334: archived log: ‘/u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc’

Recovery interrupted!

Sun Feb 20 14:04:26 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_mrp0_790780.trc:

ORA-00328: archived log ends at change 29604569404, need later change 29605086441

ORA-00334: archived log: ‘/u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc’

Sun Feb 20 14:04:26 2011

MRP0: Background Media Recovery process shutdown (zoeprd)

Cause:-

The issue can be due to many reasons realting to archived logs and SCN having wrong meta entry / data.

Standby Oracle / control file is looking for a specific Archivelog / specific SCN which it thinks is required for Recovery which  is not available in the archived log. Somewhere in the log shipping / transfer

things got distorted inside the log causing issues / .

Even if you copy the logs from primary and register / replace the on standby and apply them – still the standby controlfile cannot recover them

Even if you recreate standby controlfile and recover, still comes up with same errors

So what do you do finally ? try this one as it worked fo rme

Fix:-

12.7 Using RMAN Incremental Backups to Roll Forward a Physical Standby Database

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC

In cases where a physical standby database is far behind the primary database, an RMAN incremental backup can be used to roll the standby database forward faster than redo log apply. In this procedure, the RMAN BACKUP INCREMENTAL FROM SCN command is used to create an incremental backup on the primary database that starts at the current SCN of the standby and is used to roll forward the standby database.

Note:

The steps in this section can also be used to resolve problems if a physical standby database has lost or corrupted archived redo data or has an unresolveable archive gap.

1.     On the standby database, stop the managed recovery process (MRP):

2.         SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3.     On the standby database, find the SCN which will be used for the incremental backup at the primary database:

4.         SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> column current_scn format 999999999999999
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

     CURRENT_SCN
----------------
     29615710642

5.     In RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step:

6.         RMAN> BACKUP INCREMENTAL FROM SCN <SCN from previous step>
7.         DATABASE FORMAT '/u03/oradata/backup/ForStandby_%U' tag 'FORSTANDBY';

Note:

RMAN does not consider the incremental backup as part of a backup strategy at the source database. Hence:

o        The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database

o        The backup is not cataloged at the source database

o        The backup sets produced by this command are written to the /dbs location by default, even if the flash recovery area or some other backup destination is defined as the default for disk backups.

o        You must create this incremental backup on disk for it to be useful. When you move the incremental backup to the standby database, you must catalog it at the standby as described in Oracle Database Backup and Recovery Advanced User’s Guide. Backups on tape cannot be cataloged.

Rman>

Run

{

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT  ‘/u03/oradata/backup/ForStandby_%U’;

      BACKUP INCREMENTAL FROM SCN 29615710642 DATABASE FORMAT '/u03/oradata/backup/ForStandby_%U' tag 'FORSTANDBY';

}

input datafile fno=00646 name=/u04/oradata/zoeprd/CB151_01.DBF

input datafile fno=00657 name=/u02/oradata/zoeprd/CBIDX156_01.DBF

input datafile fno=00668 name=/u02/oradata/zoeprd/CSH067_01.DBF

channel disk1: starting piece 1 at 20-02-2011 12:44:34

channel disk1: finished piece 1 at 20-02-2011 12:48:59

piece handle=/u03/oradata/backup/ForStandby_58m5501i_1_1 tag=FORSTANDBY comment=NONE

channel disk1: backup set complete, elapsed time: 00:04:25

Finished backup at 20-02-2011 12:48:59

Starting Control File and SPFILE Autobackup at 20-02-2011 12:49:00

piece handle=/u03/oradata/flash_recovery_area/ZOEPRD_FC/autobackup/2011_02_20/o1_mf_s_743604540_1FJwU-jRQ_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 20-02-2011 12:49:02

released channel: disk1

8.     Transfer all backup sets created on the primary system to the standby system (note that there may be more than one backup file created). For example:

9.         SCP /tmp/ForStandby_* standby:/tmp
 
ftp drpu009n1
/u03/oradata/backup /u03/oradata/backup

10. Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:

11.    RMAN> CATALOG START WITH '/tmp/ForStandby';

RMAN> catalog start with '/u03/oradata/backup';

searching for all files that match the pattern /u03/oradata/backup

List of Files Unknown to the Database
=====================================
File Name: /u03/oradata/backup/ForStandby_4sm54tbs_1_1
File Name: /u03/oradata/backup/ForStandby_4tm54tnb_1_1
File Name: /u03/oradata/backup/ForStandby_4um54u0u_1_1
File Name: /u03/oradata/backup/ForStandby_51m54ub5_1_1
File Name: /u03/oradata/backup/ForStandby_52m54ugm_1_1
File Name: /u03/oradata/backup/ForStandby_53m54uod_1_1
File Name: /u03/oradata/backup/ForStandby_54m54v05_1_1
File Name: /u03/oradata/backup/ForStandby_55m54v86_1_1
File Name: /u03/oradata/backup/ForStandby_56m54vft_1_1
File Name: /u03/oradata/backup/ForStandby_57m54vnu_1_1
File Name: /u03/oradata/backup/ForStandby_58m5501i_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/oradata/backup/ForStandby_4sm54tbs_1_1
File Name: /u03/oradata/backup/ForStandby_4tm54tnb_1_1
File Name: /u03/oradata/backup/ForStandby_4um54u0u_1_1
File Name: /u03/oradata/backup/ForStandby_51m54ub5_1_1
File Name: /u03/oradata/backup/ForStandby_52m54ugm_1_1
File Name: /u03/oradata/backup/ForStandby_53m54uod_1_1
File Name: /u03/oradata/backup/ForStandby_54m54v05_1_1
File Name: /u03/oradata/backup/ForStandby_55m54v86_1_1
File Name: /u03/oradata/backup/ForStandby_56m54vft_1_1
File Name: /u03/oradata/backup/ForStandby_57m54vnu_1_1
File Name: /u03/oradata/backup/ForStandby_58m5501i_1_1

12. Recover the standby database with the cataloged incremental backup:

13.    RMAN> RECOVER DATABASE NOREDO;

Generic Note

------------------------

Use RMAN> catalog backup piece and try the recovery. And when you do the recovery allocate the disk in RMAN as you have copied / ftp’ed these files to the disk

You might encounter these errors because the primary and standby archived logGs location are different. Either create the same directories as in primary or catalog these files

destination for restore of datafile 00647: /u02/oradata/zoeprd/CBIDX151_01.DBF
destination for restore of datafile 00658: /u04/oradata/zoeprd/CB157_01.DBF
channel ORA_DISK_2: reading from backup piece /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp
ORA-19870: error reading backup piece /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp
ORA-19505: failed to identify file "/u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp"
ORA-17503: ksfdopn:4 Failed to open file /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bk
failover to copy on device type SBT_TAPE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/20/2011 13:20:20
ORA-19870: error reading backup piece 3pm53j82_1_2
ORA-19507: failed to retrieve sequential file, handle="3pm53j82_1_2", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
   ANS1302E (RC2)    No objects on server match query

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 20-02-2011 13:25:01

using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_DISK_1
using channel ORA_DISK_2
datafile 269 not processed because file is read-only
datafile 270 not processed because file is read-only
datafile 271 not processed because file is read-only
…
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/20/2011 13:26:29
ORA-27191: sbtinfo2 returned error
Additional information: 2

The above error shows – recover is not done and skips the read only datafiles as the SCN are still the same

14. In RMAN, connect to the primary database and create a standby control file backup:

15.    RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

Run
{

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/u03/oradata/backup/sttbyctl.bck’;

backup current controlfile for standby format '/u03/oradata/backup/stbyctl.bck';
}

allocated channel: disk1
channel disk1: sid=634 devtype=DISK

Starting backup at 20-02-2011 13:42:19
channel disk1: starting compressed full datafile backupset
channel disk1: specifying datafile(s) in backupset
including standby control file in backupset
channel disk1: starting piece 1 at 20-02-2011 13:42:20
channel disk1: finished piece 1 at 20-02-2011 13:42:23
piece handle=/u03/oradata/backup/stbyctl.bck tag=TAG20110220T134219 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-02-2011 13:42:23

Starting Control File and SPFILE Autobackup at 20-02-2011 13:42:24
piece handle=/u03/oradata/flash_recovery_area/ZOEPRD_FC/autobackup/2011_02_20/o1_mf_s_743607761_1FJzUyGbV_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-02-2011 13:42:43
released channel: disk1

16. Copy the standby control file backup to the standby system. For example:

17.    SCP /tmp/ForStandbyCTRL.bck standby:/tmp

:: No such file or directory
ftp> lcd /u03/oradata/backup
Local directory now /u03/oradata/backup
ftp> cd /u03/oradata/backup
250 CWD command successful.
ftp> binary
200 Type set to I.
ftp> put stbyctl.bck
200 PORT command successful.
150 Opening data connection for stbyctl.bck.
226 Transfer complete.
3260416 bytes sent in 0.144 seconds (2.211e+04 Kbytes/s)
local: stbyctl.bck remote: stbyctl.bck

18. Shut down the standby database and startup nomount:

19.    RMAN> SHUTDOWN;
20.    RMAN> STARTUP NOMOUNT;

21. In RMAN, connect to standby database and restore the standby control file:

22.    RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

connected to target database: zoeprd (not mounted)
connected to recovery catalog database

RMAN> run
2> {
3> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u03/oradata/backup/sttbyctl.bck';
4> RESTORE STANDBY CONTROLFILE FROM '/u03/oradata/backup/stbyctl.bck';
5> }

allocated channel: disk1
channel disk1: sid=816 devtype=DISK

Starting restore at 20-02-2011 13:48:13

channel disk1: restoring control file
channel disk1: restore complete, elapsed time: 00:00:04
output filename=/u02/oradata/zoeprd/control01.ctl
output filename=/u03/oradata/zoeprd/control02.ctl
output filename=/u04/oradata/zoeprd/control03.ctl
Finished restore at 20-02-2011 13:48:22
released channel: disk1

23. Shut down the standby database and startup mount:

24.    RMAN> SHUTDOWN;
25.    RMAN> STARTUP MOUNT;

26. If the primary and standby database data file directories are identical, skip to step 13. If the primary and standby database data file directories are different, then in RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just-cataloged data files. For example:

27.    RMAN> CATALOG START WITH '+DATA_1/CHICAGO/DATAFILE/'; 
28.    RMAN> SWITCH DATABASE TO COPY;

29. If the primary and standby database redo log directories are identical, skip to step 14. Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths. For example, LOG_FILE_NAME_CONVERT='/BOSTON/','/CHICAGO/'.

30. On the standby database, clear all standby redo log groups (there may be more than 3):

31.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
32.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
33.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

SQL> column member format a50
SQL> l
  1* select * from v$logfile
SQL> /

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         9         STANDBY /u03/oradata/zoeprd/stdby_redo_09.log              NO
        10         STANDBY /u03/oradata/zoeprd/stdby_redo_10.log              NO
        11         STANDBY /u03/oradata/zoeprd/stdby_redo_11.log              NO
         4         ONLINE  /u02/oradata/zoeprd/redo_04a.log                   NO
         4         ONLINE  /u03/oradata/zoeprd/redo_04b.log                   NO
         5         ONLINE  /u02/oradata/zoeprd/redo_05a.log                   NO
         5         ONLINE  /u03/oradata/zoeprd/redo_05b.log                   NO
        12         STANDBY /u03/oradata/zoeprd/stdby_redo_12.log              NO
        13         STANDBY /u03/oradata/zoeprd/stdby_redo_13.log              NO
         6         ONLINE  /u02/oradata/zoeprd/redo_06a.log                   NO
         6         ONLINE  /u03/oradata/zoeprd/redo_06b.log                   NO
         7         ONLINE  /u02/oradata/zoeprd/redo_07a.log                   NO
         7         ONLINE  /u03/oradata/zoeprd/redo_07b.log                   NO

13 rows selected.

SQL> alter database clear logfile group 9;

Database altered.

SQL> alter database clear logfile group 10;

Database altered.

SQL> alter database clear logfile group 11;

Database altered.

SQL> alter database clear logfile group 12;

Database altered.

SQL> alter database clear logfile group 13;

Database altered.

34. On the standby database, restart Flashback Database:

35.    SQL> ALTER DATABASE FLASHBACK OFF; 
36.    SQL> ALTER DATABASE FLASHBACK ON;

37. On the standby database, restart MRP:

38.    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session;

Also recreate the block_change_tracking file if it’s used as it may be disabled.

Posted in Data Guard and DR | Leave a Comment »

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

Posted by Hendry chinnapparaj on February 21, 2011

 

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

Problem

———–

The following errors occurs in standby database – seen in oracle alert log

Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect

Sat Feb 19 08:19:13 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p000_1470606.trc:

ORA-00600: internal error code, arguments: [3020], [124], [214081], [520307777], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

ORA-10564: tablespace ZOE_LM_INDEX_1M

ORA-01110: data file 124: ‘/u02/oradata/zoeprd/ZOE_LM_INDEX_1M_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 65407

Sat Feb 19 08:19:13 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p002_757812.trc:

ORA-00600: internal error code, arguments: [3020], [2], [43149], [8431757], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 2, block# 43149)

ORA-10564: tablespace UNDOTBS1

ORA-01110: data file 2: ‘/u02/oradata/zoeprd/undotbs01.dbf’

ORA-10560: block type ‘KTU UNDO BLOCK’

Sat Feb 19 08:19:13 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p001_1200318.trc:

ORA-00600: internal error code, arguments: [3020], [466], [15128], [1954560792], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 466, block# 15128)

ORA-10564: tablespace DOCIDX021

ORA-01110: data file 466: ‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 139733

Sat Feb 19 08:19:15 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p000_1470606.trc:

ORA-00600: internal error code, arguments: [3020], [124], [214081], [520307777], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

ORA-10564: tablespace ZOE_LM_INDEX_1M

ORA-01110: data file 124: ‘/u02/oradata/zoeprd/ZOE_LM_INDEX_1M_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 65407

Sat Feb 19 08:19:15 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p002_757812.trc:

ORA-00600: internal error code, arguments: [3020], [2], [43149], [8431757], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 2, block# 43149)

ORA-10564: tablespace UNDOTBS1

ORA-01110: data file 2: ‘/u02/oradata/zoeprd/undotbs01.dbf’

ORA-10560: block type ‘KTU UNDO BLOCK’

Sat Feb 19 08:19:15 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p001_1200318.trc:

ORA-00600: internal error code, arguments: [3020], [466], [15128], [1954560792], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 466, block# 15128)

ORA-10564: tablespace DOCIDX021

ORA-01110: data file 466: ‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 139733

Sat Feb 19 08:19:16 2011

MRP0: Background Media Recovery terminated with error 12801

Sat Feb 19 08:19:16 2011

 

Cause:-

———

The Redo when shipped across network or somewhere along the way had some issues which caused the inconsistency, hence not able to syncup with the standby datafiles.

 

 

Fix:-

——–

 

Get the files from the primary and syncup the standby as below

 

There are 3 files affected

File numbers are 466, 124 and 2

1) You have to find out the tablespace name related to this datafiles

‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’ ==> File 466
‘/u02/oradata/zoeprd/ZOE_LM_INDEX_1M_01.DBF’ ==> File 124
‘/u02/oradata/zoeprd/undotbs01.dbf’ ===> File 2

From primary sql> select tablespace_name from dba_data_files where file_name = ‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’ ;
do the same for rest of 2 files also..

2) From primary sql> ALTER tablespace <tablespacename> begin backup;

3) Copy (SCP/FTP) the datafile file from PRIMARY to STANDBY

 

 

ftp <stby server name>

4) From primary sql> ALTER tablespace <tablespacename> end backup;

5) Start the recovery in standby
recover managed standby database using current logfile disconnect;
6) monitor the alert log if any issues

 

 

 

 

 

 

 

 

 

 

 

 

 

Posted in Data Guard and DR | 1 Comment »

Move Oracle10gR2 – 10.2.0.4.0 Database from Veritas File System to JFS2

Posted by Hendry chinnapparaj on January 24, 2011

Hi

I have tested this successfully, no issues

– Just shutdown the database residing in veritas file system 10.2.0.4.0 and aix 5.3

– ftp all the files including datafiles, redo, standby, pwd, initfile , spfile etc,. to the JFS2 – AIX 5.3

– just startup the database

– all should be fine and easy if the directories / mount points are the same on both servers.

Shalom

Posted in Admin | Leave a Comment »