Wednesday, October 6, 2021

DBMS_SCHEDULER.

Default time zone change.

DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1)

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='DD/MM/YYYY HH24:MI:SS TZR TZD';

Session altered.

SQL> select dbms_scheduler.stime from dual;

STIME

---------------------------------------------------------------------------

06/10/2021 07:09:12 CST6CDT CDT

SELECT * FROM dba_scheduler_global_attribute WHERE attribute_name = 'DEFAULT_TIMEZONE';

SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','Asia/Colombo');

Thursday, September 16, 2021

ADRCI : DIA-48216: Field Len Exceeds Max Field Length

Detect place :
1. Find path:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME LIKE 'Diag Trace%';

2. Find problematic line:
sed ‘s/$.*$<msg.*$<msg.*$/\1\2/' < log.xml | diff -w log.xml –

3. Remove it :

RMAN Restore Preview

$ rman target /

RMAN> list backup;

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

15526 Full 6.77M DISK 00:00:01 15-SEP-21

BP Key: 15615 Status: AVAILABLE Compressed: YES Tag: TAG20210915T234616

Piece Name: +RECO/SSLCDB/BACKUP_FILES/c-4188050246-20210915-05

SPFILE Included: Modification time: 15-SEP-21

SPFILE db_unique_name: SSLCDB

Control File Included: Ckp SCN: 67374431842 Ckp time: 15-SEP-21

RMAN>

run{

set until scn 67374431842;

restore database preview;

}

List of Archived Log Copies for database with db_unique_name SSLCDB

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

Key Thrd Seq S Low Time

------- ---- ------- - ---------

92560 1 47166 A 15-SEP-21

Name: +RECO/SSLCDB/ARCHIVELOG/2021_09_15/thread_1_seq_47166.3007.1083368767

92562 1 47167 A 15-SEP-21

Name: +RECO/SSLCDB/ARCHIVELOG/2021_09_16/thread_1_seq_47167.3005.1083369733

recovery will be done up to SCN 67374431842

Media recovery start SCN is 67374208371

Recovery must be done beyond SCN 67374426191 to clear datafile fuzziness

Finished restore at 16-SEP-21

RMAN>

Restore Preview Reports RMAN-05119 and Recover Database Reports RMAN-06054 ( Multi-tenant ) (Doc ID 2233445.1)

Error seems to be a wrong notification which misleads the recovery process. Ignore the error (or apply the patch 20315311) and proceed with the 'alter database open' after the recover database.

Sample output of the errors

Assuming you have valid backup of database and archivelogs, Oracle recommends validating the integrity of the backup by running 'restore database preview'.

Due to a code defect, 'restore database preview' command returns the error RMAN-05119 although valid backups exist.

Sunday, August 29, 2021

nls length semantics must be set to char

CHAR length semantics are required for the AL32UTF8 character set.

https://www.ibm.com/support/pages/char-length-semantics-are-required-al32utf8-character-set

The following command can be executed when logged in as SYSTEM to make this change:

alter system set NLS_LENGTH_SEMANTICS = CHAR scope = both;

Renaming or Moving Oracle Files

https://oracle-base.com/articles/misc/renaming-or-moving-oracle-files

SQL> SELECT member FROM v$logfile;

MEMBER
--------------------------------------------------------------------------------

/u02/oradata/PRODCDB/redo010.log
/u01/oradata/PRODCDB/redo011.log
/u02/oradata/PRODCDB/redo020.log
/u01/oradata/PRODCDB/redo021.log
/u02/oradata/PRODCDB/redo030.log
/u01/oradata/PRODCDB/redo031.log

SQL> shutdown immediate

HOST mv /u01/oradata/PRODCDB/redo011.log /u02/oradata/PRODCDB/redo011.log

HOST mv /u01/oradata/PRODCDB/redo021.log /u02/oradata/PRODCDB/redo021.log

HOST mv /u01/oradata/PRODCDB/redo031.log /u02/oradata/PRODCDB/redo031.log

SQL> STARTUP MOUNT

ALTER DATABASE RENAME FILE '/u01/oradata/PRODCDB/redo011.log' TO '/u02/oradata/PRODCDB/redo011.log';

ALTER DATABASE RENAME FILE '/u01/oradata/PRODCDB/redo021.log' TO '/u02/oradata/PRODCDB/redo021.log';

ALTER DATABASE RENAME FILE '/u01/oradata/PRODCDB/redo031.log' TO '/u02/oradata/PRODCDB/redo031.log';

SQL> ALTER DATABASE OPEN;

SQL> SELECT member FROM v$logfile;

MEMBER
--------------------------------------------------------------------------------

