26 December, 2017

Domain Indexes -- 2 : STOPLIST for a Context Index

In the previous example, I created a simple CONTEXTIndex.
For a CONTEXT Index, Oracle automatically applies a default "STOPLIST".  This is a list of common words that are automatically excluded from the Index.
If you notice in the previous post, the words "this", "is", "a", "to", "be" are all in the MY_TEXT column of the MY_TEXT_TABLE but do not appear in the list of tokens in DR$MY_TEXT_INDEX$I.


Can we expand and build our own STOPLIST ?

I start with a new table containing the same rows :

SQL> connect ctxuser/ctxuser
Connected.
SQL> create table another_text_table
  2  as select id_column as id_column,
  3  my_text as another_text
  4  from my_text_table;

Table created.

SQL> select another_text from another_text_table;

ANOTHER_TEXT
--------------------------------------------------------------------------------
This is a long piece of text written by Hemant
Another long text to be captured by the index

SQL> 


I then build a custom STOPLIST (which I name as "another_text") and then create the CONTEXT Index.

SQL> begin
  2  ctx_ddl.create_stoplist('another_text');
  3  ctx_ddl.add_stopword('another_text','Hemant');
  4  ctx_ddl.add_stopword('another_text','long');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> 
SQL> create index another_text_index
  2  on another_text_table(another_text)
  3  indextype is ctxsys.context
  4  parameters('stoplist another_text');

Index created.

SQL> 


This time, I don't have to use CTX_DDL.SYNC_INDEX as the Index is created after the table is populated. Nevertheless, if I execute DML (INSERT, UPDATE or DELETE) to change data, I will need a call to CTX_DDL.SYNC_INDEX to update the Index.

Now, I check the Tokens that are created in this index.

SQL> select token_text, token_count
  2  from dr$another_text_index$i
  3  order by token_text
  4  /

TOKEN_TEXT                                                       TOKEN_COUNT
---------------------------------------------------------------- -----------
A                                                                          1
ANOTHER                                                                    1
BE                                                                         1
BY                                                                         2
CAPTURED                                                                   1
INDEX                                                                      1
IS                                                                         1
OF                                                                         1
PIECE                                                                      1
TEXT                                                                       2
THE                                                                        1
THIS                                                                       1
TO                                                                         1
WRITTEN                                                                    1

14 rows selected.

SQL> 


The STOPLIST words that I defined ('Hemant' and 'long') are not in the Tokens list.  However, since I defined my own Custom STOPLIST, Oracle's default BASIC_STOPLIST has been overriden.  So, now the Tokens list includes words like "a", "be", "by", "is" etc.

Contrast this with the Tokens list in the CONTEXT index that was created on MY_TEXT_TABLE using the default BASIC_STOPLIST :

SQL> select token_text, token_count
  2  from dr$my_text_index$i
  3  order by token_text
  4  /

TOKEN_TEXT                                                       TOKEN_COUNT
---------------------------------------------------------------- -----------
ANOTHER                                                                    1
CAPTURED                                                                   1
HEMANT                                                                     1
INDEX                                                                      1
LONG                                                                       2
PIECE                                                                      1
TEXT                                                                       2
WRITTEN                                                                    1

8 rows selected.

SQL> 


So, if you want to build a Custom STOPLIST, make sure you identify all the "common" words you want excluded.
.
.
.

10 December, 2017

Domain Indexes -- 1 : CONTEXT Indexes

A CONTEXT Index is one of a class of Domain Indexes.  It is used to build text-retrieval application.
Instead of a regular B-Tree index that captures the entire text of a VARCHAR2 column as a key, you can build an index that consists of "Tokens", words extracted from a long-ish text in the database column.  Searching the index is based on the CONTAINS operator.

Here is a quick demo in 11.2.0.4 :

First I setup a new user for this demo.  I could have used an existing user but my "HEMANT" user has DBA privileges and I want to demonstrate CONTEXT without such privileges.
Here the key grant is "CTXAPP" that is granted to the user.

SQL> create user ctxuser identified by ctxuser default tablespace users;

User created.

SQL> grant create session, create table to ctxuser;

Grant succeeded.

SQL> grant ctxapp to ctxuser;

Grant succeeded.

SQL> alter user ctxuser quota unlimited on users; 

User altered.

SQL> 


Next, this user creates the demonstration table and index :

SQL> connect ctxuser/ctxuser
Connected.
SQL> create table my_text_table 
  2  (id_column number primary key,
  3  my_text varchar2(2000));

Table created.

SQL> create index my_text_index
  2  on my_text_table(my_text)
  3  indextype is ctxsys.context;

Index created.

SQL> 
SQL> insert into my_text_table
  2  values (1,'This is a long piece of text written by Hemant');

1 row created.

SQL> insert into my_text_table
  2  values (2,'Another long text to be captured by the index');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> -- this update to the index would be a regular background job
SQL> exec ctx_ddl.sync_index('MY_TEXT_INDEX');

PL/SQL procedure successfully completed.

SQL> 


Note the call to CTX_DDL.SYNC_INDEX.  Unlike a regular B-Tree index, the CONTEXT (Domain) Index is, by default, *not* updated in real-time when DML occurs against the base table.  (Hopefully, in a future post, I may demonstrate real-time updates to a CTXCAT index, different from a CONTEXT Index).
Only remember that this CONTEXT index is not automatically updated.  If new rows are added to the table, they are not visible through the index until a SYNC_INDEX operation is performed.  The SYNC_INDEX can be scheduled as a job with another call to DBMS_JOB or DBMS_SCHEDULER or itself as part of the CREATE INDEX statement.

Now, let me demonstrate usage of the Index with the CONTAINS operator.

SQL> select id_column as id,
  2  my_text
  3  from my_text_table
  4  where contains (my_text, 'written by Hemant') > 0
  5  /

 ID
----------
MY_TEXT
--------------------------------------------------------------------------------
         1
This is a long piece of text written by Hemant


SQL> select my_text
  2  from my_text_table
  3  where contains (my_text, 'Another long') > 0
  4  /

MY_TEXT
--------------------------------------------------------------------------------
Another long text to be captured by the index

SQL> 


Yes, the CONTAINS operator is a bit awkward.  It will be some time before you (or your developers) get used to the syntax !

Besides CTX_DDL, there are a number of other packages in the prebuilt CTXSYS schema that are available :
 CTX_CLS
 CTX_DDL
 CTX_DOC
 CTX_OUTPUT
 CTX_QUERY
 CTX_REPORT
 CTX_THES
 CTX_ULEXER

Since I have created a separate database user, I can also demonstrate the additional objects that are created when the INDEXTYPE IS CTXSYS.CONTEXT.

SQL> select object_type, object_name, to_char(created,'DD-MON-RR HH24:MI') Crtd
  2  from user_objects
  3  order by object_type, object_name
  4  /

OBJECT_TYPE         OBJECT_NAME                    CRTD
------------------- ------------------------------ ------------------------
INDEX               DR$MY_TEXT_INDEX$X             10-DEC-17 16:48
INDEX               DRC$MY_TEXT_INDEX$R            10-DEC-17 16:48
INDEX               MY_TEXT_INDEX                  10-DEC-17 16:48
INDEX               SYS_C0017472                   10-DEC-17 16:48
INDEX               SYS_IL0000045133C00006$$       10-DEC-17 16:48
INDEX               SYS_IL0000045138C00002$$       10-DEC-17 16:48
INDEX               SYS_IOT_TOP_45136              10-DEC-17 16:48
INDEX               SYS_IOT_TOP_45142              10-DEC-17 16:48
LOB                 SYS_LOB0000045133C00006$$      10-DEC-17 16:48
LOB                 SYS_LOB0000045138C00002$$      10-DEC-17 16:48
TABLE               DR$MY_TEXT_INDEX$I             10-DEC-17 16:48
TABLE               DR$MY_TEXT_INDEX$K             10-DEC-17 16:48
TABLE               DR$MY_TEXT_INDEX$N             10-DEC-17 16:48
TABLE               DR$MY_TEXT_INDEX$R             10-DEC-17 16:48
TABLE               MY_TEXT_TABLE                  10-DEC-17 16:48

