Restircted session due to sync filed with ora-65177

Application is unable to connect the database due to restricted session.

sql> show pdbs;

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> col name for a10

SQL> col time for a10

SQL> col cause for a15

SQL> col message for a55

SQL> set linesize 205

SQL> alter session set nls_timestamp_format='dd-mon-yy hh24:mi:ss';

SQL> select time,name,type,cause,status,message from pdb_plug_in_violations where status <>'RESOLVED';

SQL> col username for a10

SQL> select con_id,username,account_status,created,common from cdb_users where username='DMN_APP';

    CON_ID USERNAME   ACCOUNT_STATUS     CREATED   COM

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

         1 DMN_APP  OPEN               15-JUN-24 YES

SQL> alter session set container=ORCL;

Session altered.

SQL>

SQL>select con_id,username,account_status,created,common from cdb_users where username='DMN_APP';

    CON_ID USERNAME   ACCOUNT_STATUS    CREATED   COM

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

         3  DMN_APP   OPEN              04-AUG-24 NO

When the pdb ORCL is being ipened it tries to synchronize the sql between pdb and codb$root .

user was created as common user in root container where as it creates as local user in pdb level with the same name.

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> 

SQL> col name for a10

SQL> col sqlstmt for a40

SQL> 

SQL> alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

Session altered.

SQL> 

SQL> select ctime,sqlstmt,name,flags,opcode,replay# from pdb_sync$ where sqlstmt like 'alter user DMN_APP account unlock%';

CTIME              SQLSTMT                                  NAME            FLAGS      OPCODE     REPLAY#

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

30-oct-24 12:20:32  alter user DMN_APP account unlock      DMN_APP          0          5          21

SQL> delete from PDB_SYNC$ where sqlstmt like ('alter user DMN_APP account unlock%');

1 row deleted.

SQL> commit;

Commit complete.

 

In pdb ORCL

SQL> alter session set container=ORCL;

Session altered.

SQL> select ctime,sqlstmt,name,flags,opcode,replay# from pdb_sync$ where sqlstmt like 'alter user DMN_APP account unlock%';

CTIME              SQLSTMT                                  NAME         FLAGS      OPCOD      EREPLAY#

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

30-oct-24 12:28:42 alter user DMN_APP account unlock      DMN_APP        0          5          21

SQL> delete from PDB_SYNC$ where sqlstmt like 'alter user DMN_APPaccount unlock%';

1 row deleted.

SQL> commit;

Commit complete.

 

reopen the pdb:

alter pluggable database orcl close;

alter pluggable database orcl open;

pdb has open without restrict session , it can appear again . better to drop the local user from pdb.

duplicate db from standy to other server

 Duplicate Testuat

 

$ export ORACLE_SID=Testuat3

$ sqlplus '/as sysdba'

Testuat3 SQL> alter system set cluster_database=FALSE scope=spfile ;

 System altered.

 Testuat3 SQL> exit

 

