When running external script by scheduler. The solution:
chown root $ORACLE_HOME/bin/extjob chmod 4750 $ORACLE_HOME/bin/extjob chown root $ORACLE_HOME/rdbms/admin/externaljob.ora chmod 640 $ORACLE_HOME/rdbms/admin/externaljob.ora chown root $ORACLE_HOME/bin/jssu chmod 4750 $ORACLE_HOME/bin/jssu
Have a nice week!
Matheus.
A quick one: I began to have this problem on 12c’s backup catalog schemas. The reason is that by now all information is protected by policies (VPD). The error:
EXP-00079: Data in table "&TABLE" is protected. Conventional path may only be exporting partial table.
The solution:
catalogdb> GRANT exempt access policy TO &exp_user; Grant succeeded.
Hugs!
Matheus.
Sad history:
IMP-00019: row rejected due to ORACLE error 12899 IMP-00003: ORACLE error 12899 encountered ORA-12899: value too large for column "SCHEMA"."TABLE"."COLUMN" (actual: 61, maximum: 60)
To understand: It happens when the export/import is being made by different charsets. Usually when the destination is a superset with “multibyting” and the source is a single-byte one. The reason is that as more as the charset is not specific, more bits are used to represent a charcter (c-ç, a-ã, o-õ-ô, for example), this way, the columns that uses as data length byte will be different sized between theese databases.
Of course, as more specific a charset configuration is, much better for performance constraints it’ll be (specially for sequencial reads), because the databases needs to work with less bytes in datasets/datablocks for the same tuples, in a simple way to explain. Otherside, this is a quite specific configuration. The performance issues are mostly related to more simple tunings (sql access plan, indexing, statistics or solution architecture) than this kind of details. But, it’s important to mention if you’re working in a database that is enough well tuned…
For more information, I recommend this (recent) documentation: https://docs.oracle.com/database/121/NLSPG/ch2charset.htm. Please, invest your time to understand the relation between “Single-Byte Encoding Schemes” and “Multibyte Encoding Schemes” in this doc.
The follow image ilustrates in a simple way the difference of byting used to address more characters (a characteristic of supersets):
Ok, doke!
And the solution is…
Let’s summarize the problem first: The char (char, varchar) columns uses more bytes to represent the same characters. So the situations where, in the source, the column was used by the maximum lengh, it “explodes” the column lengh in the destination database with a multibyte encoding scheme.
For consideration, I’m not using datapump (expdp/impdp or impdb with networklink) just because it’s a legacy system with long columns. Datapump doesn’t support this “deprecated” type of data.
So, my solution, for this pontual problem occouring during a migration was to change the data lengh of the char columns from “byte” to “char”. This way, the used metric is the charchain rather than bytesize. Here is my “kludge” for you:
select 'ALTER TABLE '||owner||'.'||TABLE_NAME||' MODIFY '||COLUMN_NAME||' CHAR('||data_length||' CHAR);'
from dba_tab_cols where DATA_TYPE='CHAR' and owner='&SCHEMA'
union all
select 'ALTER TABLE '||owner||'.'||TABLE_NAME||' MODIFY '||COLUMN_NAME||' VARCHAR2('||data_length||' CHAR);'
from dba_tab_cols
where DATA_TYPE='VARCHAR2' and owner='&SCHEMA';
And it works!
Hugs and see ya!
Matheus.
An unexpected error, right?
SQL> CREATE PROFILE TEST_PROF LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 180 PASSWORD_GRACE_TIME 30 PASSWORD_REUSE_MAX 15 PASSWORD_VERIFY_FUNCTION fnc_validation; CREATE PROFILE TEST_PROF LIMIT * ERROR at line 1: ORA-28004: invalid argument for function specified in PASSWORD_VERIFY_FUNCTION FNC_VALIDATION
That is a simple need. You have to use 3 parameters on function: username varchar2, password varchar2, old_password varchar2.
Matheus.
Almost a month away… My bad!
Here I go again, with a quick tip, that a passed today. Our kernel was ‘changed’ without advise and this began to happen:
ORA-15186: ASMLIB error function = [asm_init], error = [18446744073709551611], mesg = [Driver not installed] ERROR: error ORA-15186 caught in ASM I/O path
The solution was is basically update the asmlibs, that is based on kernel version. For RHEL, the solution is well decribed here:
http://www.oracle.com/technetwork/server-storage/linux/asmlib/rhel6-1940776.html
https://access.redhat.com/solutions/315643
Just to remember: After the kernel change, a relink of your Oracle Home is higly recommended.
Have a nice day!
Matheus.
Another “The problem -> the fix” post. 🙂
# KB:
Upgrade Recovery Catalog fails with RMAN-07539: insufficient privileges (Doc ID 1915561.1)
Unpublished Bug 17465689 – RMAN-6443: ERROR UPGRADING RECOVERY CATALOG
# Problem
[oracle@databasesrvr dbs]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 21 14:17:09 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: MYDB (not mounted) RMAN> connect catalog catalog_mydb/catalog_mydb@catalogdb connected to recovery catalog database PL/SQL package CATALOG_MYDB.DBMS_RCVCAT version 11.02.00.03 in RCVCAT database is too old RMAN> upgrade catalog RMAN-00571: ========================================================= RMAN-00569: ============== ERROR MESSAGE STACK FOLLOWS ============== RMAN-00571: ========================================================= RMAN-07539: insufficient privileges to create or upgrade the catalog schema RMAN> exit
# Solution
– Connect on the catalog database with the 12c (local) OH:
(and don’t worry about the error on alter session).
[oracle@databasesrvr dbs]$ sqlplus sys/magicpass@catalogdb as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 21 14:21:02 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @?/rdbms/admin/dbmsrmansys.sql
alter session set "_ORACLE_SCRIPT" = true
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
alter session set "_ORACLE_SCRIPT" = false
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
– Then try to upgrade catalog again:
[oracle@databasesrvr dbs]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 21 14:21:27 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: MYDB (not mounted) RMAN> connect catalog catalog_mydb/catalog_mydb@catalogdb connected to recovery catalog database PL/SQL package CATALOG_MYDB.DBMS_RCVCAT version 11.02.00.03 in RCVCAT database is too old RMAN> upgrade catalog; recovery catalog owner is CATALOG_MYDB enter UPGRADE CATALOG command again to confirm catalog upgrade RMAN> upgrade catalog; recovery catalog upgraded to version 12.01.00.02 DBMS_RCVMAN package upgraded to version 12.01.00.02 DBMS_RCVCAT package upgraded to version 12.01.00.02.
Matheus.
After some disk and a instance of RAC lost, the database was stuck with ORA-15081.
A recover was needed. #StayTheTip
# Error
dbsrvr:/home/oracle>sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 29 19:51:37 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1.3462E+10 bytes Fixed Size 2239232 bytes Variable Size 7214204160 bytes Database Buffers 6241124352 bytes Redo Buffers 4489216 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-15081: failed to submit an I/O operation to a disk
# Solution
SQL> recover database; Media recovery complete. SQL> alter database open; Database altered.
Be happy with this! 😀
Matheus.
# Error/Alert
Errors in file /oracle/diag/rdbms/mydb/mydb2/trace/mydb2_reco_26083546.trc: ORA-02062: distributed recovery received DBID e450df78, expected 0311e884
# Solution
begin commit; for d in (select local_tran_id from dba_2pc_pending) loop dbms_transaction.purge_lost_db_entry( d.local_tran_id ); commit; end loop; end; /
Matheus.
Let’s make it simple to solve the problem:
# Error:
SQL> alter database open; alter database open * ERROR at line 1: ORA-19751: could not create the change tracking file ORA-19750: change tracking file: '+DGDATA/mydb/changetracking/ctf.470.859997781' ORA-17502: ksfdcre:1 Failed to create file +DGDATA/mydb/changetracking/ctf.470.859997781 ORA-17501: logical block size 4294967295 is invalid ORA-15001: diskgroup "DGDATA" does not exist or is not mounted ORA-17503: ksfdopn:2 Failed to open file +DGDATA/mydb/changetracking/ctf.470.859997781 ORA-15001: diskgroup "DGDATA" does not exist or is not mounted ORA-15001: diskgroup "DGDATA" does not exist or is not mounted
# Solution:
SQL> alter database disable BLOCK CHANGE TRACKING; Database altered. SQL> alter database open; Database altered.
Then, after everything be OK, you fix the situation recrating a BCTF:
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DGDATANEW';
MTFBWU!
Matheus.
# Error:
dbsrvr1:/home/oracle>srvctl start database -d mydb PRCR-1079 : Failed to start resource ora.mydb.db CRS-5017: The resource action "ora.mydb.db start" encountered the following error: ORA-27154: post/wait create failed ORA-27300: OS system dependent operation:semget failed with status: 28 ORA-27301: OS failure message: No space left on device ORA-27302: failure occurred at: sskgpcreates . For details refer to "(:CLSN00107:)" in "/grid/product/11.2.0.4/log/dbsrvr2/agent/crsd/oraagent_oracle/oraagent_oracle.log". CRS-2674: Start of 'ora.mydb.db' on 'dbsrvr2' failed CRS-2632: There are no more servers to try to place resource 'ora.mydb.db' on that would satisfy its placement policy
Seems the weeror is happening on dbsrvr2, right?
The doc below talks more about the error and the semaphores calculation:
Database Startup Fails with ORA-27300: OS system dependent operation:semget failed with status: 28 (Doc ID 949468.1)
Let’s make an adjust here:
[root@dbsrvr2 ~]# cat /etc/sysctl.conf |grep sem kernel.sem = 250 32000 100 142 [root@dbsrvr2 ~]# vi /etc/sysctl.conf [root@dbsrvr2 ~]# cat /etc/sysctl.conf |grep sem kernel.sem = 250 32000 100 256 [root@dbsrvr2 ~]# sysctl -p
And try again:
dbsrvr1:/home/oracle>srvctl start database -d mydb dbsrvr1:/home/oracle>
Well done! 😀
Matheus.