15 rows selected.

SQL> 
SQL> select table_name, constraint_name, index_name
  2  from user_constraints
  3  where constraint_type = 'P'
  4  order by table_name, constraint_name
  5  /

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
INDEX_NAME
------------------------------
DR$MY_TEXT_INDEX$K             SYS_IOT_TOP_45136
SYS_IOT_TOP_45136

DR$MY_TEXT_INDEX$N             SYS_IOT_TOP_45142
SYS_IOT_TOP_45142

DR$MY_TEXT_INDEX$R             DRC$MY_TEXT_INDEX$R
DRC$MY_TEXT_INDEX$R

MY_TEXT_TABLE                  SYS_C0017472
SYS_C0017472


SQL> 


Yes, that is a large number of database objects besides MY_TEXT_TABLE and MY_TEXT_INDEX.  SYS_C0017472 is, of course, the Primary Key Index on MY_TEXT_TABLE (on the ID_COLUMN column).  The others are interesting.

The "Tokens" I mentioned in the first paragraph are, for the purpose of this table MY_TEXT_TABLE, in the DR$MY_TEXT_INDEX$I.

SQL> desc DR$MY_TEXT_INDEX$I
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TOKEN_TEXT                                NOT NULL VARCHAR2(64)
 TOKEN_TYPE                                NOT NULL NUMBER(10)
 TOKEN_FIRST                               NOT NULL NUMBER(10)
 TOKEN_LAST                                NOT NULL NUMBER(10)
 TOKEN_COUNT                               NOT NULL NUMBER(10)
 TOKEN_INFO                                         BLOB

SQL> select token_text, token_count
  2  from dr$my_text_index$i
  3  /

TOKEN_TEXT                                                       TOKEN_COUNT
---------------------------------------------------------------- -----------
ANOTHER                                                                    1
CAPTURED                                                                   1
HEMANT                                                                     1
INDEX                                                                      1
LONG                                                                       2
PIECE                                                                      1
TEXT                                                                       2
WRITTEN                                                                    1

8 rows selected.

SQL> 


Update : Here is a demonstration of a STOPLIST for a CONTEXT Index.

I have been busy in the last few months and have not published much this quarter. Hopefully, I will get more time in the coming weeks to explore CONTEXT, CTXCAT and Domain Indexes.
.
.
.

07 November, 2017

UNIQUE LOCAL (Partitioned) Index

It is easy to create a default GLOBAL Index that is defined as a Unique Index on a column (or composite of columns) containing unique values.

But what if you have a Partitioned Table and want to create a LOCAL (i.e. equi-partitioned with the table) Index as a Unique Index ?  Are there any constraints ?

Let me demonstrate a Partitioned table listing Users by Region Code.

SQL> create table users
  2  (region_code varchar2(3),
  3   username varchar2(30),
  4   account_status varchar2(32),
  5   created date,
  6   profile varchar2(128))
  7  partition by range (region_code)
  8  (partition a_m values less than ('N'),
  9   partition n_r values less than ('S'),
 10   partition s_z values less than (MAXVALUE))
 11  /

Table created.

SQL> 
SQL> insert into users
  2  select substr(username,1,3), username, account_status, created, profile
  3  from dba_users
  4  /

39 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','USERS');

PL/SQL procedure successfully completed.

SQL>
SQL> col partition_name format a30
SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'USERS'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
A_M                                    18
N_R                                     9
S_Z                                    12

SQL> 


I now verify that I can create a GLOBAL (non-partitioned) Unique Index on USERNAME.

SQL> create unique index users_username_u1 on users(username) global;

Index created.

SQL> drop index users_username_u1;

Index dropped.

SQL> create unique index users_username_u1 on users(username);

Index created.

SQL> drop index users_username_u1;

Index dropped.

SQL> 


I now verify that I can create a Non-Unique LOCAL Index (being equi-partitioned, the index is partitioned by REGION_CODE).  (Being equi-partitioned, the default behaviour is the Index Partition Names inherit from the Table Partition Names).

SQL> create index users_username_l1 on users(username) local;

Index created.

SQL> select partition_name, num_rows
  2  from user_ind_partitions
  3  where index_name = 'USERS_USERNAME_L1'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
A_M                                    18
N_R                                     9
S_Z                                    12

SQL> 
SQL> drop index users_username_l1;

Index dropped.

SQL> 


I've proven (with the GLOBAL Index) that USERNAME is Unique across the whole table.  Can I create a Unique LOCAL Index on this column ?

SQL> create unique index users_username_u_l1 on users(username) local;
create unique index users_username_u_l1 on users(username) local
                                           *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index


SQL> 


Note the Error Message. The Partition Key must be a subset of the Unique Index columns.  Let me try adding the Partition Key  (in my example table, the Partition Key is a single column -- it could be a composite of multiple columns.  In that case all the columns of the Partition Key must for a subset of the Unique Index).

SQL> create unique index users_rc_un_u_l1 on users(region_code, username) local;

Index created.

SQL> select partition_name, num_rows
  2  from user_ind_partitions
  3  where index_name = 'USERS_RC_UN_U_L1'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
A_M                                    18
N_R                                     9
S_Z                                    12

SQL> drop index users_rc_un_u_l1;

Index dropped.

SQL> create unique index users_un_rc_u_l1 on users(username, region_code) local;

Index created.

SQL> select partition_name, num_rows
  2  from user_ind_partitions
  3  where index_name = 'USERS_UN_RC_U_L1'
  4  order by partition_position
  5  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
A_M                                    18
N_R                                     9
S_Z                                    12

SQL> 

It doesn't matter if I put the Partition Key (REGION_CODE) before or after the target column(s) (USERNAME) when I create the LOCAL Unique Index.  What is necessary is that the Partition Key be a subset of the Unique Index definition.
.
.
.

29 September, 2017

Partitioned Indexes

Most discussions about Partitioning in Oracle are around Table Partitioning.  Rarely do we come across Index Partitioning.
A couple of days ago, there was an Oracle Community question on Partitioned Indexes.

So, here is a quick listing of Index Partitioning options  (these tests are in 11.2.0.4)


First, I start with a regular, non-partitioned table.

SQL> create table non_partitioned  
  2  (id_col number,
  3   data_col_1 number,
  4   data_col_2 number,
  5   data_col_3 varchar2(15)
  6  )
  7  /

Table created.

SQL> 


I now attempt to create an Equi-Partitioned (LOCAL) Index on it.

SQL> create index equi_part on non_partitioned (id_col) local;
create index equi_part on non_partitioned (id_col) local
                          *
ERROR at line 1:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned


SQL> 


As expected I can't create a LOCAL index on a non-partitioned table.

Can I create any partitioned index on this table ?

I try two different GLOBAL PARTITIONed Indexes

SQL> create index global_part   
  2  on non_partitioned (id_col) global
  3  partition by range (id_col)
  4  (partition p_100 values less than (101),
  5  partition p_200 values less than (201)
  6  )
  7  /
)
*
ERROR at line 6:
ORA-14021: MAXVALUE must be specified for all columns


SQL>
SQL> create index global_part
  2  on non_partitioned (id_col) global
  3  partition by range (id_col)
  4  (partition p_100 values less than (101),
  5  partition p_200 values less than (201),
  6  partition p_max values less than (MAXVALUE)
  7  )
  8  /

Index created.

SQL> 
SQL> create index global_part_comp
  2  on non_partitioned (id_col, data_col_3) global
  3  partition by range (id_col, data_col_3)
  4  (partition p_1 values less than (101,'M'),
  5  partition p_2 values less than (101,MAXVALUE),
  6  partition p_3 values less than (201,'M'),
  7  partition p_4 values less than (201,MAXVALUE),
  8  partition p_max values less than (MAXVALUE, MAXVALUE)
  9  )
 10  /

Index created.

SQL> 


So, I must have a MAXVALUE partition for the Index.  Note that the two indexes above are now Partitioned without the table itself being partitioned.

SQL> select index_name, partitioned
  2  from user_indexes
  3  where table_name = 'NON_PARTITIONED'
  4  order by 1
  5  /

