ON CALL DBA SUPPORT

— Database blog

WARNING OGG-00706 Failed to add supplemental log group on table

Posted by ssgottik on 16/12/2015

GGSCI (nracm1 as gguser@source) 28> add trandata scott.account

Logging of supplemental redo log data is already enabled for table scott.ACCOUNT.

2015-12-16 04:06:58 WARNING OGG-00706 Failed to add supplemental log group on table scott.ACCOUNT due to ORA-32588: supplemental logging attribute primary key exists SQL ALTER TABLE “scott”.”ACCOUNT” ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS /* GOLDENGATE_DDL_REPLICATION */.

SOLUTION :

GGSCI (nracm1 as gguser@source) 29> delete trandata scott.account

Logging of supplemental redo log data disabled for table scott.ACCOUNT.
TRANDATA for scheduling columns has been disabled on table ‘scott.ACCOUNT’.
GGSCI (nracm1 as gguser@source) 30> add trandata scott.account

Logging of supplemental redo data enabled for table scott.ACCOUNT.
TRANDATA for scheduling columns has been added on table ‘scott.ACCOUNT’.
GGSCI (nracm1 as gguser@source) 31>

Posted in ORACLE GOLDEN GATE | Leave a Comment »

INSTALLING ORACLE 12C GOLDEN GATE

Posted by ssgottik on 16/12/2015

1. UNZIP THE SOFTWARE

unzip gg software

2. GO TO Disk1

cd Disk1

3. RUN runInstaller

runInstaller

4. You will get GUI, you can install GG 12 BOTH ON 11G DATABASE AND 12C DATABASE :

STEP01

5. CLICK NEXT BY SELECTING YOUR DATABASE , HERE YOU NEED TO SPECIFY THE GG HOME LOCATION AND THE MANAGER PORT :

STEP02

6. CLICK NEXT :

STEP03

7. CLICK NEXT :

STEP04

8. INSTALLATION COMPLETE , CLOSE THE WINDOW:

STEP05

Posted in ORACLE GOLDEN GATE | Leave a Comment »

Configuring ASM failed with the following message: ORA-00845: MEMORY_TARGET not supported on this system

Posted by ssgottik on 13/12/2015

ERROR DURING INSTALLATION :
Configuring ASM failed with the following message:
Configuring HA resource failed. The following error occurred:
PRCR-1079 : Failed to start resource ora.asm
CRS-5017: The resource action “ora.asm start” encountered the following error:
ORA-00845: MEMORY_TARGET not supported on this system
. For details refer to “(:CLSN00107:)” in “/u01/app/grid/diag/crs/nracm1/crs/trace/ohasd_oraagent_grid.trc”.

CRS-2674: Start of ‘ora.asm’ on ‘nracm1’ failed

SOLUTION :

Starting with Oracle Database 12c, If MEMORY_MAX_TARGET or MEMORY_TARGET is set to a non zero value, and an incorrect size is assigned to the shared memory, it will result in an ORA-00845 error at startup.

Increase the size of MEMORY_TARGET and RESTART THE INSTALLATION.

Thanks

Posted in ASM | Leave a Comment »

Oracle Grid Infrastructure is not configured properly. ASMCA needs Oracle Grid Infrastructure to configure ASM.

Posted by ssgottik on 28/08/2015

CaptureSOLUTION:

export ORACLE_HOME=/u01/app/11.2.0/grid_1

$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/crs/install $ORACLE_HOME/crs/install/roothas.pl

Posted in ASM | Leave a Comment »

CLUVFY HEALTHCHECK

Posted by ssgottik on 24/07/2015

From oracle 11g R2 we have a new feature in oracle cluvfy , It will generate the HEALTHCHECK report in html format FOR RAC .

$mkdir -p /tmp/clu_tmp

$cluvfy comp healthcheck -collect cluster -html -save -savedir /tmp/clu_tmp

 

Posted in RAC | Leave a Comment »

ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode(During RMAN COLD BACKUP)

Posted by ssgottik on 17/06/2015

RMAN-03009: failure of backup command on c3 channel at 05/15/2012 18:49:11
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel c4: starting compressed full datafile backupset
channel c4: specifying datafile(s) in backupset

There is at least one file that is in need of recovery.
A COLD backup for a database running in NOARCHIVELOG mode has to be a CONSISTENT backup as it cannot be recovered after restore.

To confirm, mount the database and run the following SQL:

SQL >select count(1),fuzzy from v$datafile_header group by fuzzy;

COUNT(1) FUZ
——————– —
92 YES
SQL>Recover database ;

