Oracle : Convert TIMESTAMP to SCN and SCN to TIMESTAMP

Oracle : Convert TIMESTAMP to SCN and SCN to TIMESTAMP

In many recovery scenario we need to know our SCN and timestamps.

We can convert this by using the following function

SCN_TO_TIMESTAMP

TIMESTAMP_TO_SCN

We can use this function with help of dual functions. Example of using this is below

1. Convert SCN to Timestamp

SQL> select scn_to_timestamp(2011955) from dual;

SCN_TO_TIMESTAMP(2011955)-----------------------------------------------------05-SEP-18 12.46.20.000000000 PM

2. Convert Timestamp to SCN

SQL> select timestamp_to_scn(to_timestamp('05-09-2018 12:46:21','dd-mm-yyyy hh24:mi:ss')) scn from dual;

SCN----------2011955

Oracle RAC : CRS-4995: The command 'Delete resource' is invalid in crsctl. Use srvctl for this command

Issue :

$crsctl delete resource ora.RACTEST.db
CRS-4995: The command 'Delete resource' is invalid in crsctl. Use srvctl for this command.

Solution

Add “-unsupported” option to the command.  It should apply to “crsctl modify resource” as well.

Eg:
crsctl delete resource ora.RACTEST.db -unsupported

Oracle RMAN : Run Full Backup with Archivelog and Control file

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
  BACKUP
  FORMAT '/opt/sev1/full_backup/%d_%T_%s_%p_FULL'
  DATABASE
  CURRENT CONTROLFILE
  FORMAT '/opt/sev1/full_backup/%d_C_%T_%u'
  PLUS ARCHIVELOG
  FORMAT '/opt/sev1/full_backup/%d_A_%T_%u_s%s_p%p';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

oracle : Scheduling ASH reports through Crontab

I was asked to capture ASH reports every 5 minutes for an ongoing Database issue. Below is the process to schedule it through Crontab

Script to capture the ASH reports every 5 Minutes

$ cat ash.ksh
#!/bin/bash
export TZ=US/Central

dateString=`date +%d-%b-%Y_%H:%M:%S`
sqlplus -s / as sysdba << EOD1
define report_type = 'html'
define begin_time = '-5'
define duration = ''
define report_name = '/u01/user/local/reports/ashrpt.${dateString}.html'
@?/rdbms/admin/ashrpt
exit
EOF

To run it through Cron

0,5,10,15,20,22,25,30,32,35,40,45,50,55 * * * * /u01/user/local/ash/ash.ksh > /u01/user/local/reports/log/ash_collect.log 1>/dev/null 2>&1

Crontab – Quick Reference

Setting up cron jobs in Unix, Solaris & Linux
cron is a Unix, solaris, Linux utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon.

What is cron ? – Cron is a daemon which runs at the times of system boot from /etc/init.d scripts. If needed it can be stopped/started/restart using init script or with command service crond start in Linux systems.

Crontab Commands

export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e    Edit crontab file, or create one if it doesn’t already exist.
crontab -l    crontab list of cronjobs , display crontab file contents.
crontab -r    Remove your crontab file.
crontab -v    Display the last time you edited your crontab file. (This option is only available on a few systems.)

Crontab file
Crontab syntax :

A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

# ┌───────────── minute (0 - 59)
# │ ┌───────────── hour (0 - 23)
# │ │ ┌───────────── day of the month (1 - 31)
# │ │ │ ┌───────────── month (1 - 12)
# │ │ │ │ ┌───────────── day of the week (0 - 6) (Sunday to Saturday;
# │ │ │ │ │                                   7 is also Sunday on some systems)
# │ │ │ │ │
# │ │ │ │ │
# * * * * * command to execute

* in the value field above means all legal values as in braces for that column.

The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range)

Crontab Examples

A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 5:15 PM.

