ORA-12514 from DGMGRL Switchover Database

For DGMGRL to switchover database, add an entry into LISTENER.ora with static registration with listeners for each host where database resides on respectively.

ISSUES

When using DGMGRL command line to switchover database, we see ORA-12514 error.  The following are two databases for primary and standby database :

Sydney – Primary database
Melbourne – Physical standby database

DGMGRL> switchover to 'Melbourne';
Performing switchover NOW, please wait...
Operation requires a connection to instance "Melbourne" on database "Melbourne"
Connecting to instance "Melbourne"
connected
New primary database "Melbourne" is opening ...
Operation requires startup of instance "Sydney" on database "Sydney"
Starting instance "Sydney"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: you are no longer connected to ORACLE

Please complete the following steps to finish switchover:
start up and mount instance "Sydney" of database "Sydney"

SOLUTION

we need have static registration with listeners, where a standby database/Data Guard scenario exists. Basically once dmon terminates the instance it was unable to connect back to it again to restart the new standby database without a static entry.

Add an entry into LISTENER.ora for each host where database resides on respectively.

For host where Sydney database resides on:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Sydney_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = Sydney)
)

For host where Melbourne database resides on:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = Melbourne_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = Melbourne)
)

Check property “StaticConnectIdentifier” to see static registration successfully or not.

DGMGRL> show database "Sydney" StaticConnectIdentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hosta)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=Sydney_DGMGRL)(INSTANCE_NAME=Sydney)(SERVER=DEDICATED)))'
DGMGRL> show database "Melbourne" StaticConnectIdentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=Melbourne_DGMGRL)(INSTANCE_NAME=Melbourne)(SERVER=DEDICATED)))'

Where is 12.1.0.2 Oracle Clusterware Diagnostic and Alert Logs ?

From 12.1.0.2, Clusterware Diagnostic and Alert Logs are relocated from $GI_HOME/log/ to GI $ORACLE_BASE/diag/

We’ve just upgraded Oracle 12.1.0.1 GI to 12.1.0.2, and found clusterware logs have been relocated from $GI_HOME/log/.

According to Oracle support Doc (ID 1915729.1), from Oracle 12.1.0.2 on Oracle clusterware (part of Grid Infrastructure) uses Oracle database fault diagnosability infrastructure to manage diagnostic data and its alert logs. As a result, most diagnostic data resides in the Automatic Diagnostic Repository (ADR).

Please note :

1)More space is required for GI $ORACLE_BASE in case of file system full , because Clusterware logs are moved to ADR which is part of ORACLE_BASE.

2)When opening SR, instead of diagcollection.pl, TFA should be used to collect diagnostics:
$/u01/app/12.1.0.2/grid/bin/tfactl diagcollect -from “Jan/20/2015 08:00:00” -to “Jan/23/2015 13:00:00”

Collecting data for all nodes
Scanning files from Jan/20/2015 08:00:00 to Jan/23/2015 13:00:00

Repository Location in hx415 : /u01/app/grid/tfa/repository
2015/01/23 14:42:55 EST : Running an inventory clusterwide …
2015/01/23 14:42:55 EST : Collection Name : tfa_Fri_Jan_23_14_42_49_EST_2015.zip
……
……
……

CHILD TABLE DOESN’T INHERIT PARENT’S COMPRESSION ATTRIBUTE AFTER SPLITTING PARENT PARTITION

set “_force_oltp_compress”=true for CHILD TABLE to INHERIT PARENT’S COMPRESSION ATTRIBUTE AFTER SPLIT PARTITION

ISSUES

When split a partition of a reference parent table, the compression attribute “compress for OLTP” is not inherited by child partitions. Instead,it changes into “compress for BASIC’ by default.

The following test case is on 11.2.0.4 database. We have two tables named “parent” and “child”, here parent is partitioned by range of a date column, and child table is a partitioned by foreign key reference to parent table.

1) Create two tables.