INDEX_NAME                     PAR
------------------------------ ---
GLOBAL_PART                    YES
GLOBAL_PART_COMP               YES

SQL> 


The above indexes are Prefixed Global Partitioned Indexes. Can I create a Non-Prefixed Global Partitioned Index -- an Index where the Partition Key is not formed by the left-most columns of the index.

SQL> create index global_part_nonprefix
  2  on non_partitioned (id_col, data_col_3) global
  3  partition by range (data_col_1)
  4  (partition p_1 values less than (101),
  5  partition p_2 values less than (201),
  6  partition p_max values less than (MAXVALUE)
  7  )
  8  /
partition by range (data_col_1)
                              *
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>  
SQL> !oerr ora 14038
14038, 00000, "GLOBAL partitioned index must be prefixed"
// *Cause:  User attempted to create a GLOBAL non-prefixed partitioned index
//          which is illegal
// *Action: If the user, indeed, desired to create a non-prefixed
//          index, it must be created as LOCAL; otherwise, correct the list 
//          of key and/or partitioning columns to ensure that the index is 
//          prefixed

SQL> 


So, I have proved that a Non-Partitioned Table cannot have a LOCAL Partitioned Index or a Non-Prefixed Global Partitioned Index but can still have a Global Partitioned Index where the Partition Key is left-prefixed from the Index Key. Also, that a Global Partitioned Index can be a Composite Index with columns of different datatypes.

Let me now proceed with a Partitioned Table.

SQL> create table partitioned
  2  (id_col number,
  3   data_col_1 number,
  4   data_col_2 number,
  5   data_col_3 varchar2(15)
  6  )
  7  partition by range (id_col)
  8  (partition p_100 values less than (101),
  9   partition p_200 values less than (201),
 10   partition p_max values less than (MAXVALUE)
 11  )
 12  /

Table created.

SQL> 


First, the Equi-Partitioned (LOCAL) Index.

SQL> create index part_equi_part
  2  on partitioned (id_col) local
  3  /

Index created.

SQL> select partition_name, partition_position
  2  from user_ind_partitions
  3  where index_name = 'PART_EQUI_PART'
  4  order by 2
  5  /

PARTITION_NAME                 PARTITION_POSITION
------------------------------ ------------------
P_100                                           1
P_200                                           2
P_MAX                                           3

SQL> 


The usage of the LOCAL keyword instead of GLOBAL defines the Index as equi-partitioned with the table.  Index Partitions are automatically created to match the Table Partitions with the same Partition Names.  It is possible to create a LOCAL Partitioned Index and manually specify Partition Names but this, in my opinion, is a bad idea.  Attempting to manually name each Partition for the Index can result in a mis-match between Table Partition Names and Index Partition Names.

Next, I define two GLOBAL Partitioned Indexes on this table.

SQL> create index part_gbl_part  
  2  on partitioned (data_col_1) global
  3  partition by range (data_col_1)
  4  (partition p_1 values less than (1001),
  5   partition p_2 values less than (2001),
  6   partition p_3 values less than (3001),
  7   partition p_4 values less than (4001),
  8   partition p_max values less than (MAXVALUE)
  9  )
 10  /

Index created.

SQL> create index part_gbl_part_comp
  2  on partitioned (data_col_2, data_col_3) global
  3  partition by range (data_col_2, data_col_3)
  4  (partition p_a values less than (10, 'M'),
  5   partition p_b values less than (10, MAXVALUE),
  6   partition p_c values less than (20, 'M'),
  7   partition p_d values less than (20, MAXVALUE),
  8   partition p_e values less than (30, 'M'),
  9   partition p_f values less than (30, MAXVALUE),
 10   partition p_max values less than (MAXVALUE, MAXVALUE)
 11  )
 12  /

Index created.

SQL> 
SQL> l
  1  select index_name, partition_name, partition_position
  2  from user_ind_partitions
  3  where index_name in
  4    (select index_name from user_indexes
  5 where table_name = 'PARTITIONED'
  6    )
  7* order by 1,3
SQL> /

INDEX_NAME         PARTITIO PARTITION_POSITION
------------------ -------- ------------------
PART_EQUI_PART     P_100                     1
PART_EQUI_PART     P_200                     2
PART_EQUI_PART     P_MAX                     3
PART_GBL_PART      P_1                       1
PART_GBL_PART      P_2                       2
PART_GBL_PART      P_3                       3
PART_GBL_PART      P_4                       4
PART_GBL_PART      P_MAX                     5
PART_GBL_PART_COMP P_A                       1
PART_GBL_PART_COMP P_B                       2
PART_GBL_PART_COMP P_C                       3
PART_GBL_PART_COMP P_D                       4
PART_GBL_PART_COMP P_E                       5
PART_GBL_PART_COMP P_F                       6
PART_GBL_PART_COMP P_MAX                     7

15 rows selected.

SQL> 


The Equi-Partitioned (LOCAL) Index has the same number (and, recommended, names) of Partitions as the Table.
However, the GLOBAL Indexes can have different numbers of Partitions.

As with the first case, I cannot create a Global Non-Prefixed Partitioned Index (where the Index Partition key is not  a left-prefix of the Index).

SQL> create index part_global_part_nonprefix
  2  on partitioned (id_col, data_col_3) global
  3  partition by range (data_col_1)
  4  (partition p_1 values less than (101),
  5   partition p_2 values less than (201),
  6   partition p_max values less than (MAXVALUE)
  7  )
  8  /
partition by range (data_col_1)
                              *
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL> 


In this blog post, I haven't touched on Partial Indexing (a 12c feature).

I haven't touched on Unique LOCALly Partitioned Indexes.

I haven't demonstrated the impact of Partition Maintenance operations (TRUNCATE, DROP, MERGE, ADD, SPLIT) on LOCAL and GLOBAL Indexes here -- although I have touched on such operations and LOCAL indexes in earlier blog posts.
.
.
.

13 September, 2017

Recovering a Datafile created after the last Backup

Suppose you added a datafile to the database after the last backup and do not yet have a backup of the datafile when the file is lost / corrupt.

How does Oracle RMAN handle the RESTORE / RECOVER?

Here's the situation :  Tablespace HEMANT has three datafiles, of which file id 5 and 6 have been added after the last backup.  (This scenario tested in 11.2.0.4)
Note : Some of you may know that Oracle can reuse file ids.  So file ids 5 and 6 which may have been for some other tablespace / datafiles that had been dropped in the past were reused for these two datafiles added to the tablespace in September 17.


SQL> select v.file#,v.creation_time, v.name
  2  from v$datafile v, v$tablespace t
  3  where v.ts#=t.ts#
  4  and t.name = 'HEMANT'
  5  order by 2;

     FILE# CREATION_
---------- ---------
NAME
--------------------------------------------------------------------------------
 11 19-FEB-16
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

  5 07-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf

  6 13-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvg01y_.dbf


SQL> 
RMAN> list backup of tablespace hemant;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68      Full    230.29M    DISK        00:00:42     07-SEP-17      
        BP Key: 68   Status: AVAILABLE  Compressed: YES  Tag: TAG20170907T230339
        Piece Name: /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
  List of Datafiles in backup set 68
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 7608466    07-SEP-17 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

RMAN> 


What happens if I lose all three datafiles ?

