ORA-02065: illegal option for ALTER SYSTEM

In an old 10g database, when tried to change SGA_TARGET parameter in spfile, “ORA-02065: illegal option for ALTER SYSTEM” occurred.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jun 13 15:56:37 2017
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter sga_target

NAME_COL_PLUS_SHOW_PARAM
--------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
--------------------------------------------------------------------------------
sga_target
big integer
4G

SQL> alter system set sga_target=2g sid='RACTEST1' scope=spfile;
 alter system set sga_target=2g sid='RACTEST1' scope=spfile
 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

By swapping the order of “sid” and scope, it is working fine:

SQL> alter system set sga_target=2g scope=spfile sid='RACTEST1';

System altered.

SQL>
Posted in Configuration, GI and RAC, ORA- ERRORS | Tagged , | Leave a comment

ORA-02158: invalid CREATE INDEX option

Tried to apply Oracle Advanced Compression Feature onto IOT tables in 12.1.0.2, then got this error.

SQL> CREATE TABLE TEST_IOT_TBL
 ( id   number,
   sex  char(1),
   name varchar2(20),
 CONSTRAINT TEST_IOT_TBL_PK PRIMARY KEY (id,sex ) ENABLE
 ) ORGANIZATION INDEX ;

Table created.

SQL> insert into TEST_IOT_TBL values ( 1,'M','jamesh1');

1 row created.

SQL> insert into TEST_IOT_TBL values (2,'M','jamesh2');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table TEST_IOT_TBL move compress for all operations;
 alter table TEST_IOT_TBL move compress for all operations
 *
ERROR at line 1:
ORA-02158: invalid CREATE INDEX option

 

Posted in ORA- ERRORS, oracle advanced compression | Tagged , , | Leave a comment

ORA-14451: unsupported feature with temporary table

It looks like temporary table can nor be compressed by using Oracle Advanced Compression Features in 12.1.0.2.

SQL> alter session set current_schema=jamesh;

SQL> CREATE GLOBAL TEMPORARY TABLE TEST_TBL ( id number, name varchar(20), CONSTRAINT TEST_TBL_PK PRIMARY KEY (id) ENABLE ) ON COMMIT DELETE ROWS;

Table created.

SQL> insert into TEST_TBL values ( 1,'jamesh');

1 row created.

SQL> select * from TEST_TBL;

ID NAME
---------- --------------------
 1 jamesh

SQL> alter table TEST_TBL move compress for all operations;
 alter table TEST_TBL move compress for all operations
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

SQL> commit;

Commit complete.

SQL> select * from TEST_TBL;

no rows selected

 

Posted in ORA- ERRORS, oracle advanced compression | Tagged , | Leave a comment

ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

SQL> CREATE SEQUENCE test_seq
     START WITH 1
     INCREMENT BY 1
     MAXVALUE 10; 

Sequence created.
SQL> select test_seq.currval from dual;
 select test_seq.currval from dual
 *
ERROR at line 1:
ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 1

SQL> /

NEXTVAL
----------
 2

...
..
.

NEXTVAL
----------
 9

SQL> /

NEXTVAL
----------
 10

SQL> /
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

1)Increase maximum value:

SQL> alter sequence test_seq maxvalue 15;

Sequence altered.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 11

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 12

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 13

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 14

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 15

SQL> select test_seq.nextval from dual;
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

2)Increment by -1.

SQL> alter sequence test_seq increment by -1;

Sequence altered.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 14

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 13

...
..
.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 2

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 1

SQL> select test_seq.nextval from dual;
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated

3) Recycle the sequence starting from minimum value ( 1 ) again:

SQL>select test_seq.nextval from dual;


NEXTVAL
----------
 15

SQL> /
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated


SQL> select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CACHE_SIZE,CYCLE_FLAG from dba_sequences where SEQUENCE_NAME='TEST_SEQ';

SEQUENCE_NAM MIN_VALUE  MAX_VALUE  INCREMENT_BY CACHE_SIZE C
------------ ---------- ---------- ------------ ---------- -
TEST_SEQ     1          15         1            20         N

SQL> alter sequence TEST_SEQ cycle cache 10;

Sequence altered.

SQL> select test_seq.nextval from dual ;

NEXTVAL
----------
 1
Posted in ORA- ERRORS, SQL Scripts For DBA | Tagged , , , , , , , , , | Leave a comment

Oracle Database Time Zone

1) dbtimezone only shows the database creation time TZ offset from OS environment.
For AEST, it could be either of the below depending on OS environment variable:
00:00
+10:00 ( non DST )
+11:00 ( DST )

SQL> select dbtimezone from dual;

DBTIME
------
+11:00


SQL> select created from v$database;