SQL>CREATE TABLE Parent
    (
     pk_p_id number primary key ,
     P_Date DATE,
     P_cost NUMBER
    ) compress for oltp
    PARTITION BY RANGE (P_Date)
   (PARTITION P_First VALUES LESS THAN (TO_DATE('01-01-2014', 'DD-MM-YYYY')) COMPRESS for OLTP,
    PARTITION P_2014_11_01 VALUES LESS THAN (TO_DATE('02-11-2014', 'DD-MM-YYYY')) COMPRESS for OLTP,
    PARTITION P_MAXIMUM VALUES LESS THAN (MAXVALUE) COMPRESS for OLTP
   );

Table created.

SQL> CREATE TABLE Child(
pk_c_id number primary key ,
fk_c_id number not null,
c_total NUMBER,
CONSTRAINT Child_FK FOREIGN KEY ( fk_c_id ) REFERENCES parent ( pk_p_id)
) compress for oltp
partition by reference ( Child_FK ) ;

Table created.

2) All partitions are “compress for oltp”

SQL> select TABLE_NAME,PARTITION_NAME,COMPRESSION,COMPRESS_FOR 
from user_tab_partitions
where TABLE_NAME in ('PARENT','CHILD')
order by TABLE_NAME,PARTITION_POSITION; TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ----------- ---------------- --------- ------------
CHILD P_FIRST ENABLED OLTP CHILD P_2014_11_01 ENABLED OLTP CHILD P_MAXIMUM ENABLED OLTP PARENT P_FIRST ENABLED OLTP PARENT P_2014_11_01 ENABLED OLTP PARENT P_MAXIMUM ENABLED OLTP 6 rows selected.

3) split parent P_MAXIMUM partition:

SQL> alter table PARENT split partition P_MAXIMUM at (TO_DATE('03-11-2014', 'DD-MM-YYYY')) into ( partition P_2014_11_02, partition P_MAXIMUM) update indexes;

Table altered.

4) New child partitions compression attribute is “compress for BASIC”

SQL>select TABLE_NAME,PARTITION_NAME,COMPRESSION,COMPRESS_FOR 
from user_tab_partitions
where TABLE_NAME in ( 'PARENT','CHILD')
order by TABLE_NAME,PARTITION_POSITION; TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ------------ --------------- --------- ------------ CHILD P_FIRST ENABLED OLTP CHILD P_2014_11_01 ENABLED OLTP CHILD P_2014_11_02 ENABLED BASIC CHILD P_MAXIMUM ENABLED BASIC PARENT P_FIRST ENABLED OLTP PARENT P_2014_11_01 ENABLED OLTP PARENT P_2014_11_02 ENABLED OLTP PARENT P_MAXIMUM ENABLED OLTP 8 rows selected.

5) This issue still exists in 12c.

$ sqlplus / as sysdba
SQL*Plus:Release 12.1.0.2.0 Production on Mon Nov 17 07:31:31 2014
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 Partitioning, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

SQL> connect system
Enter password: 
Connected.

SQL> CREATE TABLE Parent 
 ( 
 pk_p_id number primary key , 
 P_Date DATE, 
 P_cost NUMBER 
 ) compress for oltp 
 PARTITION BY RANGE (P_Date) 
 (PARTITION P_First 
 VALUES LESS THAN (TO_DATE('01-01-2014', 'DD-MM-YYYY')) COMPRESS for OLTP,
 PARTITION P_2014_11_01 
 VALUES LESS THAN (TO_DATE('02-11-2014', 'DD-MM-YYYY')) COMPRESS for OLTP, 
 PARTITION P_MAXIMUM VALUES LESS THAN (MAXVALUE) COMPRESS for OLTP
 );
Table created.
SQL> CREATE TABLE Child 
( 
pk_c_id number primary key , 
fk_c_id number not null, 
c_total NUMBER, 
CONSTRAINT Child_FK FOREIGN KEY ( fk_c_id ) REFERENCES parent ( pk_p_id)
) compress for oltp 
partition by reference ( Child_FK ) ;

Table created.
SQL> col PARTITION_NAME format a18
SQL> col table_name format a15
SQL> set pagesize 120
SQL> set linesize 160
SQL>select TABLE_NAME,PARTITION_NAME,COMPRESSION,COMPRESS_FOR 
from user_tab_partitions
where TABLE_NAME in ( 'PARENT','CHILD')
order by TABLE_NAME,PARTITION_POSITION;

TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ----------- -------------- -------- ------------ CHILD P_FIRST ENABLED ADVANCED CHILD P_2014_11_01 ENABLED ADVANCED CHILD P_MAXIMUM ENABLED ADVANCED PARENT P_FIRST ENABLED ADVANCED PARENT P_2014_11_01 ENABLED ADVANCED PARENT P_MAXIMUM ENABLED ADVANCED 6 rows selected.
SQL> alter table PARENT split partition P_MAXIMUM at (TO_DATE('03-11-2014', 'DD-MM-YYYY')) into ( partition P_2014_11_02, partition P_MAXIMUM) update indexes;
Table altered.
SQL> select TABLE_NAME,PARTITION_NAME,COMPRESSION,COMPRESS_FOR
from user_tab_partitions
where TABLE_NAME in ( 'PARENT','CHILD')
order by TABLE_NAME,PARTITION_POSITION;
TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ---------- --------------- -------- --------------- CHILD P_FIRST ENABLED ADVANCED CHILD P_2014_11_01 ENABLED ADVANCED CHILD P_2014_11_02 ENABLED BASIC CHILD P_MAXIMUM ENABLED BASIC PARENT P_FIRST ENABLED ADVANCED PARENT P_2014_11_01 ENABLED ADVANCED PARENT P_2014_11_02 ENABLED ADVANCED PARENT P_MAXIMUM ENABLED ADVANCED 8 rows selected.

Workaround:

set “_force_oltp_compress”=true in session level or system level.

Bug:

Bug 20081931 has been raised by Oracle Support to Oracle Development

Bug 20081931 – CHILD TABLE DOESN’T INHERIT PARENT’S COMPRESSION ATTRIBUTE AFTER SPLIT PARTITION

ORA-00959 tablespace does not exist by revoking user quota on a dropped tablespace

It is a bug? Tablespace has been dropped, but this tablespace information still exists in database data dictionary.

User has quotas on two tablespaces. One tablespace needs to be dropped. After dropping this tablespace, the quota on this tablespace can not be manually revoked from this user.

To fix this, we have to create and add a dummy tablespace with same name back again, then revoke user quota on this tablespace, drop the tablespace finally.

Here are the steps to show this issue plus how to fix it most importantly.

1) User has quotas on two tablespaces.

SQL>select tablespace_name,username,max_bytes,dropped 
from dba_ts_quotas
where username='TESTUSER';
TABLESPACE_NAME USERNAME MAX_BYTES DRO ---------------- ---------- ---------- --- USERS_OLD TESTUSER -1 NO USERS TESTUSER -1 NO

2) Drop tablespace “USERS_OLD”.

SQL> drop tablespace USERS_OLD including contents and datafiles;
Tablespace dropped.

3) Quota on tablespace still exists.

SQL> select tablespace_name,username,max_bytes,dropped 
from dba_ts_quotas
where username='TESTUSER';
TABLESPACE_NAME USERNAME MAX_BYTES DRO ---------------- --------- ---------- --- USERS_OLD TESTUSER -1 YES USERS TESTUSER -1 NO

4) Revoke quota on tablespace “USERS_OLD”

SQL> alter user TESTUSER quota 0 on USERS_OLD;
alter user TESTUSER quota 0 on USERS_OLD
*
ERROR at line 1:
ORA-00959: tablespace 'USERS_OLD' does not exist

5) Create a dummy tablespace with  same name.

SQL> create tablespace USERS_OLD datafile '+dg1' size 10m;
Tablespace created.

6) Revoke quota on tablespace USERS_OLD.

SQL> alter user TESTUSER quota 0 on USERS_OLD;
User altered.

7) To confirm quota on tablespace “USERS_OLD” revoked.

SQL> select tablespace_name,username,max_bytes,dropped 
from dba_ts_quotas
where username='TESTUSER'; TABLESPACE_NAME USERNAME MAX_BYTES DROPPED ---------------- ---------- ---------- ------ USERS TESTUSER -1 NO

8) Drop the dummy tablespace. 

SQL> drop tablespace USERS_OLD including contents and datafiles;
Tablespace dropped.