Oracle: 2015

Wednesday, November 11, 2015

ORA-00245: control file backup operation failed during RMAN backup

DB: 11.2.0.4 2 node RAC

OS: RHEL5

When I’m trying to take FULL Db backup my backup failed and I see the below error message in the logfile

[oracle@oracledev]~/CB> nohup ./bkup.ksh &

released channel: ch2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup plus archivelog command at 08/21/2015 18:02:18

RMAN-03009: failure of backup command on ch2 channel at 08/21/2015 18:00:42

ORA-00245: control file backup operation failed

Recovery Manager complete.

This is the Error Specific to the RAC database, you don’t see this in NON-RAC database.

From 11.2.0.2, RMAN fails on RAC when doing controlfile autobackup which is set to DEFAULT (local server location)

Solution:

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name TEST are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/DBhome1R2/dbs/snapcf_TEST.f'; # default

From the above output we see my snapshot control file set to the default location, so we need to change to common directory for all the nodes, here I’m using ASM so I’ll set this location to ASM disk group

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/snapcf_TEST.f';

new RMAN configuration parameters:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/snapcf_TEST.f';

new RMAN configuration parameters are successfully stored

Reference:

ORA-245: In RAC environment from 11.2 onwards Backup Or Snapshot controlfile needs to be in shared location (Doc ID 1472171.1)

Thursday, October 22, 2015

ORA-39070: Unable to open the log file during EXPDP in RAC ASM

DB version: 11.2.0.4 RAC 2 node

Os version: RHEL 6

SQL> create directory EXP_DIR as '+FRADG/EXP';

Directory created.

SQL> grant read,write on directory EXP_DIR to sys;

Grant succeeded.

[oracle@oraclehost]$ expdp directory=EXP_DIR dumpfile=STDBY_FULL_metadata.dmp logfile=exp_STDBY_FULL_metadata.log full=y content=metadata_only

Export: Release 11.2.0.4.0 - Production on Sat Oct 10 23:39:03 2015

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 536

ORA-29283: invalid file operation

Solution:

You cannot put logfiles in asm directory so specify another directory for logfile file or use nologfile=y

Ex:

logfile= EXP: exp_STDBY_FULL_metadata.log à where ‘EXP’ is the directory in my local filesystem

(or)

Nologfile=y à This does not create any Logfile at all

Ref:

DataPump Export (EXPDP) To ASM Directory Fails With Errors ORA-6512 At SYS.UTL_FILE (Doc ID 1464897.1)

ORA-01466: unable to read data - table definition has changed

DB version: 11.2.0.4

OS: RHEL 6

My export command:

expdp directory=EXP_DIR dumpfile=SBX_STDBY _%U.dmp logfile=EXP:exp_SBX_STDBY.log schemas=SCHEMA_NAME flashback_scn=5424088950 parallel=4 reuse_dumpfiles=y cluster=N

ORA-31693: Table data object "SCHEMA"."TABLE_NAME" failed to load/unload and is being skipped due to error:

ORA-02354: error in exporting/importing data

ORA-01466: unable to read data - table definition has changed

Reason:

There is a DDL operation performed on this table causing the error.

Here i gave SCN number for an object before its LST_DDL_TIME

Ex:

SQL> select object_name,to_timestamp(last_ddl_time) from dba_objects where owner=’SCHEMA_NAME’ and object_name=’TABLE_NAME’;

OBJECT_NAME

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

TO_TIMESTAMP(LAST_DDL_TIME)

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

TDFM_EVT

15-OCT-15 12.00.00 AM

SQL> select SCN_TO_TIMESTAMP(5424088950) from dual;

SCN_TO_TIMESTAMP(5424088950)

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

15-OCT-15 08.44.33.000000000 PM

Solution:

Remove FLASHBACK_SCN parameter and rerun (or)

Specify SCN after the time “LST_DDL_TIME” and start again …..

Ref:

EXPDP - How to Solve "ORA-01466: unable to read data - table definition has changed" (Doc ID 1902481.1)

Saturday, September 5, 2015

OGG-01496 Failed to open target trail file

My pump process is down for so many days and when I tried to start the process it is getting ABENDED because it is trying to write in the remote trail file which is MISSING

GGSCI (oracleqa011.domain.com) 7> start PUMPQA

Sending START request to MANAGER ...
EXTRACT PUMPQA starting