SQL >select count(1),fuzzy from v$datafile_header group by fuzzy;

COUNT(1) FUZ
——————– —
92 NO

 

Now take the backup of the database (RMAN COLD BACKUP)

 

Reference : Doc ID 373828.1

Posted in ADMINISTRATION | Leave a Comment »

CATPROC Oracle Database Packages and Types INVALID After upgrade

Posted by ssgottik on 17/06/2015

STEP 01:

For validating the catproc the following steps needs to be executed:

Step 01 a  –
SQL> shutdown immediate;

Step 01 b –
SQL> startup upgrade;

Step 01 c  –

conn / as sysdba
spool catalogproc.out
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/utlrp.sql
Column comp_id format a10
Column comp_name format a35
Column version format a12
Column status format a10
select comp_id, comp_name, version, status from dba_registry;
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE STATUS<>’VALID’ ORDER BY OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS;
spool off

Step 01 d –
SQL> shutdown immediate;
SQL> startup;

STEP 02:

If step 01 doesn’t works :

Reference note:782735.1

Execute the following script repeatedly while connected as SYS AS SYSDBA and perform the indicated operation until it returns ‘CATPROC can be validated now’.

REM ***************
REM CHECKVALID.SQL
REM ***************
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
cid = ‘CATPROC’;
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line(‘Please compile Invalid object ‘||object_name||’
Object_id ‘||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘CATPROC can be validated now’ );
end;
/

Once all of the dependent objects have been recompiled, validate CATPROC by executing the following command:
execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

In my case even after running this CATPROC did not become VALID . So when I ran the below query

column object_name format a25

SQL > select object_name, object_type, owner from dba_objects
where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
—————————— ——————- ——————————
PLAN_TABLE                         TABLE                                            SYS
PLAN_TABLE                         SYNONYM                                     PUBLIC
TOAD_PLAN_TABLE          TABLE                                             DORIS
BMC_SQLEX_PLAN_TABLE_S02   TABLE                            SYSTEM
PLAN_TABLE                        TABLE                                             SYSTEM
SQL_PLAN_TABLE_TYPE   TYPE                                             SYS
SQL_PLAN_TABLE_TYPE   SYNONYM                                  PUBLIC
PLAN_TABLE$                           TABLE                                      SYS
TOAD_PLAN_TABLE             SYNONYM                                 PUBLIC

 

This issue is caused by existence of :
– table SYS.PLAN_TABLE$
– public synonym PLAN_TABLE for the SYS.PLAN_TABLE$.
– and possibly (not always present) table SYS.PLAN_TABLE

In order to prevent the issue from occurring it is necessary to drop before starting the upgrade process. :
– the SYS owned table PLAN_TABLE$
– the public synonym PLAN_TABLE
– the SYS owned table PLAN_TABLE if it exists

As the SYS user, drop the PLAN_TABLE$, the public synonym PLAN_TABLE and if it exists
the table PLAN_TABLE in SYS schema (SYS.PLAN_TABLE is not always there) :

If you receive the following error while attempting to drop the plan table:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01008: not all variables bound
ORA-06512: at line 21

ensure that the cursor_sharing parameter is set to EXACT.

This can be done at the session level using ‘alter system set cursor_sharing=exact scope=memory;’
SQL> drop table plan_table$;

Table dropped.

SQL> drop public synonym plan_table;

Synonym dropped.

if SYS.PLAN_TABLE exists :

drop table plan_table;

SQL> column object_name format a25
SQL> select object_name, object_type, owner
2 from dba_objects
3 where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
————————- ——————- ——————————
SQL_PLAN_TABLE_TYPE TYPE SYS
SQL_PLAN_TABLE_TYPE SYNONYM PUBLIC
EXF$PLAN_TABLE TABLE EXFSYS

Then re-create the PLAN_TABLE
$ sqlplus ‘/ as sysdba’
SQL> @?/rdbms/admin/catplan.sql — creates a public plan table as a global temporary table accessible from any schema
SQL> @?/rdbms/admin/dbmsxpln.sql — reload dbms_xplan spec
SQL> @?/rdbms/admin/prvtxpln.plb — reload dbms_xplan implementation
SQL> @?/rdbms/admin/prvtspa.plb — reload dbms_sqlpa (Note use prvtspao.plb if prvtspa.plb is not present).
SQL> column object_name format a25
SQL> select object_name, object_type, owner
2 from dba_objects
3 where object_name like ‘%PLAN_TABLE%’;