/u02/oradata/PRODCDB/redo010.log
/u02/oradata/PRODCDB/redo011.log
/u02/oradata/PRODCDB/redo020.log
/u02/oradata/PRODCDB/redo021.log
/u02/oradata/PRODCDB/redo030.log
/u02/oradata/PRODCDB/redo031.log

Wednesday, August 25, 2021

Startup Fails With ORA-01012: Not Logged On

Problem Description
Users cant able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance. But when we startup database it shall throw error ORA-01081: cannot start already-running .

[oracle@mezon01 ~]$ sqlplus sys/System_2018@IIRDB

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:03:07 2018

ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server

Enter user-name: sys
Enter password:
ERROR:
ORA-00020: maximum number of processes (1500) exceeded

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@mezon01 ~]$ sqlplus -prelim “/as sysdba”

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:04:06 2018

SQL> show parameter sga_t;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> startup;
ORA-01012: not logged on
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> show parameter sga_max;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> EXİT
SP2-0042: unknown command “EXİT” – rest of line ignored.
SQL> exit
Disconnected from ORACLE
[oracle@mezon01 ~]$ ps -ef | grep IIRDB
oracle 6096 1 0 08:05 ? 00:00:00 ora_pmon_IIRDB
oracle 6098 1 0 08:05 ? 00:00:00 ora_psp0_IIRDB
oracle 6136 1 1 08:05 ? 00:00:01 ora_vktm_IIRDB
oracle 6140 1 0 08:05 ? 00:00:00 ora_gen0_IIRDB
oracle 6142 1 0 08:05 ? 00:00:00 ora_diag_IIRDB
oracle 6144 1 0 08:05 ? 00:00:00 ora_dbrm_IIRDB
oracle 6146 1 0 08:05 ? 00:00:00 ora_ping_IIRDB
oracle 6148 1 0 08:05 ? 00:00:00 ora_acms_IIRDB
oracle 6150 1 0 08:05 ? 00:00:00 ora_dia0_IIRDB
oracle 6152 1 0 08:05 ? 00:00:00 ora_lmon_IIRDB
oracle 6162 1 0 08:05 ? 00:00:00 ora_lmd0_IIRDB
oracle 6164 1 0 08:05 ? 00:00:00 ora_rms0_IIRDB
oracle 6166 1 0 08:05 ? 00:00:00 ora_lmhb_IIRDB
oracle 6172 1 4 08:05 ? 00:00:04 ora_mman_IIRDB
oracle 6174 1 0 08:05 ? 00:00:00 ora_dbw0_IIRDB
oracle 6176 1 0 08:05 ? 00:00:00 ora_dbw1_IIRDB
oracle 6178 1 0 08:05 ? 00:00:00 ora_dbw2_IIRDB
oracle 6180 1 0 08:05 ? 00:00:00 ora_dbw3_IIRDB
oracle 6182 1 0 08:05 ? 00:00:00 ora_lgwr_IIRDB
oracle 6184 1 0 08:05 ? 00:00:00 ora_ckpt_IIRDB
oracle 6186 1 0 08:05 ? 00:00:00 ora_smon_IIRDB
oracle 6188 1 0 08:05 ? 00:00:00 ora_reco_IIRDB
oracle 6190 1 0 08:05 ? 00:00:00 ora_rbal_IIRDB
oracle 6192 1 0 08:05 ? 00:00:00 ora_asmb_IIRDB
oracle 6194 1 0 08:05 ? 00:00:00 ora_mmon_IIRDB
oracle 6198 1 0 08:05 ? 00:00:00 ora_mmnl_IIRDB
oracle 6200 1 0 08:05 ? 00:00:00 ora_d000_IIRDB
oracle 6202 1 0 08:05 ? 00:00:00 ora_s000_IIRDB
oracle 6204 1 0 08:05 ? 00:00:00 ora_mark_IIRDB
oracle 6206 1 0 08:05 ? 00:00:00 ora_ocf0_IIRDB
oracle 6325 1 0 08:05 ? 00:00:00 ora_o000_IIRDB
oracle 10089 28710 0 08:07 pts/1 00:00:00 grep IIRDB
oracle 20902 25965 0 May23 ? 00:04:20 /oracle/product/11.2.0.4/db/jdk/bin/java -server -Xmx384M -XX:MaxPermSize=400M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/oracle/product/11.2.0.4/db -Doracle.home=/oracle/product/11.2.0.4/db/oc4j -Doracle.oc4j.localhome=/oracle/product/11.2.0.4/db/mezon01_IIRDB/sysman -DEMSTATE=/oracle/product/11.2.0.4/db/mezon01_IIRDB -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/jazn.xml -Djava.security.policy=/oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/java2.policy -Djavax.net.ssl.KeyStore=/oracle/product/11.2.0.4/db/sysman/config/OCMTrustedCerts.txt-Djava.security.properties=/oracle/product/11.2.0.4/db/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/oracle/product/11.2.0.4/db/mezon01_IIRDB -Dsysman.md5password=true -Drepapi.oracle.home=/oracle/product/11.2.0.4/db -Ddisable.checkForUpdate=true -Doracle.sysman.ccr.ocmSDK.websvc.keystore=/oracle/product/11.2.0.4/db/jlib/emocmclnt.ks -Dice.pilots.html4.ignoreNonGenericFonts=true -Djava.awt.headless=true -jar /oracle/product/11.2.0.4/db/oc4j/j2ee/home/oc4j.jar -config /oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/server.xml
oracle 25965 1 0 May23 ? 00:00:12 /oracle/product/11.2.0.4/db/perl/bin/perl /oracle/product/11.2.0.4/db/bin/emwd.pl dbconsole /oracle/product/11.2.0.4/db/mezon01_IIRDB/sysman/log/emdb.nohup

————————————————————————————————————————————-

[oracle@mezon01 ~]$ sysresv
IPC Resources for ORACLE_SID “IIRDB” :
Shared Memory:
ID KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64
Semaphores:
ID KEY
136445992 0xf1e96e54
136478764 0xf1e96e55
136511533 0xf1e96e56
136544302 0xf1e96e57
136577071 0xf1e96e58
136609840 0xf1e96e59
136642609 0xf1e96e5a
136675378 0xf1e96e5b
136708147 0xf1e96e5c
Oracle Instance alive for sid “IIRDB”
[oracle@mezon01 ~]$ ipcrm -m 100270098
[oracle@mezon01 ~]$ ipcrm -m 100302867
[oracle@mezon01 ~]$ ipcrm -m 100335643
[oracle@mezon01 ~]$
[oracle@mezon01 ~]$
Changes
Oracle has been forcefully shutdown at OS level or crashed.

CAUSE
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.

The command
ps -ef | grep $ORACLE_SID

shows no processes but with ORACLE_SID set the Oracle ‘sysresv’ utility shows a shared memory segment for a non-existing instance, e.g.

$ sysresv

IPC Resources for ORACLE_SID “IIRDB” :

Shared Memory:
ID KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64

Semaphores:
ID KEY
No semaphore resources used
Oracle Instance not alive for sid “IIRDB”

Solution
On OS level, remove the orphaned shared memory segment using:

ipcrm -m <problem shared memory id>

ipcrm -m 100270098
ipcrm -m 100302867
ipcrm -m 100335643

$sqlplus ‘/as sysdba’

Connected to an idle instance.

SQL> startup Oracle instance started
Total System Global Area   10689474560 bytes
Fixed Size                     2237776 bytes
Variable Size               6375344816 bytes
Database Buffers            4294967296 bytes
Redo Buffers                  16924672 bytes
Oracle Instance Started.
Oracle Database Opened.

ORA-01102: cannot mount database in EXCLUSIVE mode (During database startup)

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 79693392 bytes

Database Buffers 201326592 bytes

Redo Buffers 2973696 bytes

ORA-01102: cannot mount database in EXCLUSIVE mode

Cause: There are some client shadow processes hanging. Although the lk<SID> file is deleted the hanging processes still have a lock on the open file handle. This prevents the database to startup although a new lk<SID> file can be created successfully.

An oracle process (background or shadow process) that exists while the instance is not started (crashed or not cleanly stopped) can have a lock on a file while this file is actually removed from the system. This is because on UNIX there is still a lock on the open file handle.

Solution:

Verify if there are existing oracle processes for the database:

[oracle@servername ~]$ ps -ef | grep $ORACLE_SID

oracle 1525 1 0 10:13 ? 00:00:03 ora_j000_GRIDDB3

oracle 1527 1 0 10:13 ? 00:00:01 ora_j001_GRIDDB3

oracle 4198 1 0 10:27 ? 00:00:00 oracleGRIDDB3 (LOCAL=NO)

oracle 7479 1 0 May30 ? 00:00:00 oracleGRIDDB3 (LOCAL=NO)

Kill the hanging processes to release the lock on the file handle:

[oracle@servername ~]$ kill -9 `ps -ef | grep $ORACLE_SID | grep -v grep | awk '{print $2}'`

eg: kill -9 `ps -ef | grep testcdb | grep -v grep | awk '{print $2}'`

[oracle@sservername ~]$ ps -ef | grep $ORACLE_SID

oracle 11990 11927 0 10:50 pts/1 00:00:00 grep GRIDDB3

Oracle Database and Linux Support Articles