GGSCI (oracleqa011.domain.com) 8> info PUMPQA

EXTRACT PUMPQA Last Started 2015-08-20 19:59 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:00:28 ago)
Log Read Checkpoint File ./dirdat/cb000011 --> This is source trail file
2015-08-20 19:59:30.000000 RBA 36679

GGSCI (oracleqa011.domain.com) 9> view report PUMPQA

2015-08-20 19:59:44 ERROR OGG-01496 Failed to open target trail file /u01/oracle/TARGET/cb000008, at RBA 8448365.

2015-08-20 19:59:44 ERROR OGG-01668 PROCESS ABENDING.

Here pump process is trying to write to the file “cb000008” where is has left before, since it’s been long ago this file doesn’t exist in the target location

Tried doing begin now command, but doesn’t work

GGSCI (oracleqa011.domain.com) 10> alter PUMPQA begin now

EXTRACT altered.

Still “ABENDED” --> because it will begin now but try to write to the same trail file

Solution:

Do “ETROLLOVER” for pump process, this will roll over to the next trail file in the sequence (here cb000009)

GGSCI (oracleqa011.domain.com) 10> ALTER EXTRACT PUMPQA ETROLLOVER

2015-08-20 20:02:22 INFO OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.

EXTRACT altered.

GGSCI (oracleqa011.domain.com) 11> start PUMPQA

Sending START request to MANAGER ...

EXTRACT PUMPQA starting

GGSCI (oracleqa011.domain.com) 12> info PUMPQA

EXTRACT PUMPQA Last Started 2015-08-20 20:02 Status RUNNING

Checkpoint Lag 00:03:09 (updated 00:00:08 ago)

Log Read Checkpoint File ./dirdat/cb000011

2015-08-20 19:59:30.000000 RBA 36679

In the target server I see “cb000009” has been created and writing into it.

ERROR OGG-01224 Address already in use.

DB version: 11.2.0.4 2-Node RAC

OS: RHEL 6

GG version : 11.2.1.0.3

Today when i'm trying to start my manager process it is not starting

GGSCI (oracledev01) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

JAGENT STOPPED

EXTRACT ABENDED EOECBP 291:56:23 27:02:50

GGSCI (oracledev01) 2> start mgr

Manager started.

But when I do info mgr it says manager is DOWN…..

GGSCI (oracledev01) 3> info mgr

Manager is DOWN!

GGSCI (oracledev01) 4> view params mgr

PORT 7809

DYNAMICPORTLIST 7840-9860

GGSCI (oracledev01) 5> view report mgr

***********************************************************************

Oracle GoldenGate Manager for Oracle

Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605

Linux, x64, 64bit (optimized), Oracle 11g on Oct 22 2013 07:38:49

Starting at 2015-08-11 19:56:22

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Fri May 29 10:16:43 EDT 2015, Release 2.6.32-504.23.4.el6.x86_64

Node: sl73orcdbdbq005

Machine: x86_64

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 36788

Parameters...

PORT 7809

DYNAMICPORTLIST 7840-9860

***********************************************************************

** Run Time Messages **

***********************************************************************

Source Context :

SourceModule : [mgr.main]

SourceID : [/scratch/aime1/adestore/views/aime1_adc4150267/oggcore/OpenSys/src/app/mgr/mgr.c]

SourceFunction : [init_functions]

SourceLine : [3390]

ThreadBacktrace : [8] elements

: [/gg/GG11/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7ff4595509fe]]

: [/gg/GG11/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2cc) [0x7ff45954974c]]