[oracle@ora11204 datafile]$ pwd
/u01/app/oracle/oradata/ORCL/datafile
[oracle@ora11204 datafile]$ ls -l *hemant*
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:31 o1_mf_hemant_dvktv2jd_.dbf
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:31 o1_mf_hemant_dvktvw02_.dbf
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:35 o1_mf_hemant_dvkvg01y_.dbf
[oracle@ora11204 datafile]$ rm *hemant*
[oracle@ora11204 datafile]$ ls -l *hemant*
ls: cannot access *hemant*: No such file or directory
[oracle@ora11204 datafile]$ 
[oracle@ora11204 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 13 16:40:02 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL> 


The SHUTDOWN doesn't report an error for all three files, it only reports the error for the first datafile to "fail".

Let me try to startup and open the database.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size      2260088 bytes
Variable Size    750781320 bytes
Database Buffers   310378496 bytes
Redo Buffers      5517312 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf'


SQL> 


Here, too, it only reports the error for the first datafile to fail to open. I must query V$RECOVER_FILE.

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR             CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
  5 ONLINE  ONLINE
FILE NOT FOUND           0


  6 ONLINE  ONLINE
FILE NOT FOUND           0


 11 ONLINE  ONLINE
FILE NOT FOUND           0



SQL> 


So, now I switch to RMAN.

RMAN> list backup of tablespace hemant;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68      Full    230.29M    DISK        00:00:42     07-SEP-17      
        BP Key: 68   Status: AVAILABLE  Compressed: YES  Tag: TAG20170907T230339
        Piece Name: /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
  List of Datafiles in backup set 68
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 7608466    07-SEP-17 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

RMAN> 
RMAN> restore datafile 11;

Starting restore at 13-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf
channel ORA_DISK_1: reading from backup piece /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
channel ORA_DISK_1: piece handle=/u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp tag=TAG20170907T230339
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 13-SEP-17

RMAN> restore datafile 5;

Starting restore at 13-SEP-17
using channel ORA_DISK_1

creating datafile file number=5 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-SEP-17

RMAN> restore datafile 6;

Starting restore at 13-SEP-17
using channel ORA_DISK_1

creating datafile file number=6 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvg01y_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-SEP-17

RMAN> 


Ignore the "restore not done; all files ..." error message. It's misleading.   But note how the RESTORE command actually did a "CREATING DATAFILE" operation.  Also note that these are OMF Files.

RMAN> recover datafile 11;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 180 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_180_dv2r1ywg_.arc
archived log for thread 1 with sequence 181 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_181_dv2r2f1r_.arc
archived log for thread 1 with sequence 182 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_182_dv2r2os5_.arc
archived log for thread 1 with sequence 183 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_183_dv2r62d0_.arc
archived log for thread 1 with sequence 184 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_184_dv2r68hf_.arc
archived log for thread 1 with sequence 185 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_185_dv2r97r0_.arc
archived log for thread 1 with sequence 186 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc
archived log for thread 1 with sequence 187 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc
archived log for thread 1 with sequence 188 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc
archived log for thread 1 with sequence 189 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc
archived log for thread 1 with sequence 190 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc
archived log for thread 1 with sequence 191 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc
archived log for thread 1 with sequence 192 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc
archived log for thread 1 with sequence 193 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc
archived log for thread 1 with sequence 194 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc
archived log for thread 1 with sequence 195 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc
archived log for thread 1 with sequence 196 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc
archived log for thread 1 with sequence 197 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc
archived log for thread 1 with sequence 198 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_198_dvktx82r_.arc
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_180_dv2r1ywg_.arc thread=1 sequence=180
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_181_dv2r2f1r_.arc thread=1 sequence=181
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_182_dv2r2os5_.arc thread=1 sequence=182
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_183_dv2r62d0_.arc thread=1 sequence=183
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_184_dv2r68hf_.arc thread=1 sequence=184
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_185_dv2r97r0_.arc thread=1 sequence=185
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc thread=1 sequence=186
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc thread=1 sequence=187
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc thread=1 sequence=188
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc thread=1 sequence=189
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc thread=1 sequence=190
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc thread=1 sequence=191
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc thread=1 sequence=192
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc thread=1 sequence=193
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc thread=1 sequence=194
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc thread=1 sequence=195
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc thread=1 sequence=196
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc thread=1 sequence=197
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-SEP-17

RMAN> 
RMAN> recover datafile 5;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 186 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc
archived log for thread 1 with sequence 187 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc
archived log for thread 1 with sequence 188 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc
archived log for thread 1 with sequence 189 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc
archived log for thread 1 with sequence 190 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc
archived log for thread 1 with sequence 191 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc
archived log for thread 1 with sequence 192 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc
archived log for thread 1 with sequence 193 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc
archived log for thread 1 with sequence 194 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc
archived log for thread 1 with sequence 195 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc
archived log for thread 1 with sequence 196 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc
archived log for thread 1 with sequence 197 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc
archived log for thread 1 with sequence 198 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_198_dvktx82r_.arc
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc thread=1 sequence=186
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc thread=1 sequence=187
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc thread=1 sequence=188
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc thread=1 sequence=189
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc thread=1 sequence=190
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc thread=1 sequence=191
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc thread=1 sequence=192
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc thread=1 sequence=193
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc thread=1 sequence=194
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc thread=1 sequence=195
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc thread=1 sequence=196
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc thread=1 sequence=197
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-SEP-17

RMAN> 
RMAN> recover datafile 6;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 13-SEP-17

RMAN> 


For datafile 5, I had all the ArchiveLogs (and Online Redo Logs) since the datafile was created (on 07-Sep).
For datafile 6, since it had only been recently created, the only redo to be applied was from the Online Redo Log (not yet archived). See the alert log message :

alter database recover if needed
 datafile 6
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 199 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/ORCL/clone_o1_mf_1_91zfcp2o_.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
 datafile 6



I should now be able to bring the datafiles online.

RMAN> sql 'alter database datafile 11 online';

sql statement: alter database datafile 11 online

RMAN> sql 'alter database datafile 5 online ';

sql statement: alter database datafile 5 online 

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN> 
RMAN> sql 'alter database open';

sql statement: alter database open

RMAN> 


I can query V$RECOVER_FILE now :

SQL> select * from v$recover_file;

no rows selected

SQL> 


So, even though I did not have backups of datafiles 5 and 6, as long as I had all the redo (ArchiveLogs and Online Redo Logs) for actions against these datafiles, I could restore and recover them.

SQL> select v.file#, v.creation_time,  v.name
  2  from v$datafile  v, v$tablespace t
  3  where v.ts#=t.ts#
  4  and t.name = 'HEMANT'
  5  order by 2;

     FILE# CREATION_
---------- ---------
NAME
--------------------------------------------------------------------------------
 11 19-FEB-16
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvyx0y_.dbf

  5 07-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvz8tz_.dbf

  6 13-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvzhnq_.dbf


SQL> 


As these are OMF Files, the actual file name created can well be different from what it was earlier.


Question :  What if I had to do a Database Point In Time Recovery ?  Would this method still work ?  Test it yourself and come back with your comments.

.
.
.

01 September, 2017

ASM, DiskGroup, AU Size, Tablespace and Table Extents

Creating a new DiskGroup, specifying the Allocation Unit Size for it, creating a Tablespace in a Pluggable Database and creating multiple Segments (Tables) with multiple Extents.

I have added a new disk to my 12.1 VM.

SQL> select path, name, total_mb, header_status, state, group_number
  2  from v$asm_disk
  3  order by 1
  4  /

PATH
--------------------------------------------------------------------------------
NAME                             TOTAL_MB HEADER_STATU STATE    GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000                            5114 MEMBER       NORMAL              1

/dev/asm-disk2
DATA_0001                            5114 MEMBER       NORMAL              1

/dev/asm-disk3
FRA_0000                             5114 MEMBER       NORMAL              2

/dev/asm-disk4
OCRVOTE_0000                         5114 MEMBER       NORMAL              3

/dev/asm-disk5
DATA_0002                           12284 MEMBER       NORMAL              1

/dev/asm-disk6
                                        0 CANDIDATE    NORMAL              0


6 rows selected.

SQL>

The new disk is /dev/asm-disk6.
I create an ASM DiskGroup with AU Size of 1MB.

SQL> create diskgroup NEWDG_1M_AU external redundancy
  2  disk '/dev/asm-disk6'
  3  attribute 'compatible.asm'='12.1', 'compatible.rdbms'='12.1','au_size'='1M';

Diskgroup created.

SQL>
SQL> select path, name, total_mb, header_status, state, group_number
  2  from v$asm_disk
  3  order by 1
  4  /

PATH
--------------------------------------------------------------------------------
NAME                             TOTAL_MB HEADER_STATU STATE    GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000                            5114 MEMBER       NORMAL              1

/dev/asm-disk2
DATA_0001                            5114 MEMBER       NORMAL              1

/dev/asm-disk3
FRA_0000                             5114 MEMBER       NORMAL              2

/dev/asm-disk4
OCRVOTE_0000                         5114 MEMBER       NORMAL              3

/dev/asm-disk5
DATA_0002                           12284 MEMBER       NORMAL              1

/dev/asm-disk6
NEWDG_1M_AU_0000                     2149 MEMBER       NORMAL              4


6 rows selected.

SQL>
SQL> select group_number, name, sector_size, block_size, allocation_unit_size, state
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE
-------------------- -----------
           1 DATA                                   512       4096
             1048576 MOUNTED

           2 FRA                                    512       4096
             1048576 MOUNTED

           3 OCRVOTE                                512       4096
             1048576 MOUNTED

           4 NEWDG_1M_AU                            512       4096
             1048576 MOUNTED


SQL>

I then create a Tablespace in a Pluggable Database specifically on this DG, instead of the default location for new Tablespace datafiles.

SQL> alter session set container=PDB;

Session altered.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATA
SQL>
SQL> create tablespace NEWTS_ON_1M_AU
  2  datafile '+NEWDG_1M_AU'
  3  extent management local autoallocate segment space management auto;

Tablespace created.

SQL>
SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2851
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           1987

SQL>
SQL> drop tablespace NEWTS_ON_1M_AU including contents and datafiles;

Tablespace dropped.

SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2851
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           2089

SQL>
SQL> create tablespace NEWTS_ON_1M_AU
  2  datafile '+NEWDG_1M_AU'
  3  extent management local autoallocate segment space management auto;

Tablespace created.

SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2851
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           1987

SQL>
SQL> select file_name, bytes/1048576, user_bytes/1048576
  2  from dba_data_files
  3  where tablespace_name = 'NEWTS_ON_1M_AU'
  4  /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
          100                 99


SQL>

Thus, we can see that the Usable Space in the Disk Group is less than the DiskGroup size, even with External Redundancy that specifies no mirroring of ASM Extents. The Usable space in the Disk Group is 1987MB after creating a 100MB datafile.

Bytes available in the datafile are 99MB because a Locally Managed Tablespace reserves space for the Extent Map in the datafile header blocks.  The datafile, belonging to a Tablespace in a Pluggable Database has a path that is identified by the DB_UNIQUE_NAME (RAC) and the PDB GUID as the identifier (44BBC69CE8F552AEE053334EA8C07365)

Now, I create 5 small tables, each with 5 extents of 64KB in the Tablespace.  I use DEFERRED_SEGMENT_CREATION=FALSE  to force creation of the Segment and allocation of all the Extents.

SQL> connect hemant/hemant@PDB
Connected.
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL> create table t1 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t2 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t3 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t4 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t5 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL>
SQL>
SQL> connect system/manager@PDB

Connected.
SQL> select sum(bytes)/1024,  count(*)
  2  from dba_extents
  3  where tablespace_name = 'NEWTS_ON_1M_AU'
  4  /

SUM(BYTES)/1024   COUNT(*)
--------------- ----------
          25600         25

SQL> select file_name, bytes/1048576, user_bytes/1048576
  2  from dba_data_files
  3  where tablespace_name = 'NEWTS_ON_1M_AU'
  4  /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
          100                 99


SQL> select group_number, name, total_mb, usable_file_mb
  2  from v$asm_diskgroup
  3  order by 1
  4  /

GROUP_NUMBER NAME                             TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
           1 DATA                                22512          12865
           2 FRA                                  5114           2832
           3 OCRVOTE                              5114           4820
           4 NEWDG_1M_AU                          2149           1987

SQL>


Thus, a short demo of :
1.  Creating a new ASM DiskGroup with a specified AU Size
2.  Creating a Tablespace in the new DG (not being the default location)
3.  Creating multiple tables with pre-allocated Extents
4.  Verifying the Usable Space in the DiskGroup and Datafile
.
.
.


05 August, 2017

12c MultiTenant Posts -- 7 : Adding Custom Service to PDB (nonRAC/GI)

Earlier I have already demonstrated adding and managing custom services in a RAC environment in a blog post and a video.

But what if you are running Single Instance and not using Grid Infrastructure?  The srvctl command in Grid Infrastructure is what you'd use to add and manage services in RAC and Oracle Restart environments.  But without Grid Infrastructure, you can fall back on DBMS_SERVICE.

The DBMS_SERVICE API has been available since Oracle 8i -- when Services were introduced.

Here is a quick demo of some facilities with DBMS_SERVICE.

1.  Adding a Custom Service into a PDB :

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 22:52:21 2017

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

Last Successful login time: Mon Jul 10 2017 22:22:30 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_id  

CON_ID
------------------------------
4
SQL> 
SQL> execute dbms_service.create_service('HR','HR');

PL/SQL procedure successfully completed.

SQL> execute dbms_service.start_service('HR');

PL/SQL procedure successfully completed.

SQL> 


Connecting to the service via tnsnames.

SQL> connect hemant/hemant@HR
Connected.
SQL> show con_id

CON_ID
------------------------------
4
SQL> 


2.  Disconnecting all connected users on the Service

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 23:02:47 2017

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

Last Successful login time: Sat Aug 05 2017 23:02:28 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
SQL> execute dbms_service.disconnect_session(-
> service_name=>'HR',disconnect_option=>DBMS_SERVICE.IMMEDIATE);

PL/SQL procedure successfully completed.

SQL> 
In the HEMANT session connected to HR :
SQL> show con_id
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 5062
Session ID: 67 Serial number: 12744


SP2-1545: This feature requires Database availability.
SQL> 


(Instead of DBMS_SERVICE.IMMEDIATE, we could also specify DBMS_SERVICE.POST_TRANSACTION).


3.  Shutting down a Service without closing the PDB :

SQL> execute dbms_service.stop_service('HR');

PL/SQL procedure successfully completed.

SQL> 
SQL> connect hemant/hemant@HR
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL> 


Does restarting the Database, restart this custom service?

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size      8798312 bytes
Variable Size    343936920 bytes
Database Buffers   478150656 bytes
Redo Buffers      7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable databas all open;
alter pluggable databas all open
                *
ERROR at line 1:
ORA-02000: missing DATABASE keyword


SQL> alter pluggable database all open;

Pluggable database altered.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> connect hemant/hemant@HR
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL> 
SQL> connect system/oracle@NEWPDB
Connected.
SQL> execute dbms_service.start_service('HR');

PL/SQL procedure successfully completed.

SQL> connect hemant/hemant@HR
Connected.
SQL> 


I had to reSTART this custom service ('HR') after the PDB was OPENed.

Services is a facility that has been available since 8i non-OPS.  However, Services were apparently only being used by most sites in RAC environments.

Services allow you to run multiple "applications" (each application advertised as a Service) within the same (one) database.

Note that, in a RAC environment, srvctl configuration of Services can configure auto-restart of the Service.


UPDATE : A Video using a different PDB (in the same CDB) and ServiceName is posted here.
.
.
.

10 July, 2017

12c MultiTenant Posts -- 6 : Partial (aka Subset) Cloning of PDB

Note : This is a 12.2 feature.

Normally, if you clone a PDB, you'd get a full copy with all the tablespaces.  Now, in 12.2, you can exclude non-essential tablespaces by specifying USER TABLESPACES -- those that you want cloned.  (SYSTEM, SYSAUX and Local UNDO will certainly be cloned).

Let me start with the "NEWPDB" PDB (that I've used in previous examples) that has one more schema and tablespace:

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 10 21:52:55 2017

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

Last Successful login time: Mon Jul 10 2017 11:04:00 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select tablespace_name from dba_tablespaces order by 1;

TABLESPACE_NAME
------------------------------
HRDATA
MYDATA
SYSAUX
SYSTEM
TEMP
UNDOTBS1

6 rows selected.

SQL> col owner format a8           
SQL> col segment_name format a30
SQL> col tablespace_name format a8
SQL> select owner, segment_name, tablespace_name
  2  from dba_segments
  3  where tablespace_name like '%DATA'
  4  order by 1,2
  5  /

OWNER  SEGMENT_NAME   TABLESPA
-------- ------------------------------ --------
HEMANT  BIN$UVb24iaCIE/gUwEAAH/WaQ==$0 MYDATA
HEMANT  BIN$UVb24iaIIE/gUwEAAH/WaQ==$0 MYDATA
HEMANT  HKC_STORE_FILE   MYDATA
HEMANT  I    MYDATA
HEMANT  OBJ_LIST   MYDATA
HEMANT  SYS_IL0000073525C00003$$ MYDATA
HEMANT  SYS_IL0000073532C00003$$ MYDATA
HEMANT  SYS_IL0000073535C00003$$ MYDATA
HEMANT  SYS_LOB0000073525C00003$$ MYDATA
HEMANT  SYS_LOB0000073532C00003$$ MYDATA
HEMANT  SYS_LOB0000073535C00003$$ MYDATA
HEMANT  T    MYDATA
HR  EMPLOYEES   HRDATA

13 rows selected.

SQL> 
SQL> select * from hr.employees;

EMPLOYEE_ID FIRST_NAME      LAST_NAME
----------- ------------------------------ ------------------------------
HIRE_DATE DEPARTMENT_ID     SALARY EMAIL_ID
--------- ------------- ---------- ---------------------------------------------
   1 Hemant      Chitale
06-JUL-17       1      15000 hemant@mydomain.com


SQL> 


Besides, the HEMANT objects in the MYDATA tablespace, I now have HR owning an EMPLOYEES table in the HRDATA tablespace.

Now, I want to clone the NEWPDB tablespace but want to exclude HR data.

First, I set a target location for the datafiles.

$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 10 21:57:33 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter db_create_file

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest       string
SQL> alter session set db_create_file_dest='/u02/oradata';

Session altered.

SQL> 


Next, I create my Partial (or SubSet) Clone PDB:

SQL> create pluggable database NONHR from NEWPDB user_tablespaces=('MYDATA');

Pluggable database created.

SQL> 
SQL> select con_id, file#, name
  2  from v$datafile
  3  order by 1,2
  4  /

    CON_ID FILE#
---------- ----------
NAME
--------------------------------------------------------------------------------
  1     1
/u01/app/oracle/oradata/orcl12c/system01.dbf

  1     3
/u01/app/oracle/oradata/orcl12c/sysaux01.dbf

  1     7
/u01/app/oracle/oradata/orcl12c/users01.dbf

  1    15
/u01/app/oracle/oradata/orcl12c/undotbs2.dbf

  2     5
/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf

  2     6
/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf

  2     8
/u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

  3     9
/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf

  3    10
/u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf

  3    11
/u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf

  3    12
/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf

  3    13
/u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf

  3    14
/u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf

  4    16
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di

  4    17
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2

  4    18
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1

  4    19
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

  4    20
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-HRDATA_FNO-20_0ds7a8e5

  5    21
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_system_dp72
3vp5_.dbf

  5    22
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_sysaux_dp72
3vsz_.dbf

  5    23
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_undotbs1_dp
723vt1_.dbf

  5    24
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_mydata_dp72
3vt3_.dbf


22 rows selected.

SQL> 
SQL> select con_id, name, open_mode
  2  from v$pdbs
  3  order by 1
  4  /

    CON_ID
----------
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
  2
PDB$SEED
READ ONLY

  3
ORCL
READ WRITE

  4
NEWPDB
READ WRITE

  5
NONHR
MOUNTED


SQL> 
SQL> alter pluggable database nonhr open;

Pluggable database altered.

SQL> 


I can identify the new PDB "NONHR" as CON_ID=5.
This is a Subset Clone that is a Copy with Data.

Let's create the TNSNAMES.ORA entry for NONHR:

NONHR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = nonhr)
    )
  )


