How to change the time zone of time in oracle database

 we have requirement to change the time zone format of the data (UTC to CST)  from the column and oracle provided solution to convert the time zones.

Here is the function that you should use to convert the timezone:

to_char(NEW_TIME(SCHEDULED_TIME,'GMT','CDT'), 'dd-mm-yy hh:mi:ss')

old time zone data:

WORK_NAME JOB_NAME  NEWTIME_TEST

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

servername124        FINAL            06-09-22 03:29:00 PM

new  time zone data:

WORK_NAME JOB_NAME  NEWTIME_TEST

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

servername124         FINAL            07-09-22 10:29:00 AM

ORA-39181: Only partial table data may be exported due to fine grain access control on

 

Issue:

I got the below error while doing the export of few tables.

. exported "admin"."REG_CONFIG"                          6.507 KB       1 rows

. . exported "admin"."TEST_TO_UPLOAD"                    5.156 KB       3 rows

ORA-39181: Only partial table data may be exported due to fine grain access control on "admin"."fiel_TAB"

Root cause:

Looks like fine grained access policy has been enabled on those tables and need to granted the access .

Solution:

select * from dba_policies where OBJECT_OWNER='ADMIN' AND OBEJCT_NAME='FIEL_TAB';

grant EXEMPT ACCESS POLICY to  ADMIN;

SAML error: invalid_grant: BBSTS50056: The user account {EmailHidden} does not exist in

 Issue:

we are using SSO on aws  Redshift database login and we got the below error and I tried to connect to Redshift using SSO and got the below error,but local accounts able to make connections to database.Then found the issue Related to SAML assertion.

Something unusual has occurred to cause the driver to fail. Please report this exception.

  SAML error: invalid_grant: BBSTS50056: The user account {EmailHidden} does not exist in the 45f81a76-3477-44dd-b547-fgb3b6a146543 directory. 

To sign into this application, the account must be added to the directory. Trace ID: 45dd7dae-8355-45bc-b4f9-43df120f888 Correlation ID: 1fed5c1a-7c4f-4ffc-b234-672d069c15c1 Timestamp: 2022-02-28 05:28:14Z 

 Impact:

unable to login to Redshift database using SSO

Root cause:

Then found the issue Related to SAML assertion. Users unable to connect to Redshift DB because  the respective  AD account has been deleted on Azure Active directory.

looks like the AD accounts marked as deleted on AZURE Active directory.

Solution

we asked the cloud admin to enable the AD accounts on Azure active directory and issue resolved.

script to find out which database actively used in AWS redshift

 To identify which databases are actively used, you can run this SQL command as a superuser.

SELECT database,

  COUNT(*) as num_queries,

  AVG(DATEDIFF(sec,starttime,endtime)) avg_duration,

  MIN(starttime) as oldest_ts,

  MAX(endtime) as latest_ts

FROM stl_query

WHERE userid > 1

GROUP BY database; 

script to find out owner of the schema in aws Redshift

 script to find out owner of the schema in aws Redshift:

select nspname as schema, usename as owner

from pg_catalog.pg_namespace, pg_catalog.pg_user

where pg_catalog.pg_namespace.nspowner = pg_catalog.pg_user.usesysid

and pg_catalog.pg_user.usename ='us_sales';

   schema |  owner

----------+----------

 us_sales | dwuser

(1 row)

script to list all schema in AWS redshift database

 script to list all schema in AWS redshift database:

select s.nspname as table_schema,

       s.oid as schema_id,  

       u.usename as owner

from pg_catalog.pg_namespace s

join pg_catalog.pg_user u on u.usesysid = s.nspowner

order by table_schema;

ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

 issue:

when I tried to register the oracle DB with catalog DB during registration process got the below error.

RMAN> REGISTER DATABASE;

database registered in recovery catalog

starting full resync of recovery catalog

got ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

ORA-06512: at "RMAN.DBMS_RCVCAT", line 7261 during resync

retrying with snapshot controlfile

Creating and using snapshot control file for resync

starting full resync of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03008: error while performing automatic resync of recovery catalog

ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

Cause:

could be the same Db already registered and not properly unregistred ,so that we  got the above error.

Solution:

connect to RMAN catalog DB using Catalog user and  execute the below package.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.11.0.0.0

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

4RMANCAT  READ WRITE