CREATED
-----------------
20161115-15:06:11    <------ DST

2) timezone file. For Australia, it needs to be greater than 14.

SQL> select * from v$timezone_file;

FILENAME              VERSION 
-------------------- ---------- 
timezlrg_18.dat       18

3) Database server time zone ( TIMESTAMP WITH TIME ZONE ):

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
-------------------------------------
24-MAY-17 04.12.15.769958 PM +10:00

4) Current session time zone :

SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
-------------------------------------------------
24-MAY-17 02.16.06.660376 PM +08:00 

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
-------------------------------------------------
+08:00

5) Time zone example :

SQL> create table table_tz ( t1 TIMESTAMP WITH TIME ZONE, t2 TIMESTAMP WITH LOCAL TIME ZONE);

Table created.


SQL> insert into table_tz values (SYSTIMESTAMP,SYSTIMESTAMP);

1 row created.

SQL> select t1,t2 from table_tz ;

T1
----------------------------------------
T2
----------------------------------------
24-MAY-17 04.28.20.428944 PM +10:00
24-MAY-17 02.28.20.428944 PM

SQL> insert into table_tz values (LOCALTIMESTAMP,LOCALTIMESTAMP );

1 row created.

SQL> select t1,t2 from table_tz;

T1
--------------------------------------------------------------
T2
--------------------------------------------------------------
24-MAY-17 04.28.20.428944 PM +10:00
24-MAY-17 02.28.20.428944 PM

24-MAY-17 02.30.26.599416 PM +08:00
24-MAY-17 02.30.26.599416 PM
Posted in Configuration, Globalization | Tagged , , , , , , , , , , , , | Leave a comment

Change the ADR retention

The default retention for SHORTP_POLICY is 720 ( HOURS ) /30 days, LONGP_POLICY is 8760 ( HOURS ) or 1 year.  The details are described below:

Attribute Name Description
SHORTP_POLICY Number of hours after which to purge ADR contents that have a short life. Default is 720 (30 days).A setting of 0 (zero) means that all contents that have a short life can be purged. The maximum setting is 35791394. If a value greater than 35791394 is specified, then this attribute is set to 0 (zero).

The ADR contents that have a short life include the following:

  • Trace files
  • Core dump files
  • Packaging information
LONGP_POLICY Number of hours after which to purge ADR contents that have a long life. Default is 8760 (365 days).A setting of 0 (zero) means that all contents that have a long life can be purged. The maximum setting is 35791394. If a value greater than 35791394 is specified, then this attribute is set to 0 (zero).

The ADR contents that have a long life include the following:

  • Incident information
  • Incident dumps
  • Alert logs

The below example shows how to change retention policies to 1 week ( 168 hours )  and 2 weeks ( 336 hours ) respectively:

oracle@racnode1:/u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace$ adrci

ADRCI: Release 12.1.0.2.0 - Production on Mon May 22 10:43:49 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show home
ADR Homes:
diag/rdbms/eactest/RACTEST1
adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;

ADR Home = /u01/app/oracle/diag/rdbms/ractest/RACTEST1:
*************************************************************************exit

SHORTP_POLICY         LONGP_POLICY
-------------------- --------------------
720                   8760

1 rows fetched

adrci> set control (SHORTP_POLICY=168);
adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;

ADR Home = /u01/app/oracle/diag/rdbms/ractest/RACTEST1:
*************************************************************************
SHORTP_POLICY         LONGP_POLICY
-------------------- --------------------
168                    8760

1 rows fetched

adrci> set control (LONGP_POLICY=336 );
adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;

ADR Home = /u01/app/oracle/diag/rdbms/ractest/RACTEST1:
*************************************************************************
SHORTP_POLICY         LONGP_POLICY
-------------------- --------------------
168                   336

1 rows fetched


adrci> purge;
adrci>

To purge incident files older than 6 days ( 8640 MINUTES ) , you will need to prefer the following commands:

adrci> purge -age 8640 -type INCIDENT
Posted in ADR | Tagged , , , , , , , , | Leave a comment

Tablespace Usage History Report

Please make sure you are licensed to run sqls against DBA_HIST_*, like  dba_hist_tbspc_space_usage tblusage.

The length of the report depends on  AWR retention. Please refer to  “Change AWR Retention & Interval” for how to change AWR retention.

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING
select THE_DATE,
       TABLESPACE,
       SIZE_IN_GB_TD,
       SIZE_IN_GB_YTD,
       SIZE_IN_GB_TD - SIZE_IN_GB_YTD  SIZE_IN_GB_INCREASED