Let's now connect to NONHR and confirm its contents.

SQL> connect system/oracle@NONHR
Connected.
SQL> show con_id

CON_ID
------------------------------
5
SQL> show con_name

CON_NAME
------------------------------
NONHR

SQL> select tablespace_name   
  2  from dba_tablespaces
  3  order by 1 
  4  /

TABLESPACE_NAME
------------------------------
HRDATA
MYDATA
SYSAUX
SYSTEM
TEMP
UNDOTBS1

6 rows selected.

SQL> select file_name from dba_data_files
  2  where tablespace_name = 'HRDATA'
  3  /

no rows selected

SQL> select owner, segment_name, segment_type
  2  from dba_segments
  3  where tablespace_name = 'HRDATA'
  4  /

no rows selected

SQL> 
SQL> select tablespace_name, file_name
  2  from dba_data_files
  3  order by 1
  4  /

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
MYDATA
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_mydata_dp72
3vt3_.dbf

SYSAUX
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_sysaux_dp72
3vsz_.dbf

SYSTEM
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_system_dp72
3vp5_.dbf

UNDOTBS1
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_undotbs1_dp
723vt1_.dbf


SQL> 
SQL> select segment_name, segment_type
  2  from dba_segments
  3  where owner = 'HR'
  4  /

no rows selected

