When working with Oracle Database, particularly in environments utilizing Pluggable Databases (PDBs), you may encounter the ORA-65177 error when trying to open a PDB. This article explains the common causes of this error and provides practical solutions to resolve it.

Understanding ORA-65177 Error

The ORA-65177 error typically occurs due to synchronization issues between the Container Database (CDB) and the PDB. This can happen when there are attempts to synchronize changes of common users in the CDB that conflict with local users in the PDB.

Common Scenario

Consider the following scenario:

• A common user, e.g., ABCDE_APP, is created in the CDB.
• A local user with the same name, ABCDE_APP, already exists in the PDB.
• An operation, such as ALTER USER ABCDE_APP ACCOUNT UNLOCK, is executed in the CDB.

In this case, when attempting to open the PDB, Oracle tries to synchronize the change for the common user in the CDB with the PDB. Since there is already a local user with the same name in the PDB, the ORA-65177 error occurs, causing the PDB to open in RESTRICTED SESSION mode.

Identifying the Issue

To diagnose the issue, follow these steps:

1. Open the PDB and verify if it is in restricted mode:

SQL> ALTER PLUGGABLE DATABASE ORCL OPEN;
Warning: PDB altered with errors.

SQL> SHOW PDBS;
CON_ID CON_NAME OPEN MODE RESTRICTED
------ --------- --------- ----------
2      PDB$SEED READ ONLY NO
3      ORCL     READ WRITE YES

2. Query the PDB_PLUG_IN_VIOLATIONS view for details of the error:

SQL> SELECT TIME, NAME, TYPE, CAUSE, STATUS, MESSAGE
FROM PDB_PLUG_IN_VIOLATIONS
WHERE STATUS <> 'RESOLVED';

TIME                 NAME  TYPE   CAUSE         STATUS   MESSAGE
-------------------  ----  -----  ------------  -------  -------------------------------------------------
30-OCT-20 12:00:52   ORCL  ERROR  Sync Failure  PENDING  Sync PDB failed with ORA-65177 during 'alter user ABCDE_APP account unlock'

Resolving the Issue

To resolve the error, follow these steps:

1. Connect to the CDB and remove the pending synchronization statement:

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.

SQL> DELETE FROM PDB_SYNC$
WHERE SQLSTMT LIKE 'alter user ABCDE_APP account unlock%';
1 row deleted.

SQL> COMMIT;
Commit complete.

2. Connect to the PDB and repeat the process:

SQL> ALTER SESSION SET CONTAINER = ORCL;
Session altered.

SQL> DELETE FROM PDB_SYNC$
WHERE SQLSTMT LIKE 'alter user ABCDE_APP account unlock%';
1 row deleted.

SQL> COMMIT;
Commit complete.

3. Close and reopen the PDB:

SQL> ALTER PLUGGABLE DATABASE ORCL CLOSE;
Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE ORCL OPEN;
Pluggable database altered.

SQL> SHOW PDBS;
CON_ID CON_NAME OPEN MODE RESTRICTED
------ --------- --------- ----------
2      PDB$SEED READ ONLY NO
3      ORCL     READ WRITE NO

After these steps, the PDB should open without any restrictions.

Preventing Future Occurrences

To prevent the issue from occurring again:

• Avoid creating local users in the PDB with the same name as common users in the CDB.
• Consider deleting the local user from the PDB and granting access to the common user in the PDB.

Conclusion

The ORA-65177 error when opening a PDB is usually related to synchronization conflicts between common users in the CDB and local users in the PDB. By following the steps described, you can resolve the issue and prevent future occurrences, ensuring the integrity and availability of your database environment.

For more details, see the original article: Pluggable database opened with RESTRICTED SESSION due to Sync PDB failed with ORA-65177 during ‘alter user’.