from ( 
     select 
            to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') THE_DATE,
            tbl.name TABLESPACE,
            round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2) SIZE_IN_GB_TD,
            lag( round(max( tblusage.TABLESPACE_USEDSIZE ) * pmt.value /1024/1024/1024, 2),1) over ( order by to_char( histsnap.begin_interval_time, 'YYYY-MM-DD') ) SIZE_IN_GB_YTD 
     from 
          dba_hist_snapshot histsnap,
          dba_hist_tbspc_space_usage tblusage,
          v$tablespace tbl,
          v$parameter pmt
    where 
          histsnap.SNAP_ID = tblusage.SNAP_ID
      and tbl.ts# = tblusage.TABLESPACE_ID
      and tbl.name ='&TABLESPACENAME'
      and pmt.name ='db_block_size'
   group by 
          to_char( histsnap.begin_interval_time, 'YYYY-MM-DD'),
          tbl.name,
          pmt.value
 order by 
         to_char( histsnap.begin_interval_time, 'YYYY-MM-DD')
 );

Enter value for tablespacename: USERS
old 17: and tbl.name ='&TABLESPACENAME'
new 17: and tbl.name ='USERS'

THE_DATE   TABLESPACE  SIZE_IN_GB_TD SIZE_IN_GB_YTD SIZE_IN_GB_INCREASED
---------- ----------- ------------- -------------- --------------------
2017-05-10 USERS        4.17
2017-05-11 USERS        4.17         4.17           0
2017-05-12 USERS        4.45         4.17            .28
2017-05-13 USERS        4.55         4.45            .1
2017-05-14 USERS        4.55         4.55           0
2017-05-15 USERS        4.56         4.55            .01
2017-05-16 USERS        4.84         4.56            .28
2017-05-17 USERS        4.96         4.84            .12
2017-05-18 USERS        5.14         4.96            .18
2017-05-19 USERS        5.14         5.14           0

10 rows selected.
Posted in AWR, SQL Scripts For DBA | Tagged , , , , , , , | Leave a comment

Change the ORMB DB User Password

After the DB user password is changed by DBA, or after the ORMB schema is refreshed from other environment and need keep the password of old one. The DB user password needs to be changed as per Oracle Doc.

For example, CISADM_TEST was refreshed from CISADM_PROD. In database, the password is still the one of CISADM_PROD.

SQL> select * from CI_WFM_OPT where ext_opt_type in ('F1DP','F1DU');

WFM_NAME SEQ_NUM        VERSION    WFM_OPT_VAL                                       EXT_
------------ ---------- ---------- ------------------------------------------------  ----
F1_DBCONINFO 1          1          ENC(m94Ug7/qXRpNj4PaaI/Wq9Lasp/6RI76LOrbouIMN0Q=) F1DP
F1_DBCONINFO 1          1          CISADM_PROD                                       F1DU

Change DB user password as per Oracle Doc:

Appendix C : Changing the DB User Password 
If you have changed the database user password, you need to execute the following steps before installing the rollup pack for Oracle Utilities Application Framework Version 4.3.0.1.0:
Download and apply the single fix available for Bug 22505470 - PATCHES APPEND EXTRA SPACE TO STRINGS AND ADD DB SERVICE CONNECTION SUPPORT on the application and database environments.
Perform the following steps on the application environment:
Change the DB Name using the configureEnv command.
Execute the initialSetup utility using the following command:

AIX, Linux:

 $SPLEBASE/bin/initialSetup.sh

Windows: 

%SPLEBASE%\bin\initialSetup.cmd

Execute the invokeDBUpdatePatch utility to change the database user name and password using the following command:

AIX, Linux: 

$SPLEBASE/bin/invokeDBUpdatePatch.sh -b

Windows: 

%SPLEBASE%\bin\invokeDBUpdatePatch.cmd –b

Check the password is changed with new user name:

SQL> select * from CI_WFM_OPT where ext_opt_type in ('F1DP','F1DU');

WFM_NAME SEQ_NUM        VERSION    WFM_OPT_VAL                                       EXT_
------------ ---------- ---------- ------------------------------------------------  ----
F1_DBCONINFO 1          1          ENC(fGXkVOgDO5OUv63oEMCGpd9mujCEIrw5+/DkKn+QSUQ=) F1DP
F1_DBCONINFO 1          1          CISADM_TEST                                       F1DU
Posted in ORMB | Tagged , , , , , | Leave a comment

ORA-01758: table must be empty to add mandatory (NOT NULL) column

While adding NOT NULL column into a table, the below error occurred:

SQL> Alter table CM_ODS_DWN_STG add MATCH_EVT_ID  CHAR(12) NOT NULL;
Alter table CM_ODS_DWN_STG add MATCH_EVT_ID  CHAR(12) NOT NULL
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

The workarounds could be :

