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

February 6, 2016 Leave a comment

Today i was trying to enable supplemental log data for a table i got the below error.

Error:-

GGSCI (ORA11G01.world.com) 3> add trandata scott.emp

2016-02-06 07:48:26 WARNING OGG-00706 Failed to add supplemental log group on table SCOTT.EMP due to ORA-01031: insufficient privileges SQL ALTER TABLE “SCOTT”.”EMP” ADD SUPPLEMENTAL LOG GROUP “GGS_73201” (“EMPNO”) ALWAYS /* GOLDENGATE_DDL_REPLICATION */.

GGSCI (ORA11G01.world.com) 4>

root cause is:-

gguser does not have alter any table priv on scott.emp table hence the issue.

i given below and worked fine.

============================================

SYS@INDIADB>grant alter any table to gguser;

Grant succeeded.

SYS@INDIADB>

============================================

GGSCI (ORA11G01.world.com) 5> add trandata scott.emp

Logging of supplemental redo log data is already enabled for table SCOTT.EMP.

GGSCI (ORA11G01.world.com) 6>

 

All the best.

 

 

 

Categories: ISSUES

How to change the asm related users(sys,asmsnmp) passowords in 12c.

December 24, 2015 Comments off

Today i came across changing the passwords for sys and asmsnmp users passwords in 12c and i followed the below procedure.   System itself taken care of

 

njunpXXXXXXX<oracle>su – grid
Password:
Oracle Corporation SunOS 5.11 11.2 July 2015
njunpXXXXXXX<grid>asmcmd
ASMCMD> passwd
usage: passwd <user>
help: help passwd
ASMCMD> passwd sys
Enter old password (optional):
Enter new password: *************
ASMCMD> exit
njunpXXXXXXX<grid>sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 23 16:29:56 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option

SQL> alter user asmsnmp identified by XXXXXXXXX;

User altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option
njunpXXXXXXX<grid>

Also below one also worked for me.

njunpXXXXXXX<grid>asmcmd
ASMCMD> passwd asmsnmp
Enter old password (optional):
Enter new password: **************
ASMCMD>

 

Categories: ASM

How to change the oracle_home for GRID based database.

December 10, 2015 Comments off

Today i was asked to change the oracle home of my database due to naming standards. So i had installed oracle home in new location.

OLD oracle_home:- /local0/app/oracle/product/11.2

NEW oracle_home:- /local0/app/oracle/product/11.2.0.4

After that i did the following actions.

  1. Copied the parameter and password files  to $ORACLE_HOME/dbs of new.
  2. Collected the current configuration of the database using srvctl.

    ncclpdomds06<grid>srvctl config database -d domds
    Database unique name: domds
    Database name:
    Oracle home: /local0/app/oracle/product/11.2
    Oracle user: oracle
    Spfile: +data/domds_06/parameterfile/spfile.409.892459461
    Domain:
    Start options: open
    Stop options: immediate
    Database role: PRIMARY
    Management policy: AUTOMATIC
    Database instance: domds
    Disk Groups: DATA,FRA,RDOEV
    Services:
    ncclpdomds06<grid>

  3. Change the oracle_home location using srvctl using below command.

ncclpdomds06<grid>srvctl modify database -d domds -o /local0/app/oracle/product/11.2.0.4

     4.   Now check the configuration changes reflected or not?

ncclpdomds06<grid>srvctl config database -d domds
Database unique name: domds
Database name:
Oracle home: /local0/app/oracle/product/11.2.0.4
Oracle user: oracle
Spfile: +data/domds_06/parameterfile/spfile.409.892459461
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Database instance: domds
Disk Groups: DATA,FRA,RDOEV
Services:
ncclpdomds06<grid>

5.   Start the database.

ncclpdomds06<grid>srvctl start database -d domds

 

Categories: 11g

Necesassary priveleges for running oracle base lines 11g.

December 7, 2015 Comments off

Today i got a requirement to grant the ability to create Oracle base lines for individual users. i gone through various links and few were saying that select any dictionary privs is required, however i am interested to provide bare minimum privs.   Below are the privs which i granted and user is able to perform the required action from his side.

 

grant ADMINISTER SQL TUNING SET to testadmin;
grant ADMINISTER ANY SQL TUNING SET to testadmin;
grant ADMINISTER SQL MANAGEMENT OBJECT to testadmin;
grant ADMINISTER SQL TUNING SET to testadmin;

Categories: 11g

How to connect to DB using JDBC method.

December 3, 2015 Comments off

Hi,

Simply connect to DB using sqldeveloper using below string.

jdbc:oracle:thin:@(description=(address=(host=HOSTNAME)(protocol=tcp)(port=PORT))(connect_data=(service_name=DB_NAME)(server=DEDICATED)))

 

JDBC_connection_details

 

Categories: Oracle DBA

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

September 18, 2014 Comments off

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

# Problem while turning on autotrace with statistics
Error:-
SQL>set autotrace on statistics
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS reportCause:-
autotrace privs not exists.
Solution:-
cd $ORACLE_HOME/rdbms/admin
sqlplus system
@utlxplan
create public synonym plan_table for plan_table;
grant all on plan_table to public;
exit
cd $ORACLE_HOME/sqlplus/admin
sqlplus / as sysdba
@plustrce
grant plustrace to public
Categories: Oracle DBA

DBA INTERVIEW Qs.

June 26, 2014 Comments off

Few interview questions which may be asked in the interview for DBAs.

Patching

1) What is the user of PSU.

2) How you will plan for PSU.

3) What are the pre steps for patching.

4) What are the post_steps for patching.

5) I said, as a part of pre-req, we will take the backup of ORACLE_HOME then the question from her was
that why we need to take the backup of ORACLE_HOME.

RMAN

1) What is sync command in RMAN.

2) How to restore control file from RMAN command.

3) What is reset logs option.

DG:

1) How you will create Physical DB?

2) How you will stop and start the recovery

RAC:

1) How you will stop two instances of a cluster where 4 nodes.

2) What is the SCAN?

Performance tuning

1) If user complaining that the sql is running fine till yesterday and today it is performing very poorly, how you will solve this problem. what is the approach you will start.

2) What you the method you will approach if you find “DB sequential read” in the top 5 events.

3) In which scenarios, you will consider to increase shared_pool_size.

4) In which scenarios, you will consider to tune pga_aggregate_target.

5) How you will avoid disk sorts.

6) How you will tune undo tablespace.

7) How you will enable the trace for a session.

 Recovery: 

1) What is instance recovery? how recovery will happen.

2) What is incomplete recovery and how you will open the database after incomplete recovery?

3) How to recover a data file?

Categories: INTERVIEW_QS
Follow

Get every new post delivered to your Inbox.