: [/gg/GG11/libgglog.so(_MSG_ERR_TCP_GENERIC(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x31) [0x7ff4595318a5]]

: [./mgr(init_functions(int, char**)+0x7f5) [0x4511c5]]

: [./mgr(main_loop(int, char**)+0x4c) [0x454aec]]

: [./mgr(main+0xf2) [0x455362]]

: [/lib64/libc.so.6(__libc_start_main+0xfd) [0x3d9361ed5d]]

: [./mgr(__gxx_personality_v0+0x142) [0x43efca]]

2015-08-11 19:56:22 ERROR OGG-01224 Address already in use.

2015-08-11 19:56:22 ERROR OGG-01668 PROCESS ABENDING.

Error in ggserr.log:

2015-08-11 19:56:10 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info mgr.

2015-08-11 19:56:19 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.

2015-08-11 19:56:22 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start mgr.

2015-08-11 19:56:22 ERROR OGG-01224 Oracle GoldenGate Manager for Oracle, mgr.prm: Address already in use.

2015-08-11 19:56:22 ERROR OGG-01668 Oracle GoldenGate Manager for Oracle, mgr.prm: PROCESS ABENDING.

CAUSE:

Previous mgr process is still running, which used port 7809 according to parameter file.

SOLUTION:

Use other port and start

To release the port perform below steps

As a root or Goldengate owner check the port

[root@oracledev01~]# netstat -nap | grep 7809

tcp 0 0 0.0.0.0:7809 0.0.0.0:* LISTEN 44402/./mgr

From the above output we see mgr is already running on port 7809 so kill this old process and start again

[root@oracledev01~]# kill -9 44402

[root@oracledev01~]# netstat -nap | grep 7809

Nothing displays

[oracle@oracledev01 GG11]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Oct 22 2013 11:03:39

GGSCI (oracledev01) 1> start mgr

Manager started.

GGSCI (oracledev01) 2> info mgr

Manager is running (IP port oracledev01.7809).

Friday, August 14, 2015

ENABLE automatic statistics collection in 11g.

SQL> SHO PARAMETER STATISTICS_LEVEL

NAME TYPE VALUE

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

statistics_level string TYPICAL

SQL> select client_name, status,attributes,service_name from dba_autotask_client;

CLIENT_NAME STATUS ATTRIBUTES SERVICE_NAME

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

auto optimizer stats collection DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL

auto space advisor ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL

sql tuning advisor ENABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL

SQL> select client_name,status from DBA_AUTOTASK_TASK;

CLIENT_NAME STATUS

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

sql tuning advisor ENABLED

auto space advisor ENABLED

SQL> SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS'),sql_tune_advisor, optimizer_stats, segment_advisor FROM DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME TO_CHAR(WINDOW_NEXT_TIME,'D SQL_TUNE OPTIMIZE SEGMENT_

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

MONDAY_WINDOW 17-AUG-15 22:00:00 ENABLED DISABLED ENABLED

TUESDAY_WINDOW 18-AUG-15 22:00:00 ENABLED DISABLED ENABLED

WEDNESDAY_WINDOW 19-AUG-15 22:00:00 ENABLED DISABLED ENABLED

THURSDAY_WINDOW 13-AUG-15 22:00:00 ENABLED DISABLED ENABLED

FRIDAY_WINDOW 14-AUG-15 22:00:00 ENABLED DISABLED ENABLED

SATURDAY_WINDOW 15-AUG-15 06:00:00 ENABLED DISABLED ENABLED

SUNDAY_WINDOW 16-AUG-15 06:00:00 ENABLED DISABLED ENABLED

7 rows selected.

SQL>

SQL> SELECT ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

ENABL

-----

TRUE

Check if this table has any pending stats

SQL> select LAST_ANALYZED,NUM_ROWS from dba_TAB_PENDING_STATS where TABLE_NAME='TEST_TAB';

no rows selected

Enable optimizer stats :

SQL> BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

client_name => 'auto optimizer stats collection',

operation => NULL,

window_name => NULL);

END;

PL/SQL procedure successfully completed.

SQL> SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS'),sql_tune_advisor, optimizer_stats, segment_advisor FROM DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME TO_CHAR(WINDOW_NEXT_TIME,'D SQL_TUNE OPTIMIZE SEGMENT_

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

MONDAY_WINDOW 17-AUG-15 22:00:00 ENABLED ENABLED ENABLED

TUESDAY_WINDOW 18-AUG-15 22:00:00 ENABLED ENABLED ENABLED

WEDNESDAY_WINDOW 19-AUG-15 22:00:00 ENABLED ENABLED ENABLED

THURSDAY_WINDOW 13-AUG-15 22:00:00 ENABLED ENABLED ENABLED

FRIDAY_WINDOW 14-AUG-15 22:00:00 ENABLED ENABLED ENABLED

SATURDAY_WINDOW 15-AUG-15 06:00:00 ENABLED ENABLED ENABLED

SUNDAY_WINDOW 16-AUG-15 06:00:00 ENABLED ENABLED ENABLED