15     17     *     *     *         chmod 777 /home/user/files/*

Crontab every hour

This is most commonly used for running cron every hour and executing a command after an interval of one hour.

crontab format every hour is simple to have hour field as *  which runs every hour as the clock switches to new hour.  if you want to run it at the beginning of hour the minute filed needs to be 0 or any other minutes when you want to run it at a specific minute of the hour.
cron every hour to run at the beginning of the hour.
00     *     *     *     *         chmod 777 /home/user/files/*

cron every hour to run at 15 minute of an hour..
15     *     *     *     *         chmod 777 /home/user/files/*

Cron every minute

To run cron every minute keep the minutes field as * , as minute changes to new minute cron will be executed every minute.  if you want to run it continuously every hour then the hour field also needs to have value of  * .

*     *     *     *     *         chmod 777 /home/user/files/*

if you want to run a script every minute at specific hour, change the value of hour field to specific value such as 11th hour.

*     11     *     *     *          chmod 777 /home/user/files/*

More crontab examples

Changing the parameter values as below will cause this command to run at different time schedule below :
min hour day/month month day/week Execution time
30 0 1 1,6,12 * — 00:30 Hrs  on 1st of Jan, June & Dec.

0 20 * 10 1-5 –8.00 PM every weekday (Mon-Fri) only in Oct.

0 0 1,10,15 * * — midnight on 1st ,10th & 15th of month

5,10 0 10 * 1 — At 12.05,12.10 every Monday & on 10th of every month

Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

Crontab Environment

cron invokes the command from the user’s HOME directory with the shell, (/usr/bin/sh).
cron supplies a default environment for every shell, defining:

HOME=user’s-home-directory
LOGNAME=user’s-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

Disable Email

By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .

>/dev/null 2>&1

Generate log file

To collect the cron execution execution log in a file :

15 17 * * * chmod 777 /home/user/file/* > /home/user/file/perm.log

Crontab file location

User crontab files are stored by the login names in different locations in different Unix and Linux flavors. These files are useful for backing up, viewing and restoring but should be edited only with crontab command by the users.

• Mac OS X
/usr/lib/cron/tabs/
• BSD Unix 
/var/cron/tabs/
• Solaris, HP-UX, Debian, Ubuntu
/var/spool/cron/crontabs/
• AIX, Red Hat Linux, CentOS, Ferdora
/var/spool/cron/

Message file RMAN.msb not found

Was getting below error when I ran rman


$ rman target /
Message file RMAN<lang>.msb not found
Verify that ORACLE_HOME is set properly


ORACLE_HOME was set correctly and other environments were set as well After looking further into this, we found the below files missing


$ORACLE_HOME/rdbms/mesg/rmanus.msg
$ORACLE_HOME/rdbms/mesg/rmanus.msb


Copied those two files from another working server and the issue got resolved.

Oracle : ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled

ACL creation failing with

ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled


BEGIN 
    DBMS_NETWORK_ACL_ADMIN.create_acl(acl => 'Resolve_Access.xml',
         description  => 'Resolve Network Access using UTL_INADDR', 
                                      principal    => 'SYS', 
                                      is_grant     => TRUE, 
                                      privilege    => 'resolve', 
                                      start_date   => NULL, 
                                      end_date     => NULL 
                                                          ); 

    COMMIT; 
END; 
/
BEGIN
*
ERROR at line 1:
ORA-30554: function-based index XDB.XDB$ACL_XIDX is disabled
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 258
ORA-06512: at line 2

Fix :

SQL> select owner, index_name, status, domidx_status, domidx_opstatus,funcidx_status from dba_indexes where domidx_opstatus is not null;

OWNER                          INDEX_NAME                     STATUS   DOMIDX_STATU DOMIDX FUNCIDX_
------------------------------ ------------------------------ -------- ------------ ------ --------
XDB                            XDBHI_IDX                      VALID    VALID        VALID  ENABLED
XDB                            XDB$ACL_XIDX                   VALID    VALID        VALID  DISABLED

SQL> alter index XDB.XDB$ACL_XIDX rebuild;

Index altered.

SQL> select owner, index_name, status, domidx_status, domidx_opstatus,funcidx_status from dba_indexes where domidx_opstatus is not null;

OWNER                          INDEX_NAME                     STATUS   DOMIDX_STATU DOMIDX FUNCIDX_
------------------------------ ------------------------------ -------- ------------ ------ --------
XDB                            XDBHI_IDX                      VALID    VALID        VALID  ENABLED
XDB                            XDB$ACL_XIDX                   VALID    VALID        VALID  ENABLE

No Rerun the Create ACL command

BEGIN 
    DBMS_NETWORK_ACL_ADMIN.create_acl(acl => 'Resolve_Access.xml',
         description  => 'Resolve Network Access using UTL_INADDR', 
                                      principal    => 'SYS', 
                                      is_grant     => TRUE, 
                                      privilege    => 'resolve', 
                                      start_date   => NULL, 
                                      end_date     => NULL 
                                                          ); 

    COMMIT; 
END; 

PL/SQL procedure successfully completed.

Oracle : Check who is locking User

There are different ways to find who is locking the USER depending on what level of auditing is set on the database

For OS level Auditing

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      OS

$cd <audit-log-location>
$cat *.aud | grep -i <user> | grep 1017

For DB level Auditing

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB


set lines 200
set pages 200
column USERNAME format a12
column OS_USERNAME format a12
column USERHOST format a25
column EXTENDED_TIMESTAMP format a40

SELECT USERNAME, OS_USERNAME, USERHOST, EXTENDED_TIMESTAMP
FROM SYS.DBA_AUDIT_SESSION WHERE returncode != 0 and username = '&Account_Locked'
and EXTENDED_TIMESTAMP > (systimestamp-1) order by 4 desc
/

ORACLE RAC : TERMINATING THE INSTANCE DUE TO ERROR 304

After refreshing my QA database using RMAN DUPLICATE, my instance startup was failing with below error


USER (ospid: 60897): terminating the instance due to error 304
Instance terminated by USER, pid = 60897
Wed Mar 06 02:14:34 2019
Starting ORACLE instance (normal)

Looking into the spfile, I noticed the the database was pulling wrong instance_number and thread numbers even though the DB configuration was correct

$ srvctl config database -d oradb
Database unique name: oradb
Database name: oradb
Oracle home: /opt/app/oradb/oracle/product/11.2.0.4
Oracle user: oradb
Spfile: +oradb_DATA/oradb/spfileoradb.ora
Domain: db.abc.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: oradb
Database instances: oradb1,oradb2
Disk Groups: oradb_DATA,oradb_FRA,oradb_REDO1,oradb_REDO2
Mount point paths: 
Services: oradb_1_2.db.abc.com,oradb_2_1.db.abc.com
Type: RAC
Database is administrator managed

From the pfile I created from the current spfile I could see 

*.instance_number=2
*.thread=2

To resolve this, bring down the complete database and just start the failing instance, in our case instance 1

srvctl start instance -d oradb -i oradb1

Once the instance is started, login to SQL and run below

SQL> alter system set instance_number=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set thread=1 scope=spfile sid='oradb1';

System altered.

SQL> alter system set undo_tablespace='UNDO01' sid='oradb1';

System altered.

shutdown the instance and start the complete database

SQL> shutdown immediate

srvctl start database -d oradb


Hope this resolves your issue. 

Oracle : Check Hidden Parameter Values

You can run the below query to find the value of the Hidden parameters set on the database

undef 1 
set lines 150 
column value for a40 
col Parameter for a50 
col "Default Value" for a13 
col "Session Value" for a13 
col "Instance Value" for a13 
col IS_SESSION_MODIFIABLE for a25 
col IS_SYSTEM_MODIFIABLE for a24 

SELECT distinct a.ksppinm "Parameter", b.KSPPSTDF "Default Value", 
b.ksppstvl "Session Value", 
c.ksppstvl "Instance Value", 
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE, 
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE 
FROM x$ksppi a, 
x$ksppcv b, 
x$ksppsv c 
WHERE a.indx = b.indx 
AND a.indx = c.indx 
AND a.ksppinm LIKE '_rollback_segment_%' 
/ 



Parameter                                          Default Value Session Value Instance Valu IS_SESSION_MODIFIABLE     IS_SYSTEM_MODIFIABLE

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

_rollback_segment_count                            FALSE         4000          4000          FALSE                     IMMEDIATE


_rollback_segment_initial                          TRUE          1             1             FALSE                     FALSE

Oracle : RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

The backups were failing with below error after a DB refresh was done

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 02/26/2019 18:31:13
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

Fix : 

We had to register the database by connecting to the catalog

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 27 14:58:42 2019

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

connected to target database: ABSPD2 (DBID=3485384910)

RMAN> connect catalog rman_s2rom1d2/Apr_of_2016@p1nbu1d5.db.att.com;

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

This fixed the issue and the backups started running again. 

Oracle :Cross Platform DB migration; Checking Endian Format

Doing cross platform migration using RMAN

Starting from 10gR2, Oracle introduced the next magical feature of RMAN its ability to convert the database from one platform to a different one which shares the same endian format.  This feature is called Cross-Platform Database Migration.  

To convert the database from one platform to another, the endian format of both databases should be the same

To know if we can use RMAN for the migration, We cam query v$transportable_platform view which shows the endian format for almost all Oracle platforms: 

SQL> select * from v$transportable_platform;

PLATFORM_ID PLATFORM_NAME                  ENDIAN_FORMAT
----------- ------------------------------ --------------
1           Solaris[tm] OE (32-bit)          Big
2           Solaris[tm] OE (64-bit)          Big
7           Microsoft Windows NT             Little
10          Linux IA (32-bit)                Little
6           AIX-Based Systems (64-bit)       Big
3           HP-UX (64-bit)                   Big
5           HP Tru64 UNIX                    Little
4           HP-UX IA (64-bit)                Big
11          Linux IA (64-bit)                Little
15          HP Open VMS                      Little
8           Microsoft Windows IA (64-bit)    Little
9           IBM zSeries Based Linux          Big
13          Linux 64-bit for AMD             Little
16          Apple Mac OS                     Big
12          Microsoft Windows 64-bit for AMD Little



The v$database data dictionary view also adds two columns, platform ID and platform name:


select  name, platform_id, platform_name from v$database;

NAME      PLATFORM_ID PLATFORM_NAME
--------- ----------- ---------------------

P3OMS1D5           13 Linux x86 64-bit

To transport a tablespace from one platform to another, datafiles on different platforms must be in the same endian format (byte ordering).

The pattern for byte ordering in native types is called endianness. There are only two main patterns, big endian and little endian. Big endian means the most significant byte comes first, and little endian means the least significant byte comes first.

Oracle : Difference between .trc and .trm

Oracle *.trm *.trc

A new type of files with .trm extension are created in ADR_HOME/trace directory in Oracle 11g databases. The following are some facts:

• From 11g onwards, all the trace files are found in ADR_HOME/trace directory.
• There is usually one “.trm” file for each “.trc” file.
• The files located in the TRACE directory, with the “.trm” extensions, are called Trace
• Metadata files. The metadata in .trm files describe the trace records stored inside of .trc trace files.
• The Trace metadata allows tools, such as ADRCI, to post process the trace information.
• By deleting the .trm file, you make the .trc file unusable for post processing from ADR using ADRCI tool.