SQL> select username      
  2  from dba_users
  3  where username = 'HR'
  4  /

USERNAME
--------------------------------------------------------------------------------
HR

SQL> 
SQL> select object_name, object_type
  2  from dba_objects
  3  where owner = 'HR'
  4  /

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
EMPLOYEES
TABLE


SQL>
SQL> select owner, segment_name
  2  from dba_segments
  3  where tablespace_name = 'MYDATA'
  4  /

OWNER  SEGMENT_NAME
-------- ------------------------------
HEMANT  BIN$UVb24iaCIE/gUwEAAH/WaQ==$0
HEMANT  BIN$UVb24iaIIE/gUwEAAH/WaQ==$0
HEMANT  HKC_STORE_FILE
HEMANT  I
HEMANT  OBJ_LIST
HEMANT  SYS_IL0000073525C00003$$
HEMANT  SYS_IL0000073532C00003$$
HEMANT  SYS_IL0000073535C00003$$
HEMANT  SYS_LOB0000073525C00003$$
HEMANT  SYS_LOB0000073532C00003$$
HEMANT  SYS_LOB0000073535C00003$$
HEMANT  T

12 rows selected.

SQL> 
SQL> select count(*) from hemant.obj_list;

  COUNT(*)
----------
    145282

SQL> 


So, what has been copied to the NONHR PDB?  The HRDATA Tablespace, but not the DataFile.  The HR User and Table (definition only, no data), but not the Segment.
However, for the MYDATA Tablespace that was identified as a USER_TABLESPACE in the CREATE PLUGGABLE DATABASE statement, the Tablespace, Datafile, User, Table and Segment have all been copied.

Therefore, NONHR does not have the HR data!   I can drop the User and Tablespace.

SQL> drop tablespace hrdata including contents;

Tablespace dropped.

SQL> drop user hr;

User dropped.

SQL> 


However, HR is still present in NEWPDB where NONHR was cloned from:

SQL> connect system/oracle@NEWPDB
Connected.
SQL> select owner, segment_name
  2  from dba_segments
  3  where tablespace_name = 'HRDATA'
  4  /

OWNER  SEGMENT_NAME
-------- ------------------------------
HR  EMPLOYEES

SQL> select * from hr.employees;

EMPLOYEE_ID FIRST_NAME      LAST_NAME
----------- ------------------------------ ------------------------------
HIRE_DATE DEPARTMENT_ID     SALARY EMAIL_ID
--------- ------------- ---------- ---------------------------------------------
   1 Hemant      Chitale
06-JUL-17       1      15000 hemant@mydomain.com


SQL> show con_id

CON_ID
------------------------------
4
SQL> show con_name

CON_NAME
------------------------------
NEWPDB
SQL> 
SQL> select tablespace_name, file_name
  2  from dba_data_files
  3  order by 1
  4  /

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
HRDATA
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-HRDATA_FNO-20_0ds7a8e5

MYDATA
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

SYSAUX
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2

SYSTEM
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di

UNDOTBS1
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1


SQL> 


So, 12.2 introduces the ability to create a clone PDB database that is a SubSet (i.e. selected User Tablespaces data) of an existing PDB.

(Note : NEWPDB is in /u03 where it was moved from /u02 earlier as a Relocated Database while NONHR is in /u02 where it was created with OMF based on DB_CREATE_FILE_DEST).
.
.
.