7 rows selected.

Automatic Statistics Collection (Doc ID 1233203.1)

OGG-01224 Oracle GoldenGate Manager for Oracle, mgr.prm: Address already in use.

DB version: 11.2.0.4 2-Node RAC

OS: RHEL 6

GG version : 11.2.1.0.3

GGSCI (oracledev01) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

JAGENT STOPPED

EXTRACT ABENDED EOECBP 291:56:23 27:02:50

GGSCI (oracledev01) 2> start mgr

Manager started.

But when I do info mgr it says manager is DOWN…..

GGSCI (oracledev01) 3> info mgr

Manager is DOWN!

GGSCI (oracledev01) 4> view params mgr

PORT 7809

DYNAMICPORTLIST 7840-9860

GGSCI (oracledev01) 5> view report mgr

***********************************************************************

Oracle GoldenGate Manager for Oracle

Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605

Linux, x64, 64bit (optimized), Oracle 11g on Oct 22 2013 07:38:49

Starting at 2015-08-11 19:56:22

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Fri May 29 10:16:43 EDT 2015, Release 2.6.32-504.23.4.el6.x86_64

Node: sl73orcdbdbq005

Machine: x86_64

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 36788

Parameters...

PORT 7809

DYNAMICPORTLIST 7840-9860

***********************************************************************

** Run Time Messages **

***********************************************************************

Source Context :

SourceModule : [mgr.main]

SourceID : [/scratch/aime1/adestore/views/aime1_adc4150267/oggcore/OpenSys/src/app/mgr/mgr.c]

SourceFunction : [init_functions]

SourceLine : [3390]

ThreadBacktrace : [8] elements

: [/gg/GG11/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7ff4595509fe]]

: [/gg/GG11/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2cc) [0x7ff45954974c]]

: [/gg/GG11/libgglog.so(_MSG_ERR_TCP_GENERIC(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x31) [0x7ff4595318a5]]

: [./mgr(init_functions(int, char**)+0x7f5) [0x4511c5]]

: [./mgr(main_loop(int, char**)+0x4c) [0x454aec]]

: [./mgr(main+0xf2) [0x455362]]

: [/lib64/libc.so.6(__libc_start_main+0xfd) [0x3d9361ed5d]]

: [./mgr(__gxx_personality_v0+0x142) [0x43efca]]

2015-08-11 19:56:22 ERROR OGG-01224 Address already in use.

2015-08-11 19:56:22 ERROR OGG-01668 PROCESS ABENDING.

Error in ggserr.log:

2015-08-11 19:56:10 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info mgr.

2015-08-11 19:56:19 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.

2015-08-11 19:56:22 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start mgr.

2015-08-11 19:56:22 ERROR OGG-01224 Oracle GoldenGate Manager for Oracle, mgr.prm: Address already in use.

2015-08-11 19:56:22 ERROR OGG-01668 Oracle GoldenGate Manager for Oracle, mgr.prm: PROCESS ABENDING.

CAUSE:

Previous mgr process is still running, which used port 7809 according to parameter file.

SOLUTION:

Use other port and start

To release the port perform below steps

As a root or Goldengate owner check the port

[root@oracledev01~]# netstat -nap | grep 7809

tcp 0 0 0.0.0.0:7809 0.0.0.0:* LISTEN 44402/./mgr

From the above output we see mgr is running on port 7809 so kill this old process and start again

[root@oracledev01~]# kill -9 44402

[root@oracledev01~]# netstat -nap | grep 7809

[oracle@sl73orcdbdbq005 GG11]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Oct 22 2013 11:03:39

GGSCI (oracledev01) 1> start mgr

Manager started.

GGSCI (oracledev01) 2> info mgr

Manager is running (IP port oracledev01.7809).

Wednesday, August 5, 2015

ORA-04045: errors during recompilation/revalidation of GG_USER.DDLREPLICATION

DB version: 11.2.0.4

OS: RHEL 6

GG version : 11.2.1.0.3

For any DDL operation performed in the database I’m hitting the below error

EX:

SQL> ALTER TABLE USER.TEST MODIFY (emp_id VARCHAR2(100) );

ALTER TABLE USER.TEST MODIFY (emp_id VARCHAR2(100) )

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04045: errors during recompilation/revalidation of GGS_ADMIN.DDLREPLICATION

ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist