Learn oracle 12c database management

Subscribe to Learn oracle 12c database management  feed
I will be concentrating mostly on 12c database on this site. you can visit my other blog for knowledge on previous versions of oracle database. Arvind Toorpunoreply@blogger.comBlogger32125
Updated: 11 hours 25 sec ago

Enabling archive log mode -12c

Fri, 2016-03-04 11:05
Since the Redologs are created at container database level in 12c and not at pluggable database level. (Enabling archivelog at pluggable database level is not possible). Archiving is done at CDB's.

You can check archive log mode either by querying v$database or archivelog list 

SQL> select name,open_mode,log_mode from v$database;

NAME  OPEN_MODE       LOG_MODE
--------- -------------------- ------------
ORCL  READ WRITE       NOARCHIVELOG

(OR)

SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence       13

*************** ***************
To enable the Archvielog mode
*************** ***************

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select name,open_mode,log_mode from v$database;

NAME  OPEN_MODE       LOG_MODE
--------- -------------------- ------------
ORCL  READ WRITE       ARCHIVELOG


SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence       13
Categories: DBA Blogs

open all pluggable databases -12c

Fri, 2016-03-04 09:48
SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       READ ONLY
PDBORCL3       READ WRITE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.


SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       MOUNTED
PDBORCL3       MOUNTED


SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       READ WRITE

Categories: DBA Blogs

Clone the pluggable database - 12c

Fri, 2016-03-04 09:40
********************* *********************
   Select a pluggable database to clone
********************* *********************

SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ ONLY
PDBORCL2       READ WRITE

SQL> alter pluggable database pdborcl1 close;

Pluggable database altered.

SQL> alter pluggable database pdborcl1 open read only;

Pluggable database altered.


********************* *********************
  Lets clone the pluggable database now
********************* *********************

SQL> create pluggable database pdborcl3 from pdborcl1 file_name_convert=('/u01/app/oracle/oradata/orcl/pdborcl1/'
,'/u01/app/oracle/oradata/orcl/pdborcl3/');  

Pluggable database created.

SQL> alter pluggable database pdborcl3 open;

Pluggable database altered.


SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ ONLY
PDBORCL2       READ WRITE
PDBORCL3       READ WRITE


SQL> alter pluggable database pdborcl1 close;

Pluggable database altered.

SQL> alter pluggable database pdborcl1 open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ WRITE
PDBORCL2       READ WRITE
PDBORCL3       READ WRITE


************************** **************************
Check this below link on how to drop a pluggable database :
************************** **************************

DROPPING-PLUGGABLE-DATABASE








Categories: DBA Blogs

Dropping a pluggable database -12c

Fri, 2016-03-04 09:38
********************* *********************
Dropping a pluggable database :
********************* *********************

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       READ WRITE



SQL> DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES;
DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES
*
ERROR at line 1:
ORA-65025: Pluggable database PDBORCL3 is not closed on all instances.

You need to close the database before dropping.

SQL> alter pluggable database PDBORCL3 close;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       MOUNTED

SQL> DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES;

Pluggable database dropped.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE


Lets make sure by looking at datafile location

[oracle@Linux03 pdborcl3]$ pwd
/u01/app/oracle/oradata/orcl/pdborcl3

[oracle@Linux03 pdborcl3]$ ls -ll
total 0


[oracle@Linux03 pdborcl3]$ 


Categories: DBA Blogs

Connecting to a pluggable database -12c

Thu, 2016-03-03 10:05
******* ********** *********
USING EZCONNECT:
******* ********** *********

[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxxx@localhost:1521/pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:12:17 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDBORCL1

******** ********* *********
USING TNSNAMES:
******** ********* *********

Create a tnsnames entry for the pdb that you want to connect

[oracle@Linux03 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

pdborcl1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Linux03)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
 



[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxx@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:15:10 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDBORCL1

Categories: DBA Blogs

Pages