08 July, 2017

02 July, 2017

12c MultiTenant Posts -- 5 : Flashback a PDB

12.2 allows FLASHBACK DATABASE for a Pluggable Database.

Note that ALTER DATABASE FLASHBACK ON is not enabled by default in a newly created database -- you must issue this command at the CDB level to enable Flashback for all the Pluggable Databases.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
OBJ_LIST
HKC_STORE_FILE
T

SQL> select count(*) from obj_list;

  COUNT(*)
----------
    145282

SQL> select  count(*) from hkc_store_file;

  COUNT(*)
----------
  1

SQL> truncate table obj_list;

Table truncated.

SQL> drop table hkc_store_file;

Table dropped.

SQL> 


Now, let me Flashback the PDB.

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database newpdb close;

Pluggable database altered.

SQL> flashback pluggable database newpdb to timestamp sysdate-3/1440;

Flashback complete.

SQL> alter pluggable database newpdb open;
alter pluggable database newpdb open
*
ERROR at line 1:
ORA-01113: file 19 needs media recovery
ORA-01110: data file 19:
'/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4'


SQL> alter pluggable database newpdb open resetlogs;

Pluggable database altered.

SQL> 


Let me test the data.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select count(*) from obj_list;

  COUNT(*)
----------
    145282

SQL> select count(*) from hkc_store_file;

  COUNT(*)
----------
  1

SQL> 


Yes, the FLASHBACK DATABASE is successful.

What are the pre-requisites ?

1.  ALTER DATABASE FLASHBACK ON  at the CDB
2.  ARCHIVELOG at the CDB
3.  LOCAL UNDO enabled -- highly recommended else a subsequent Point In Time Recovery of the CDB may prevent OPENing the PDB
4.  OPEN RESETLOGS for the PDB

.
.
.

21 June, 2017

12c MultiTenant Posts -- 4 : Relocate Individual PDB using RMAN

Given the current location of a PDB :

SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7
f8go_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7
f8hf_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dk
j7f8hg_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbc
p0wz_.dbf

FILE_NAME
--------------------------------------------------------------------------------


SQL> 


I can use RMAN to relocate it. First I take an Image Copy Backup to the new location

RMAN> backup as copy pluggable database newpdb format '/u03/oradata/NEWPDB/%U';

Starting backup at 20-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2 tag=TAG20170620T231338 RECID=4 STAMP=947200428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di tag=TAG20170620T231338 RECID=5 STAMP=947200441
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1 tag=TAG20170620T231338 RECID=6 STAMP=947200451
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4 tag=TAG20170620T231338 RECID=7 STAMP=947200454
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 20-JUN-17

Starting Control File and SPFILE Autobackup at 20-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_20/o1_mf_s_947200455_dnms48pp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-JUN-17

RMAN> 


Then I switch the database file pointers to the new location.

RMAN> alter pluggable database newpdb close;

Statement processed

RMAN> switch pluggable database newpdb to  copy;

datafile 16 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di"
datafile 17 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2"
datafile 18 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1"
datafile 19 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4"

RMAN> recover pluggable database newpdb;

Starting recover at 20-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 20-JUN-17

RMAN> alter pluggable database newpdb open;

Statement processed

RMAN>


I can now verify the new location for the database files.

 
SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

SQL> 


The datafiles at the old location still remain and can be manually deleted later as they are no longer part of the database.  Note that those datafiles are still registered by RMAN as COPY

RMAN> list copy of pluggable database newpdb;

using target database control file instead of recovery catalog
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time        Sparse
------- ---- - --------------- ---------- --------------- ------
8       16   A 20-JUN-17       2188274    20-JUN-17       NO    
        Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
        Container ID: 4, PDB Name: NEWPDB

9       17   A 20-JUN-17       2188274    20-JUN-17       NO    
        Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
        Container ID: 4, PDB Name: NEWPDB

10      18   A 20-JUN-17       2188274    20-JUN-17       NO    
        Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
        Container ID: 4, PDB Name: NEWPDB

11      19   A 20-JUN-17       2188274    20-JUN-17       NO    
        Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
        Container ID: 4, PDB Name: NEWPDB


RMAN> 


If I manually delete the old location files, I'd need to also delete them from the RMAN Registry.  Alternatively, I can directly delete them from RMAN.

RMAN> delete copy of pluggable database newpdb;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=278 device type=DISK
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time        Sparse
------- ---- - --------------- ---------- --------------- ------
8       16   A 20-JUN-17       2188274    20-JUN-17       NO    
        Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
        Container ID: 4, PDB Name: NEWPDB

9       17   A 20-JUN-17       2188274    20-JUN-17       NO    
        Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
        Container ID: 4, PDB Name: NEWPDB

10      18   A 20-JUN-17       2188274    20-JUN-17       NO    
        Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
        Container ID: 4, PDB Name: NEWPDB

11      19   A 20-JUN-17       2188274    20-JUN-17       NO    
        Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
        Container ID: 4, PDB Name: NEWPDB


Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf RECID=8 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf RECID=9 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf RECID=10 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf RECID=11 STAMP=947200522
Deleted 4 objects


RMAN> 


I still need to relocate the TEMP Tablespace Tempfile.

SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_temp_dkj7f8
hg_.dbf


SQL> alter tablespace temp add tempfile '/u03/oradata/NEWPDB/temp01.dbf' size 100M;

Tablespace altered.

SQL>  
SQL> alter tablespace temp drop tempfile '/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_temp_dkj7f8hg_.dbf';

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u03/oradata/NEWPDB/temp01.dbf

SQL> 


So, I used the same Image Copy method we'd use in 11g databases to relocate a 12c Pluggable Database when within the same server and storage.

To verify that I can access the PDB in the new location :

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
OBJ_LIST
HKC_STORE_FILE
T

SQL> insert into obj_list select * from obj_list;

72641 rows created.

SQL> commit;

Commit complete.

SQL> 


.
.

07 June, 2017

12c MultiTenant Posts -- 3 : Restore Individual PDB

Restoring the single PDB in a Container Database.

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 7 06:34:47 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL12C (DBID=768045447)

RMAN> restore pluggable database newpdb;

Starting restore at 07-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00016 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
channel ORA_DISK_1: restoring datafile 00017 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
channel ORA_DISK_1: restoring datafile 00018 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
channel ORA_DISK_1: restoring datafile 00019 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp tag=TAG20170605T061347
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 07-JUN-17

RMAN> recover pluggable database newpdb;

Starting recover at 07-JUN-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_06/o1_mf_1_16_dmgyksjy_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_17_dmhj6nbk_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp tag=TAG20170605T061438
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_15_dmhoqlq3_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_15_dmhoqlq3_.arc RECID=4 STAMP=946017330
media recovery complete, elapsed time: 00:00:11
Finished recover at 07-JUN-17

RMAN> alter pluggable database newpdb open;

Statement processed

RMAN> 


I had connected to the root to restore the PDB.
.
.

.

05 June, 2017

12c MultiTenant Posts -- 2 : Backup individual PDB

In the 12c MultiTenant architecture, you can backup (and restore) individual PDBs.  However, you must also backup the CDB (Root) !  You cannot restore a PDB as a "standalone" database --- it has to be restored back to the CDB.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size      8798312 bytes
Variable Size    343936920 bytes
Database Buffers   478150656 bytes
Redo Buffers      7974912 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter pluggable database newpdb open;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$
$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 5 06:10:17 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL12C (DBID=768045447)

RMAN> backup database root;

Starting backup at 05-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=270 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl12c/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl12c/sysaux01.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/orcl12c/undotbs2.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUN-17
channel ORA_DISK_1: finished piece 1 at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061033_dmbcjttq_.bkp tag=TAG20170605T061033 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 05-JUN-17

Starting Control File and SPFILE Autobackup at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_05/o1_mf_s_945843079_dmbcl8fm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-17

RMAN> 
RMAN> backup pluggable database newpdb;

Starting backup at 05-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
input datafile file number=00016 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
input datafile file number=00018 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
input datafile file number=00019 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUN-17
channel ORA_DISK_1: finished piece 1 at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp tag=TAG20170605T061347 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 05-JUN-17