1) Default value is given:

SQL> Alter table CM_ODS_DWN_STG add MATCH_EVT_ID  CHAR(12) DEFAULT 'EMPTY' NOT NULL;

2) Remove NOT NULL:

SQL> Alter table CM_ODS_DWN_STG add MATCH_EVT_ID  CHAR(12);

3) Backup the table to another staging table, truncate the table and then add the NOT NULL column, insert the records back to the table from the staging table.

Posted in ORA- ERRORS | Tagged , | Leave a comment

ORA-39346: data loss in character set conversion for object SCHEMA_EXPORT/TABLE/COMMENT

When importing a schema exported from one 12cR1 AL32UTF8 database to another database  with same AL32UTF8 characterset, this ORA -error was generated :

ORA-39346: data loss in character set conversion for object SCHEMA_EXPORT/TABLE/COMMENT

The error message clearly shows the problem data is from “SCHEMA_EXPORT/TABLE/COMMENT”.

Using MDU tool ( Database  Migration Assistant for Unicode ) to scan database Dictionary Objects, we can see the problem data is from SYS.COM$.

Get the columns and tables for those problem comments:

SQL> desc sys.com$
 Name           Null?     Type
 -------------- -------- ----------------------------
 OBJ#           NOT NULL NUMBER
 COL#                    NUMBER
 COMMENT$                VARCHAR2(4000)

SQL> select count(*) from sys.com$;

COUNT(*)
----------
 151

SQL> select OBJ# , COL#, substr(COMMENT$,1,80) from sys.com$ where comment$ like 'The Accounting Date of the Transaction%same as the incoming transaction date';

OBJ# COL#
---------- ----------
SUBSTR(COMMENT$,1,80)
--------------------------------------------------------------------------------
 143947 6
The Accounting Date of the Transaction ?.. same as the incoming transaction date

143982 6
The Accounting Date of the Transaction ?.. same as the incoming transaction date


SQL> select owner, OBJECT_ID,OBJECT_NAME from dba_objects where OBJECT_ID in (143947,143982);

OWNER            OBJECT_ID   OBJECT_NAME
---------------- ---------- ------------------------------
JAMESH           143947     CM_GL_REC_DWN_STG
JAMESH1          143982     CM_GL_REC_DWN_STG

SQL> select OWNER,TABLE_NAME,COLUMN_NAME from dba_tab_columns where TABLE_NAME='CM_GL_REC_DWN_STG' and COLUMN_ID=6;

OWNER            TABLE_NAME            COLUMN_NAME
---------------- --------------------- --------------------
JAMESH           CM_GL_REC_DWN_STG     ACCOUNTING_DT
JAMESH1          CM_GL_REC_DWN_STG     ACCOUNTING_DT


SQL>select OWNER,TABLE_NAME,COLUMN_NAME,COMMENTS from dba_COL_COMMENTs where comments like 'The Accounting Date of the Transaction%same as the incoming transaction date'

OWNER            TABLE_NAME            COLUMN_NAME COMMENTS
---------------- --------------------- -------------------- --------------------------------------------------------------------------------
JAMESH           CM_GL_REC_DWN_STG     ACCOUNTING_DT The Accounting Date of the Transaction ?.. same as the incoming transaction date
JAMESH1          CM_GL_REC_DWN_STG     ACCOUNTING_DT The Accounting Date of the Transaction ?.. same as the incoming transaction date

SQL> comment on column JAMESH.CM_GL_REC_DWN_STG.ACCOUNTING_DT is 'The Accounting Date of the Transaction,same as the incoming transaction date';

Comment created.

SQL> comment on column JAMESH1.CM_GL_REC_DWN_STG.ACCOUNTING_DT is 'The Accounting Date of the Transaction,same as the incoming transaction date';

Comment created.

SQL>select OWNER,TABLE_NAME,COLUMN_NAME,COMMENTS from dba_COL_COMMENTs where comments like 'The Accounting Date of the Transaction%same as the incoming transaction date'

OWNER            TABLE_NAME            COLUMN_NAME COMMENTS
---------------- --------------------- -------------------- --------------------------------------------------------------------------------
JAMESH           CM_GL_REC_DWN_STG     ACCOUNTING_DT The Accounting Date of the Transaction,same as the incoming transaction date
JAMESH1          CM_GL_REC_DWN_STG     ACCOUNTING_DT The Accounting Date of the Transaction,same as the incoming transaction date

Using MDU tool ( Database  Migration Assistant for Unicode ) to scan database Dictionary Objects again, we can see all green including SYS.COM$.

Do an another export and import again, everything will be fine.

Posted in DataPump, ORA- ERRORS | Tagged , , , , , , , , | Leave a comment