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.

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_____________