Starting Control File and SPFILE Autobackup at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_05/o1_mf_s_945843252_dmbcqo6t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-17

RMAN> 
RMAN> backup archivelog all delete input;

Starting backup at 05-JUN-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=1 STAMP=945843278
channel ORA_DISK_1: starting piece 1 at 05-JUN-17
channel ORA_DISK_1: finished piece 1 at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp tag=TAG20170605T061438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_05/o1_mf_1_15_dmbcrfq1_.arc RECID=1 STAMP=945843278
Finished backup at 05-JUN-17

Starting Control File and SPFILE Autobackup at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_05/o1_mf_s_945843280_dmbcrjdg_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-17

RMAN> 


Thus, a PDB in a MultiTenant environment can be backed-up but the Root must always be backed up.
.
.
.

14 May, 2017

12c MultiTenant Posts -- 1 : Creating a PDB in a different directory

What if the current (default) location for Pluggable Databases is running out of space and, yet, you want to create an Oracle Managed Files Pluggable Database ?

First, I start with this configuration :

SQL> select con_id, file#, substr(name,1,56)
  2  from v$datafile
  3  order by 1,2;

    CON_ID FILE#
---------- ----------
SUBSTR(NAME,1,56)
--------------------------------------------------------------------------------
  1     1
/u01/app/oracle/oradata/orcl12c/system01.dbf

  1     3
/u01/app/oracle/oradata/orcl12c/sysaux01.dbf

  1     7
/u01/app/oracle/oradata/orcl12c/users01.dbf

  1    15
/u01/app/oracle/oradata/orcl12c/undotbs2.dbf

  2     5
/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf

  2     6
/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf

  2     8
/u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

  3     9
/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf

  3    10
/u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf

  3    11
/u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf

  3    12
/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf

  3    13
/u01/app/oracle/oradata/orcl12c/orcl/APEX_19913751733706

  3    14
/u01/app/oracle/oradata/orcl12c/orcl/APEX_19931956603709


13 rows selected.

SQL> 


Currently, I have one Pluggable Database (CON_ID=3) but none of the database files are OMF.  I don't have enough space in /u01 and want to create the new PDB in /u02 and also use OMF.  As long as I have /u02/oradata precreated by the system administrator, I can :

SQL> show parameter db_create_file

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest       string
SQL> alter session set db_create_file_dest='/u02/oradata'; 

Session altered.

SQL> create pluggable database NEWPDB admin user newpdb_adm identified by newpdb_adm;

Pluggable database created.

SQL> alter pluggable database NEWPDB open;

Pluggable database altered.

SQL> select con_id, open_mode 
  2  from v$pdbs
  3  where name = 'NEWPDB'
  4  /

    CON_ID OPEN_MODE
---------- ----------
  4 READ WRITE

SQL> 
SQL> select file#, name     
  2  from v$datafile
  3  where con_id=4
  4  order by file#
  5  /

     FILE#
----------
NAME
--------------------------------------------------------------------------------
 16
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7
f8go_.dbf

 17
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7
f8hf_.dbf

 18
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dk
j7f8hg_.dbf


SQL> 
SQL> show parameter db_unique_name

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_unique_name        string  orcl12c
SQL> 


Note how the parent folder name "ORCL12C" is inherited from the DB_UNIQUE_NAME.  I can now proceed to setup this new PDB.  Later, I  can migrate it as an OMF PDB to another Container Database.
.
.
.

08 May, 2017

12cR1 RAC Posts -- 10 : Video on Database Startup

I've created a short video on manually starting up a RAC database that has a PDB with two custom services.  If a database is shutdown before the cluster is shutdown, the cluster startup does not automatically startup the database instance, so I demonstrate a manual startup.

See the video at:  https://youtu.be/saFvo9QhYSI


.
.
.

30 April, 2017

12cR1 RAC Posts -- 9 : Adding a Service to a PDB in RAC

My 2node RAC environment has 1 PDB.  Here I add (create) a new Service to the PDB.

Oracle recommends using srvctl instead of DBMS_SERVICE to add a Service.

Can I add a service without having the PDB OPEN ?

[oracle@collabn1 ~]$ srvctl start database -d RAC
[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:16:01 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB                            MOUNTED

SQL> alter pluggable database pdb open;  -- command issued in both instances

Pluggable database altered.

SQL>
SQL> select con_id, pdb, name, creation_date, clb_goal
  2  from v$services
  3  where pdb='PDB'
  4  /

    CON_ID PDB
---------- ------------------------------
NAME                                                             CREATION_ CLB_G
---------------------------------------------------------------- --------- -----
         3 PDB
pdb.racattack                                                    29-DEC-16 LONG


SQL>


I add the Service to the TNSNAMES.ORA and try to connect to it.

MYSVC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYSVC.racattack)
    )
  )

[oracle@collabn1 ~]$ tnsping MYSVC

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-APR-2017 23:22:43

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYSVC.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:22:51 2017

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:


So, apparently, the service isn't running.   Shall I try re-adding the service ?

[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
PRKO-3117 : Service MYSVC already exists in database RAC
[oracle@collabn1 ~]$ srvctl remove service -db RAC -service MYSVC -pdb PDB
PRKO-2002 : Invalid command line option: -pdb
[oracle@collabn1 ~]$  srvctl remove service -db RAC -service MYSVC
[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:31:15 2017

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: 


Yet, it doesn't work !   How do I add and start service to a PDB ?  What's missing ?

[oracle@collabn1 ~]$ srvctl remove service -db RAC -service MYSVC
[oracle@collabn1 ~]$ srvctl start service -db RAC -service MYSVC
[oracle@collabn1 ~]$

SQL> select distinct name from v$services;

NAME
----------------------------------------------------------------
pdb.racattack
RAC.racattack
MYSVC
SYS$BACKGROUND
RAC_DGB
RACXDB
SYS$USERS


[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:43:01 2017

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

Last Successful login time: Sun Apr 16 2017 23:30:21 +08:00

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

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA
MY_NEW_TABLE

SQL>


I had forgotten to *START* the service !   Let me go back and add another service with the PDB CLOSEd.

SQL> alter pluggable database pdb close immediate;  -- on both instances

Pluggable database altered.

SQL>

[oracle@collabn1 ~]$ srvctl add service -db RAC -pdb PDB -service NEWSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT
[oracle@collabn1 ~]$

SQL> alter pluggable database pdb open;  -- on both instances

Pluggable database altered.

SQL>

NEWSVC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NEWSVC.racattack)
    )
  )

[oracle@collabn1 ~]$ srvctl start service -db RAC -service NEWSVC
[oracle@collabn1 ~]$ tnsping NEWSVC

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-APR-2017 23:54:38

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NEWSVC.racattack)))
OK (0 msec)


[oracle@collabn1 ~]$ sqlplus hemant/hemant@NEWSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:55:25 2017

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

Last Successful login time: Sat Apr 29 2017 23:54:51 +08:00

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

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA
MY_NEW_TABLE

SQL>


However, these entries in PDB_PLUG_IN_VIOLATIONS seem to be a bug :

SQL> alter pluggable database pdb close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb open;

Pluggable database altered.

SQL> select * from pdb_plug_in_violations;

TIME
---------------------------------------------------------------------------
NAME
------------------------------
CAUSE                                                            TYPE
---------------------------------------------------------------- ---------
ERROR_NUMBER       LINE
------------ ----------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
ACTION
--------------------------------------------------------------------------------
29-APR-17 11.58.32.409572 PM
PDB
Service Name Conflict                                            WARNING
           0          1
Service name or network name of service MYSVC in the PDB is invalid or conflicts
 with an existing service name or network name in the CDB.
PENDING
Drop the service and recreate it with an appropriate name.

29-APR-17 11.58.32.412142 PM
PDB
Service Name Conflict                                            WARNING
           0          2
Service name or network name of service NEWSVC in the PDB is invalid or conflict
s with an existing service name or network name in the CDB.
PENDING
Drop the service and recreate it with an appropriate name.


SQL>


So, remember, it is not sufficient to ADD a Service. You must also START the Service using srvctl.
.
.
.