Pluggable Database (PDB) useful SQL statement

[ora@oracle]$ sqlplus system/****@PROD_CDB

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> select name from v$database;

NAME
---------
PROD

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                         MOUNTED


SQL> ALTER SESSION SET CONTAINER= PDB;

Session altered.

SQL> ALTER PLUGGABLE DATABASE OPEN;

Warning: PDB altered with errors.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         3 PDB                         READ WRITE YES

SQL> shut immediate

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> select value from v$parameter where name='PDB';

SQL> select cause, message from pdb_plug_in_violations where name = 'PDB';

SQL> select * from v$services;

SQL> select SERVICE_ID,NAME,NETWORK_NAME from v$services;

SQL> select NAME from v$services;

[ora@oracle]$ sqlplus apps/****@PDB


SQL> select username from dba_users where USERNAME= 'APPS';

no rows selected

ORA-03113: end-of-file on communication channel

SQL> startup
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  3731384 bytes
Variable Size            1140850760 bytes
Database Buffers         7432306688 bytes
Redo Buffers               13045760 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 1264
Session ID: 814 Serial number: 23355


SQL>

[ora@sujeet ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 22 21:07:49 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  3731384 bytes
Variable Size            1140850760 bytes
Database Buffers         7432306688 bytes
Redo Buffers               13045760 bytes
SQL>
SQL> alter database mount;

Database altered.

SQL>
SQL>
SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  3731384 bytes
Variable Size            1140850760 bytes
Database Buffers         7432306688 bytes
Redo Buffers               13045760 bytes
Database mounted.
Database opened.
SQL>

Renaming Data Files in 11g database

Renaming datafile (.dbf)  rename in 11g

Rename Datafiles in Single Tablespace  (Database Open Mode) :-

Caution : Backup your tablespace Before you will do some change in your tablespace.

Please find below steps to rename datafile.

We can use the alter tablespace rename datafile command,
but the tablespace most be offline and you must re-name at OS level command the data file while the tablespace is offline:


Steps:- 1

[ora@oracle ~]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;


Tablespace altered.
SQL> !

Steps:- 2

Rename undo07.dbf to sysaux08.dbf


[ora@oracle ~]$ cd /u01/db/apps_st/data/

[ora@oracle data]$ ls -lrt|grep undo07.dbf
-rw-r----- 1 oraqa dba  3221233664 Aug  9 11:32 undo07.dbf

[ora@oracle data]$ chmod 755 undo07.dbf

[ora@oracle data]$ mv undo07.dbf sysaux08.dbf

[ora@oracle data]$ ls -lrt|grep undo07.dbf

[ora@oracle data]$ ls -lrt|grep sysaux08.dbf
-rwxr-xr-x 1 oraqa dba  3221233664 Aug  9 11:32 sysaux08.dbf


Steps:- 3

[ora@oracle data]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX RENAME DATAFILE '/u01/db/apps_st/data/undo07.dbf' to '/u01/db/apps_st/data/sysaux08.dbf';

Tablespace altered.

Steps:- 4

SQL> ALTER TABLESPACE SYSAUX ONLINE;

Tablespace altered.

Steps:- 5

Verify tablespace status.

SQL> SELECT status
FROM dba_tablespaces
WHERE tablespace_name = 'SYSAUX';

STATUS
---------
ONLINE

Verify datafile name.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME = 'SYSAUX';

TABLESPACE_NAME-------------FILE_NAME
--------------------------------------------------------------------------------
SYSAUX                    /u01/db/apps_st/data/sysaux08.dbf





Rename datafile undo07.dbf to sysaux08.dbf completed.