OBJECT_NAME OBJECT_TYPE OWNER
————————- ——————- ——————————
SQL_PLAN_TABLE_TYPE TYPE SYS
SQL_PLAN_TABLE_TYPE SYNONYM PUBLIC
PLAN_TABLE SYNONYM PUBLIC
EXF$PLAN_TABLE TABLE EXFSYS
PLAN_TABLE$ TABLE SYS

Posted in ADMINISTRATION | Leave a Comment »

ORA-01157: cannot identify/lock data file 202 – see DBWR trace file

Posted by ssgottik on 17/06/2015

ERROR at line 1:
ORA-01157: cannot identify/lock data file 202 – see DBWR trace file
ORA-01110: data file 202: ‘/u10/oradata/orcl01/temp_02.dbf’

STEP 01: Drop the particular datafile:

SQL>alter database tempfile ‘/u10/oradata/orcl01/temp_02.dbf’ drop;

STEP 02 : Add the temp file :

SQL>ALTER TABLESPACE TEMP1 add tempfile ‘/u10/oradata/orcl01/temp_02.dbf’ size 10G;

Posted in ADMINISTRATION | Leave a Comment »

FLUSING THE SINGLE SQL STATEMENT FROM THE SHARED POOL

Posted by ssgottik on 12/06/2015

FLUSING THE SINGLE SQL STATEMENT FROM THE SHARED POOL:

Till 10.2.0.2:
————–
SQL > ALTER SYSTEM FLUSH SHARED_POOL;

In 10.2.0.4:
————
The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.

NOTE : RDBMS patch 5614566

FROM 11g:
———
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID = ‘9k2thjfjykcb%’;

ADDRESS HASH_VALUE
—————- ———-
000000085FD77CF0 808321886

SQL> exec DBMS_SHARED_POOL.PURGE (‘0000000090DA1C68, 809121806’, ‘C’);

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like ‘9k2thjfjykcb%’;

No rows selected

Posted in ADMINISTRATION | Leave a Comment »

CLONE ORACLE 11g HOME

Posted by ssgottik on 09/04/2015

STEPS TO CLONE ORACLE 11g ORACLE_HOME  FROM ONE SERVER TO ANOTHER

 

STEP 01 : ZIP THE ORACLE HOME FILE FROM THE SOURCE

cd /u01/app/oracle/product

$tar  -pcvf   11204.tar   11.2.0.4

STEP 02 : SCP  .tar file to the target server

scp -p username@target.com:/u01/app/oracle/product/ .

STEP 03 : UNTAR THE .tar FILE

$tar -xvf 11204.tar

STEP04 : RUN CLONE.PL FROM ORACLE_HOME

vmlinux01::/u01/app/oracle/product/11.2.0.4/clone/bin> perl clone.pl ORACLE_BASE=’/u01/app/oracle/’
ORACLE_HOME=’/u01/app/oracle/product/11.2.0.4′ ORACLE_HOME_NAME=’11204_HOME’
./runInstaller -silent -clone -waitForCompletion “ORACLE_HOME=/u01/app/oracle/product/11.2.0”
“ORACLE_HOME_NAME=11204_HOME” -noConfig -nowait
Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-12-02_03-22-12PM. Please wait …Oracle
Universal Installer, Version 11.2.0.4.0 Production
Copyright (C) 1999, 2008, Oracle. All rights reserved.

You can find a log of this install session at:
/u01/app/oraInventory/logs/cloneActions2012-12-02_03-22-12PM.log
………………………………………………………………………………………. 100% Done.

Installation in progress (Tuesday, December 2, 2012 3:22:29 PM EST)
……………………………………………………………….
73% Done.
Install successful

Linking in progress (Tuesday, December 2, 2012 3:22:39 PM EST)
Link successful

Setup in progress (Tuesday, December 2, 2012 3:24:50 PM EST)
Setup successful

End of install phases.(Tuesday, December 2, 2012 3:24:56 PM EST)
WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central
inventory of this system.
To register the new inventory please run the script ‘/u01/app/oraInventory/orainstRoot.sh’ with root privileges.
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts need to be executed as the “root” user.
#!/bin/sh
#Root script to run
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as “root”
3. Run the scripts

The cloning of 11204_HOME was successful.
Please check ‘/u01/app/oraInventory/logs/cloneActions2012-12-02_03-22-12PM.log’ for more details.
vmlinux01::/u01/app/oracle/product/11.2.0/clone/bin> cat /etc/oratab
#

STEP 05 : RUN orainstRoot.sh and root.sh as root user

#/u01/app/oraInventory/orainstRoot.sh
#/u01/app/oracle/product/11.2.0/root.sh

 

Thanks,

Satish.G.S

Posted in ADMINISTRATION | Leave a Comment »