$ srvctl stop database -d DRTestuat

 Switch to grid and from asmcmd remove all DRTestuat and leave parameter file and password file on ASM and exit to oracle

 $ sqlplus '/as sysdba'

 Testuat3 SQL>startup nomount

 rman target  sys/****@192.156.0.14:1521/DRNTEST auxiliary sys/****@192.156.0.54:1521/Testuat3

 

RMAN> duplicate target database to Testuat from active database;

---

Finished Duplicate Db at 03-OCT-24

RMAN> exit

 

$ sqlplus '/as sysdba'

Testuat3 SQL> alter system set cluster_database=TRUE scope=spfile ;

 

System altered.

 Testuat3 SQL> shut immediate

  

$ srvctl start database -d DRTestuat

select name,open_mode from gv$database;

creating pluggable database 19c

-- CREATE pluggable DATABASE --

[oracle@testdbslvd01 ~]$ . oraenv

ORACLE_SID = [stgcont1] ? testcont1

[oracle@testdbslvd01 ~]$ sqlplus / as sysdba

SYS@ testcont1 SQL> create pluggable database TESTDEV admin user PDBADMIN identified by ********;

Pluggable database created.

SYS@ testcont1 SQL> alter pluggable database TESTDEV open instances=all;

Pluggable database altered.

SYS@ testcont1 SQL> alter session set container = TESTDEV;

Session altered.

SYS@ testcont1 SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

        15 TESTDEV                         READ WRITE NO

SYS@ testcont1 SQL> exit

-- CREATE SERVICE NAME --

[oracle@testdbslvd01 ~]$ srvctl add service -d tevcont-s test_test_srv -pdb TESTDEV -r testcont1,testcont2 -m BASIC -e SELECT -z 180 -w 5

[oracle@testdbslvd01 ~]$ srvctl start service -s test_test_srv-d testcont

[oracle@testdbslvd01 ~]$ srvctl status service -s test_test_srv -d testcont

Service test_test_srv is running on instance(s) testcont1,testcont2

[oracle@testdbslvd01 ~]$

-- CREATE TABLESPACE & USER --

create tablespace USERS;

create tablespace TEST_TBS;

create user TEST_DEV identified by test_test1144;

grant connect to TEST_DEV;

grant resource to TEST_DEV;

grant unlimited tablespace TO TEST_DEV;

alter user TEST_DEV default tablespace TEST_TBS;

alter user TEST_DEV quota unlimited on TEST_TBS;

GRANT CREATE VIEW TO TEST_DEV;

GRANT CREATE TRIGGER TO TEST_DEV;

-- grant access rights to resolve lock issues

GRANT EXECUTE ON DBMS_LOCK TO TEST_DEV;

-- grant access rights to resolve XA related issues:

GRANT SELECT ON PENDING_TRANS$ TO TEST_DEV;

GRANT SELECT ON DBA_2PC_PENDING TO TEST_DEV;

GRANT SELECT ON DBA_PENDING_TRANSACTIONS TO TEST_DEV;

GRANT EXECUTE ON DBMS_XA TO TEST_DEV;

alter profile default LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Postgres commads

 [oracle@Tesdb ~]$ systemctl status postgresql-15

● postgresql-15.service - PostgreSQL 15 database server

   Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)

   Active: active (running) since Thu 2024-02-01 17:45:15 +03; 3 days ago

     Docs: https://www.postgresql.org/docs/15/static/

  Process: 33491 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)

 Main PID: 33500 (postmaster)

   CGroup: /system.slice/postgresql-15.service

           ├─33500 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/

           ├─33503 postgres: logger

           ├─33505 postgres: checkpointer

           ├─33506 postgres: background writer

           ├─33508 postgres: walwriter

           ├─33509 postgres: autovacuum launcher

           ├─33510 postgres: logical replication launcher

           ├─33541 postgres: postgres Tesdb [local] idle

           ├─65961 postgres: postgres Tesdb 172.16.162.240(61004) idle

           ├─69716 postgres: postgres Tesdb 172.16.162.240(63935) idle

           ├─69816 postgres: postgres Tesdb 172.16.162.240(64108) idle

           ├─71388 postgres: postgres postgres 10.28.68.209(49286) idle

           ├─71411 postgres: postgres Tesdb 172.16.162.240(49191) idle

           └─71552 postgres: postgres postgres 10.28.68.215(44922) idle

[oracle@Tesdb ~]$

[oracle@Tesdb ~]$ sudo -i -u postgres

-bash-4.2$

-bash-4.2$

-bash-4.2$ id

uid=26(postgres) gid=26(postgres) groups=26(postgres)

[oracle@Tesdb ~]$ sudo -i -u postgres

-bash-4.2$

postgres-# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

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

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres-# \c Tesdb

You are now connected to database "Tesdb" as user "postgres".

Tesdb-#

Tesdb-#

Tesdb-#

Tesdb-# \l

                                                 List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges

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

 Tesdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +

           |          |          |             |             |            |                 | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +

           |          |          |             |             |            |                 | postgres=CTc/postgres

(4 rows)

Tesdb-# \c

You are now connected to database "Tesdb" as user "postgres".

Tesdb-#

Tesdb-#

Tesdb-# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

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

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Tesdb=# \dt

               List of relations

 Schema |       Name        | Type  |  Owner

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

 public | chat_conversation | table | postgres

 public | employees         | table | postgres

 public | orgnaization      | table | postgres

 public | user              | table | postgres

(4 rows)

Tesdb=# \dt+ employees

                                       List of relations

 Schema |   Name    | Type  |  Owner   | Persistence | Access method |    Size    | Description

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

 public | employees | table | postgres | permanent   | heap          | 8192 bytes |

(1 row)

Tesdb=# select schemaname,relname from pg_stat_user_Tables;

 schemaname |      relname

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

 public     | employees

 public     | user

 public     | chat_conversation

 public     | orgnaization

Tesdb=# select datname,oid from pg_database;

  datname  |  oid

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

 postgres  |     5

 Tesdb    | 16535

 template1 |     1

 template0 |     4

(4 rows)

how to connect database 

-bash-4.2$ psql -U postgres Tesdb

psql (15.5)

Type "help" for help.

Tesdb=#

Tesdb=#

Tesdb=# \conninfo

You are connected to database "Tesdb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

creating os user:

+++++++++++++++++++

h-#

Tesdb-#

Tesdb-# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

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

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Tesdb-# \q

-bash-4.2$

-bash-4.2$

-bash-4.2$ createuser test

-bash-4.2$ psql

psql (15.5)

Type "help" for help.

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

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

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 test      |  

 

 

date and time

select now() -interval '2 hours 30 minutes' as deduct_time;

          deduct_time

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

 2024-02-06 07:39:10.676531+03

(1 row)

select now() as current;

            current

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

script to ddl for table--metadata

 export ORACLE_SID="$1"

export ORACLE_HOME=/u01/db/oracle19c/oraHome_1
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=/u01/db/oracle19c/oraHome_1/lib

 $ORACLE_HOME/bin/sqlplus /nolog << End_SQL >> r_tables.log

 connect / as sysdba

 SET head OFF

SET feed OFF
SET trimspool ON
SET linesize 32767
SET pagesize 32767
SET echo OFF
SET termout OFF
SET verify OFF

 

@r_tables.sql

 

End_SQL

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

more r_tables.sql

spool readwrite_tables.sql
select 'alter table ' || owner || '.' || table_name || ' read write;' from dba_tables where read_only='YES';
spool off

 

spool readonly_tables.sql
select 'alter table ' || owner || '.' || table_name || ' read only;' from dba_tables where read_only='YES';
spool off

create the public synonyms in the target database.

 

1. Prepare the script to create the public synonyms in the target database.

cat header.txt > 13_b_get_public_synonym.pomdir01.sql.out

cat 13_b_get_public_synonym. pomdir01.sql.out | grep "CREATE PUBLIC SYNONYM" | grep -v SELECT  >> create the 13_create_public_synonym.sql

echo "quit" >> create the 13_create_public_synonym.sql

Huge page script

 

awk '/Hugepagesize:/{p=$2} / 0 /{next} / kB$/{v[sprintf("%9d GB %-s",int($2/1024/1024),$0)]=$2;next} {h[$0]=$2} /HugePages_Total/{hpt=$2} /HugePages_Free/{hpf=$2} {h["HugePages Used (Total-Free)"]=hpt-hpf} END{for(k in v) print sprintf("%-60s %10d",k,v[k]/p); for (k in h) print sprintf("%9d GB %-s",p*h[k]/1024/1024,k)}' /proc/meminfo|sort -nr|grep --color=auto -iE "^|( HugePage)[^:]*"

Analyze a particular SQL ID and see the trends for the past day

 Analyse a particular SQL ID and see the trends for the past day

set pages 50000 lines 32767

select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id = sql.snap_id
and s.begin_interval_time > sysdate -1
and
sql.sql_id='&sqlid'
order by c7
/

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

set pages 50000 lines 32767

select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in (
'&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS
/

shell script to send sql query output to html format to mail body

 #!/bin/bash

# oracle credentials

set oracle env variablbles

# Email details

TO= mail id

subject =" report"

#get tablespace details

sqlplus -S / as sysdba << EOF > /tmp/tablespace_details.html

SET MARKUP HTML ON

SET FEEBACKUP OFF

SET VERIFY OFF

SET UNDERLINE OFF

SET TRIMSPOOL ON

SET LINESIZE 200

SET LONG 50000

----SQL QUERY

EOF

#get email content

body=$(cat /tmp/tablespace_details.html)

#send email

export CONTENT_TYPE=text/html

echo -e "TO:$TO\nSubject: $SUBJECT\nContent-Type:$CONTENT_TYPE\n\n$body" | /usr/sbin/sendmail -t

#clearnup

rm /tmp/tablespace_details.html

schema refresh from source to target(prod to qa)

 

Source Database Side :

Step 1:

Check the Schema Exist or Not. Schema must be there in source database.

SQL> select username, account_status, created from dba_users where username='ORACLE';

Step 2: 

Check the schema size 

SQL> select owner, sum(bytes)/1024/1024  "MB"  from dba_segments where owner="ORACLE";

Step 3:

Take the count of schema objects, this will be use full after complete the refresh to compare both target and source schema objects.

SQL> select object_type, count(*) from dba_objetcts where owner='ORACLE' group by object_type;

Step 4:

Before going to take the export, first check mount point size where you're  going to store the export dumpfile, if mount point doesn't have sufficient space export job gets fail.

example :

]$  cd /d01/exports

exports]$ df  -h  .  (in Linux)  df  -g . (AIX and Sun Solaris)

Step 5 :

Create a datapump directory in database level, default datapump directory  location is " /app/oracle/product/10.2.0/rdbms/log/".

First create a directory in OS Level

]$ mkdir -p  /d01/exports 

Next create in database levele

SQL>  create or replace directory DATAPUMP as '/d01/exports';

Step 6 :

Now take the export of schema.

]$ expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile= ORACLE_DBNAME.dmp logfile= ORACLE_DBNAME.log schemas=ORACLE

Step 7 :

If  you're going to import on same server means no need to move the dumpfile anywhere, incase if you're going to import this dump file in some other server, copy this dumpfile through SCP command 

expdp]$ scp  -p  username@servername:\do1\targetlocation   (It will ask you target server password )

We have completed almost all steps in source database end, now we are moving to target database side,

Target Database :

Step 1 :

Check the mount pint size, it should be more then schema size.

]$  cd /d01/exports

exports]$ df  -h  .  (in Linux)  df  -g . (AIX and Sun Solaris)

Step 2 :

Create a directory same like how we have create for source database.

First create a directory in OS Level

]$ mkdir -p  /d01/exports 

Next create in database levele

SQL>  create or replace directory DATAPUMP as '/d01/exports';

Step 3:

Take target schema backup before importing.(for safe side). with export command.

]$ expdp \'/ AS SYSDBA\' directory=DATAPUMP dumpfile= ORACLE_DBNAME.dmp logfile= ORACLE_DBNAME.log schemas=ORACLE

Step 4 :

SCRIPTS:Find complete list of  objects in the schema

Use this script when you do not need to drop the schema, But only the dependent objects in the schema. For ex :- to preserve DB-links, grants, privileges

Below scripts will generate all of the drop statements needed to drop almost all objects (sometimes 1-2 of them will have problems and you will have to manually drop those) from a specified schema (it prompts you for the schema).

EXECUTION

Login to the database where the schema to be dropped exists. Copy and paste the following script , double checking that you are in the correct database!:

--Initializations

set linesize 1000;

SET VERIFY OFF

col owner format a15;

col object_name format a30;

col object_type format a20;

--Select Non-system object owners

SELECT OWNER,OBJECT_TYPE,COUNT(*)

FROM SYS.DBA_OBJECTS

WHERE OWNER NOT IN ('SYS','SYSTEM','TSMSYS','ORACLE_OCM','WMSYS','PATMAN','OUTLN','PUBLIC','DBSNMP','XDB','APPQOSSYS','CTXSYS')

GROUP BY OWNER,OBJECT_TYPE

ORDER BY OWNER,OBJECT_TYPE;

--Select specific Owner, Object_Type & Count

SELECT OWNER,OBJECT_TYPE,COUNT(*)

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

GROUP BY OWNER,OBJECT_TYPE

ORDER BY OWNER,OBJECT_TYPE;

--Drops: Tables, Indexes, & Triggers

SELECT 'DROP TABLE '||OWNER||'.'||OBJECT_NAME||' CASCADE CONSTRAINTS PURGE;'

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('TABLE');

--Drops: Sequences, Views, Packages, Functions & Procedures, Synonyms

SELECT 'DROP '|| OBJECT_TYPE || ' ' ||OWNER||'.'||OBJECT_NAME||';'

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('PACKAGE','SEQUENCE','VIEW','FUNCTION','PROCEDURE','SYNONYM','TRIGGER');

--Drops: Types

SELECT 'DROP '|| OBJECT_TYPE || ' ' ||OWNER||'.'||OBJECT_NAME||' FORCE;'

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('TYPE');

--DO NOT DROP OR IMPORT DBLINKS, EXCLUDE=DATABASE_LINK

SELECT OWNER, OBJECT_TYPE, COUNT(*) 

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('DATABASE LINK')

GROUP BY OWNER, OBJECT_TYPE;

OR

Drop the Schema at destination database:

SQL> Drop Schema <schema_name> cascade; 

(Better drop only schema objects instead of dropping  schema)

Step 5 :

Import the dumpfile into target schema

impdp \'/ AS SYSDBA\' directory=DATAPUMP dumpfile=ORACLE_DATABASE.dmp logfile=ORACLE_DATABASE.log schemas=ORACLE

Step 6 :

Compare the Object Count with source database.

SQL> select object_type, count(*) from dba_objetcts where owner='ORACLE' group by object_type;

(If all the objects same and looks good go ahead and run utlrp.sql.

Step 7 :

Check invalid objects count 

SQL> select owner, object_type, status, count(*)

from sys.dba_objects

where status = ‘INVALID’

group by owner, object_type, status

order by owner, object_type, status;

SQL> @?/rdbms/admin/utlrp.sql

Schema Refresh in QA/DEV step by step

 Schema Refresh step by step 

Below are the details step for schema refresh . The below steps are very helpful when you want to refresh schemas in QA/DEV and want to keep all the previous grants and privileges after refresh .

1. Take the backup of the schema in target database.

expdp directory=<directory_name> dumpfile=backup_%U.dmp logfile=Backup.log parllel=<n> compression=all

schemas=Schema_name

2. sys_privs.sql :

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

set echo off

set head off

set pagesize 0

set feedback off

set linesize 200

spool sys_privs.sql

select 'grant '||privilege||' to '||grantee||' ; '  from dba_sys_privs where grantee in ('schema_name');

spool off

3.owner.sql :

-----------

spool owner.sql

select 'grant '||privilege||' on '||OWNER||'.'||table_name||' to  '||grantee||' ; ' from dba_tab_privs WHERE OWNER in ('schema_name');

spool off

4.pre_refresh_role_privs.sql :

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

set echo off

set head off

set pagesize 0

set feedback off

set linesize 200

spool pre_refresh_role_privs.sql

select 'grant '||granted_role||' to '||grantee||' ; '  from dba_role_privs where grantee in ('schema_name');

spool off

5.Check the object count in the database under that schema

 SELECT object_type,COUNT(*) FROM DBA_OBJECTS WHERE OWNER in ('schema_name') group by object_type ;

6. check the table count

SELECT COUNT(*) FROM DBA_tables WHERE OWNER in ('schema_name');

7.drop all the object under that schema

select 'drop '||object_type||' '||owner||'."'||object_name||'";' from dba_objects where owner in ('schema_names');

8.drop all the tables under that schema

select 'drop table  '||owner||'.'||table_name||' cascade constraints;' from dba_tables where owner in('schema_names');

9.purge the recyclebin

select 'purge table '||owner||'."'||object_name||'";' FROM dba_RECYCLEBIN where owner='schema_name' ;

10.now import data into target database from source dumpfile.

 impdp dumpfile=<dumpfile_name> logfile=import.log directory=<directory_name> schemas=schema_name parallel=<n>

11.check the count of objects after refresh for each schema

SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER in ('schema_name');

12.check the table count

SELECT COUNT(*) FROM DBA_tables WHERE OWNER='schema_name';

13.run the spool file

@sys_privs.sql

14. run the spool file

@owner.sql

15.run the spool file

pre_refresh_role_privs.sql

How to delete MGMTDB

Ref : 

• How to Move/Recreate GI Management Repository (GIMR / MGMTDB) to Different Shared Storage  (Doc ID 1589394.1)

• 12.2: How to Create GI Management Repository (Doc ID 2246123.1)

  MDBUtil: GI Management Repository configuration tool (Doc ID 2065175.1)

 MGMTDB:

. oraenv

+ASM1

as a root user for grid

crsctl stop res ora.crf -init  //node 1

crsctl stop res ora.crf -init  //node2

perfom in both the node:

crsctl modify res ora.crf -attr ENABLED=0 -init

crsctl modify res ora.crf -attr ENABLED=0 -init

srvctl status mgmtdb

as oracle user perform the below

$ORACLE_HOME/bin/dbca -silent -deleteDatabase -sourceDB -MGMTDB

$ORACLE_HOME/bin/dbca -silent -deleteDatabase -sourceDB -MGMTDB

SUCCESSFULLY DELETED MGMTDB

++++++++++++++++++++++++++++++++++++++++++++++++++++

TO  recreate the MGMTDB in Oracle Database (12.2) required to use a perl script mdbutil.pl 

REF : MDBUtil: GI Management Repository configuration tool (Doc ID 2065175.1)

./mdbutil.pl -h

./mdbutil.pl --addmdb --target=+DATA02

AS A ROOT USER FROM GRID HOME

crsctl modify res ora.crf -attr ENABLED=1 -init

crsctl start res ora.crf -init

REPEAT ON NODE 2

Prerequisite Check “CheckActiveFilesAndExecutables” Failed --19c -issue

 issues:

Patch: /u01/patch/33182768/33192793

Log: /u01/app/19c/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-18_12-54-47PM_1.log

Reason: Failed during Patching: oracle.opatch.opatchsdk.OPatchException: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Patch: /u01/patch/33182768/33208107

Log: /u01/app/19c/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-18_12-54-47PM_1.log

Reason: Failed during Patching: oracle.opatch.opatchsdk.OPatchException: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Patch: /u01/patch/33182768/33208123

Log: /u01/app/19c/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-18_12-54-47PM_1.log

Reason: Failed during Patching: oracle.opatch.opatchsdk.OPatchException: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Patch: /u01/patch/33182768/33239955

Log: /u01/app/19c/cfgtoollogs/opatchauto/core/opatch/opatch2022-11-18_12-54-47PM_1.log

Reason: Failed during Patching: oracle.opatch.opatchsdk.OPatchException: Prerequisite check "CheckActiveFilesAndExecutables" failed.

solution :

/sbin/fuser /u01/app/19c/lib/libclntsh.so.19.1

       

 /u01/app/19c/lib/libclntsh.so.19.1 29785M

ps -ef |grep 29785

kill -9 29785                             

/sbin/fuser /u01/app/19c/lib/libasmclntsh19.so

/u01/app/19c/lib/libasmclntsh19.so  98766M

kill -9 98766

After fixing the cause of failure Run opatchauto resume      

How to create restore points for PDB and perform flashback at PDB level and when flashback is off

select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED TRUE 

SQL> alter session set container=CDB$ROOT;

SQL> create restore point test2 for pluggable database hdprd;

SQL> create restore point test3 for pluggable database hdprd guarantee flashback database;

SQL> select NAME,TIME,SCN,PDB_RESTORE_POINT,GUARANTEE_FLASHBACK_DATABASE from V$RESTORE_POINT;

NAME                                     TIME                                            SCN PDB GUA

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

TEST3                                    03-NOV-22 02.10.11.000000000 PM             4706851 YES YES

TEST2                                    03-NOV-22 01.42.15.000000000 PM             4699251 YES NO

RMAN> list restore point all;

using target database control file instead of recovery catalog

SCN              RSP Time  Type       Time      Name

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

4699251                               03-NOV-22 TEST2

4706851                    GUARANTEED 03-NOV-22 TEST3

if it using local undo

SQL> alter pluggable database hdprd close;

SQL> flashback pluggable database hdprd to restore point TEST2;

SQL> alter pluggable database test2 open resetlogs;

if it shared undo and restore point created when pdb was open;

RMAN> alter pluggable database hdprd close;

RMAN> flashback pluggable database hdprd to restore point TEST2 auxiliary destination '/U01/app/oracle/';

RMAN> alter pluggable database HDPRD open resetlogs;

SQL> alter pluggable database hdprd close;

SQL> flashback pluggable database hdprd to clean restore point TEST1;

SQL> alter pluggable database hdprd open resetlogs;

if flashback is off then we can restore only with guarantee flashback:

SQL> alter pluggable database hdprd close immediate instances=all;

RMAN> flashback pluggable database hdprd to restore point TEST3 auxiliary destination '+RECO_01';

RMAN> alter pluggable database hdprd open resetlogs;

once it is up ,drop the old restore point and create a new restore point then start the services

 

OEM alert 12c and 13c

Links for OEM alerts  

EM 12c, EM 13c: How to Configure a Incident Rule to Send a Notification When a Target is marked as UP / Down? ( Doc ID 1936555.1 )

EM 12c, EM 13c : Steps to Use Incident Rule Simulator for Events in Enterprise Manager Cloud Control ( Doc ID 2083126.1 )

EM 12c, EM 13c: How to Configure a Incident Rule to Send a Notification for a Tablespace Space Used (%) Metric Alert? ( Doc ID 1936559.1 )

EM 12c, EM 13c : Steps for Subscribing or Unsubscribing to Email or Page Notifications in the Incident Management Rule Set in Enterprise Manager Cloud Control ( Doc ID 1389460.1 )

EM 12c, EM 13c: How to Configure Incident Rules to Send Notifications for Job Executions (Status Changes) in Enterprise Manager Cloud Control ( Doc ID 1386816.1 )

For incident rule:

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/emadm/using-incident-management.html#GUID-2689210F-C5B6-475B-86F4-7DA667095CD1

Setting Up Rule Sets

For monitoring template:

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/emadm/using-monitoring-templates.html#GUID-647036A5-DF3B-4FFD-9F5C-A43A247A4EFA

monitoring rule 

- https://www.oracle.com/a/otn/docs/enterprise-manager/wp_enterprisemanager13c_monitoringstrategies.pdf

clone non-cdb to another container pdb in 19c

User request to clone for specific pdb from non cdb prod database 

source :SELECT name,network_name,pdb FROM   v$active_services ORDER BY 1;

target : SELECT name,network_name,pdb FROM   v$active_services ORDER BY 1;

SOURCE :

   srvctl status database -d PROD

    srvctl status service database -d PROD

Target :

srvctl status service database -d xbduat -s xcuatsrv

srvctl config service -d xdbuat -s xcuatsrv

Target :

SRVCTL status service -d xdbuat

srvctl stop service -d xdbuat -s xcuatsrv

srvctl remove service -d xdbuat -s xcuatsrv

alter pluggable database xdbuat close immediate instances=all;

drop pluggable database xdbuat including datafiles;

Source:

create user ram identified by *****;

grant create session,create pluggable database to ram;

Target :

drop database link cdb_dblink;

create database link cdb_dblink connect to ram identified by ****** using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prod-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD)))'

select * from dual@cbd_dblink;

create pluggable database xdbuat from NON$CDB@cdb_dblink standbys=none;

----on standby side:

col name format a20

select name, recovery_status from v$pdbs;

select name,open_mode from v$pdbs;

ALTER SESSION SET CONTAINER=xdbuat;

@?/rdbms/admin/noncdb_to_pdb.sql

ALTER PLUGGABLE DATABASE xdbuat OPEN instances=all;

col cause for a15

col name for a15

col message for a35 word_wrapped

set line 200

set head off

select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='xdbuat';

alter pluggable database xdbuat open;

alter session set container=xdbuat;

select count(*) from dba_objects where status='INVALID'

@?/rdbms/admin/utlrp.sql

shut immediate    // inside the pdb

startup     //inside the pdb

select network_name from dba_services;

remove the service if not required.

BEGIN DBMS_SERVICE.STOP_SERVICE (SERVICE_NAME => 'cdsrv'); END;

/

BEGIN DBMS_SERVICE.DELETE_SERVICE (SERVICE_NAME => 'cdsrv'); END;

/

[oracle@ed ~]$ srvctl status service -d xdbuat -s xdbuatsrv

srvctl add service -d xdbuat  -service cdbsrv -pdb xdbuat -preferred drod3,drod4

srvctl start service -d xdbuat  -service cdbsrv 

srvctl status service -d xdbuat  -service cdbtsrv 

_____________happy learning_____________

OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) ( Doc ID 428681.1 )

 refer metalink OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) ( Doc ID 428681.1 )

listener service register

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = demo.localdomain)(PORT = 1531))';

alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = demo.localdomain)(PORT = 1531))','(ADDRESS = (PROTOCOL = TCP)(HOST = demo.localdomain)(PORT = 1522))' scope=both;

stop and start the listener

lsnrctl status LISTENR.

agent status check scripts for oem13c agent

 agent_check.sh

#!/bin/bash

agent_status=`ps -ef | grep -i gcagent.tmmain.TMMain|grep -i sysman |grep -v grep | wc -l`

if [ $agent_status = 0 ]

then

date >> /u01/app/agent13c/log/agentStarted.log

/u01/app/agent13c/agent_13.4.0.0.0/bin/emctl start agent >> /u01/app/agent13c/log/agentStarted.log & > /dev/null

#mailing program

#mail -s "OEM Agent 13c was not running...  Restarted on `hostname` $i `basename $0`" 'EM13c@*****' < agentStarted.log

else

date >> /u01/app/agent13c/log/agentStarted.log

echo 'Agent is running' >> /u01/app/agent13c/log/agentStarted.log

fi

MULTITENANT service register in 19c

 MULTITENANT:

BEGIN

  DBMS_SERVICE.create_service(

    service_name => 'my_new_service',

    network_name => 'my_new_service'

  );

END;

/

COLUMN name FORMAT A30

COLUMN network_name FORMAT A30

SELECT name,

       network_name

FROM   dba_services

ORDER BY 1;

SELECT name,

       network_name

FROM   v$active_services

ORDER BY 1;

BEGIN

    DBMS_SERVICE.START_SERVICE (SERVICE_NAME   => 'herospdb.localdomain');

END;

/

BEGIN

  DBMS_SERVICE.modify_service(

    service_name => 'my_new_service',

    goal         => DBMS_SERVICE.goal_throughput

  );

END;

/

sqlplus ram/ram@demo:1521/herospdb.localdomain