SQL> select 'EXEC DBMS_RCVCAT.UNREGISTERDATABASE('||DB_KEY||','||DBID||');'

  command, resetlogs_time

 from rc_database

 where name = '&db_name';  2    3    4

Enter value for db_name: TESTDB

old   4:  where name = '&db_name'

new   4:  where name = 'TESTDB'

 from rc_database

      *

ERROR at line 3:

ORA-00942: table or view does not exist

SQL> conn rman

Enter password:

Connected.

SQL> select 'EXEC DBMS_RCVCAT.UNREGISTERDATABASE('||DB_KEY||','||DBID||');'

  command, resetlogs_time

 from rc_database

 where name = '&db_name';  

Enter value for db_name: TESTDB

old   4:  where name = '&db_name'

new   4:  where name = 'TESTDB'

COMMAND

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

RESETLOGS

---------

EXEC DBMS_RCVCAT.UNREGISTERDATABASE(85399,987618234);

28-JAN-21

SQL> EXEC DBMS_RCVCAT.UNREGISTERDATABASE(85399,987618234);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

> rman target / catalog rman@RCATDB

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 12 06:11:50 2022

Version 19.6.0.0.0

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

connected to target database: TESTDB (DBID=987618234)

recovery catalog database Password:

connected to recovery catalog database

recovery catalog schema version 19.11.00.00. is newer than RMAN version

RMAN> REGISTER DATABASE ;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN> list db_unique_name all;

List of Databases

DB Key  DB Name  DB ID            Database Role    Db_unique_name

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

86302   TESTDB    987618234        PRIMARY          TESTDB

script to check mysql db user locked status

 SELECT 

    user, host, account_locked

FROM

    mysql.user

WHERE

    user = 'mysqluser' ;

mysql> CREATE USER mysqluser

    -> IDENTIFIED BY '5password67'

    -> ACCOUNT LOCK;

Query OK, 0 rows affected (2.10 sec)

mysql> SELECT

    ->     user, host, account_locked

    -> FROM

    ->     mysql.user

    -> WHERE

    ->     user = 'mysqluser' AND

    ->     host='localhost';

Empty set (0.00 sec)

mysql> SELECT

    ->     user, host, account_locked

    -> FROM

    ->     mysql.user

    -> WHERE

    ->     user = 'mysqluser' ;

+-----------+------+----------------+

| user      | host | account_locked |

+-----------+------+----------------+

| mysqluser | %    | Y              |

+-----------+------+----------------+

1 row in set (0.00 sec)

If the mysql server is performing slow than how to find out the process which is causing problem.

 If the mysql server is performing slow than how to find out the process which is causing problem.

Answer: Best way is to check with: ‘SHOW PROCESSLIST / SHOW ENGINE INNODB STATUS

script to check the uptime of a mysql database server?

SELECT
  VARIABLE_VALUE AS Uptime_seconds,
  NOW() AS "Now",
  NOW() - INTERVAL VARIABLE_VALUE SECOND AS "Up since",
  DATEDIFF(NOW(), NOW() - INTERVAL VARIABLE_VALUE SECOND) AS "Uptime_days"
FROM performance_schema.session_status
WHERE VARIABLE_NAME = 'Uptime';

 mysql> SELECT

    ->   VARIABLE_VALUE AS Uptime_seconds,

    ->   NOW() AS "Now",

    ->   NOW() - INTERVAL VARIABLE_VALUE SECOND AS "Up since",

    ->   DATEDIFF(NOW(), NOW() - INTERVAL VARIABLE_VALUE SECOND) AS "Uptime_days"

    -> FROM performance_schema.session_status

    -> WHERE VARIABLE_NAME = 'Uptime';

+----------------+---------------------+----------------------------+-------------+

| Uptime_seconds | Now                 | Up since                   | Uptime_days |

+----------------+---------------------+----------------------------+-------------+

| 100752         | 2021-12-27 10:02:34 | 2021-12-26 06:03:22.000000 |           1 |

+----------------+---------------------+----------------------------+-------------+

1 row in set (0.01 sec)

mysql> SHOW GLOBAL STATUS LIKE '%UPTIME%';

+---------------------------+--------+

| Variable_name             | Value  |

+---------------------------+--------+

| Uptime                    | 100776 |

| Uptime_since_flush_status | 100776 |

+---------------------------+--------+

2 rows in set (0.00 sec)