Refreshable Clone PDB -- 1 Manual Refresh

 The facility of creating Clone PDBs that are Refreshable either "on-demand" or to a set schedule was introduced in 12.2.  The Refreshable PDB can be opened only as a Read-Only database

This is somewhat similar to having a Standby Database that can be opened Read-Only for queries and refreshed as and when on-demand (without using the Active Data Guard License)

Here is a quick demo in 19.12 on Linux

First, on the Source database ORCLCDB I verify that I have the required configuration of Local Undo and ArchiveLog mode and then setup a Common account with the requisite privileges:

oracle19c>sqlplus sys/manager@ORCLCDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 30 23:01:41 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> col property_value format a16
SQL> select property_value, log_mode, force_logging
  2  from database_properties, v$database
  3  where property_name = 'LOCAL_UNDO_ENABLED'
  4  /

PROPERTY_VALUE   LOG_MODE     FORCE_LOGGING
---------------- ------------ ---------------------------------------
TRUE             ARCHIVELOG   YES

SQL>
SQL> alter pluggable database orclpdb1 open  -- verify or open the source pdb
  2  /
alter pluggable database orclpdb1 open  -- verify or open the source pdb
*
ERROR at line 1:
ORA-65019: pluggable database ORCLPDB1 already open


SQL>
SQL> create user c##for_pdb_clones identified by for_pdb_clones
  2  default tablespace users temporary tablespace temp
  3  container=ALL   -- a common user for all PDBs
  4  /

User created.

SQL> grant create session, create pluggable database, sysoper -- privileges that are required (not DBA Role)
  2  to c##for_pdb_clones
  3  container=ALL  -- across all PDBs
  4  /

Grant succeeded.

SQL>
SQL> select con_id, privilege, common
  2  from cdb_sys_privs  -- check across all PDBs
  3  where grantee = 'C##FOR_PDB_CLONES'
  4  order by 1,2
  5  /

    CON_ID PRIVILEGE                                COM
---------- ---------------------------------------- ---
         1 CREATE PLUGGABLE DATABASE                YES
         1 CREATE SESSION                           YES
         3 CREATE PLUGGABLE DATABASE                YES
         3 CREATE SESSION                           YES

SQL>

Next, on the Target Database CDB2 (where I want to create the Clone PDB), I setup a Database Link and then issue the CREATE PLUGGABLE DATABASE command

oracle19c>sqlplus sys/manager@CDB2 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 30 23:06:41 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> create database link for_pdb_clones  -- create in the Root CDB
  2  connect to c##for_pdb_clones identified by for_pdb_clones using 'ORCLCDB'
  3  /

Database link created.

SQL> create pluggable database ro_pdb  -- the cloned pdb will be Read-Only
  2  from orclpdb1@for_pdb_clones -- create from pluggable database orclpd1
  3  refresh mode manual -- not specifying an auto-refresh interval
  4  -- file_name_convert=('ORCLCDB','CDB2')  -- not required as I am using OMF with db_create_file_dest
  5  /

Pluggable database created.

SQL>
SQL> alter pluggable database ro_pdb open -- this should be opened Read-Only
  2  /
alter pluggable database ro_pdb open -- this should be opened Read-Only
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode


SQL>
SQL> alter pluggable database ro_pdb open read only -- this will succeed
  2  /

Pluggable database altered.

SQL>

So, now the Source PDB ORCLPDB1 has been cloned to CDB2 as a new Pluggable Database RO_PDB.

How do I refresh it ?  I will update the Source and then query the RO_PDB at CDB2

SQL> connect hemant/hemant@ORCLPDB1
ERROR:
ORA-28002: the password will expire within 7 days


Connected.
SQL> create table list_of_objects
  2  as
  3  select * from dba_objects
  4  /

Table created.

SQL> select count(*) from list_of_objects
  2  /

  COUNT(*)
----------
     73645

SQL>
SQL> connect hemant/hemant@ro_pdb
ERROR:
ORA-28032: Your password has expired and the database is set to read-only


Warning: You are no longer connected to ORACLE.
SQL>
--- cannot connect to the Read Only database with an expired password
SQL> connect sys/manager@CDB2 as sysdba
Connected.
SQL> alter session set container=RO_PDB;

Session altered.

SQL> select count(*)
  2  from dba_objects
  3  where owner = 'HEMANT'
  4  /

  COUNT(*)
----------
        32

SQL> select max(created)
  2  from dba_objects
  3  where owner = 'HEMANT'
  4  /

MAX(CREAT
---------
04-SEP-21

SQL>
SQL> desc hemant.list_of_objects
ERROR:
ORA-04043: object hemant.list_of_objects does not exist


SQL>

So, the RO_PDB does not have the new table and does not even allow login with an Expired Password. I will first update my password at the source ORCLPDB1 and then refresh RO_PDB and query again.

SQL> connect hemant/hemant@ORCLPDB1
ERROR:
ORA-28002: the password will expire within 7 days


Connected.
SQL> password hemant
Changing password for hemant
Old password:
New password:
Retype new password:
Password changed
SQL>
SQL> connect sys/manager@CDB2 as sysdba
Connected.
SQL> alter pluggable database ro_pdb close;  -- must CLOSE to allow a REFRESH

Pluggable database altered.

SQL> alter pluggable database ro_pdb refresh;  -- REFRESH command

Pluggable database altered.

SQL> alter pluggable database ro_pdb open read only;

Pluggable database altered.

SQL>
SQL> connect hemant/newhemant@ro_pdb
Connected.
SQL> select count(*) from list_of_objects;

  COUNT(*)
----------
     73645

SQL>

The Refresh of the RO_PDB is a simple process. I simply CLOSE it, issue an ALTER ... REFRESH command and then OEPN READ ONLY again.

Now, RO_PDB allows connections with the new Password and has the updated data *as of the time the REFRESH command is issued*.   Any new data populated in the source or any change made in the source are not available in RO_PDB  until a fresh REFRESH is executed.

SQL Server Migration Assistant for Oracle

 Here's a Video Demo of the SQL Server Migration Assistant for Oracle   https://youtu.be/zNTF1ncr45g  

The tool is available for download here

Indexing Documents in Oracle

 In the previous post, I had demonstrated how to load documents into an Oracle Database.  

(Note : I am referring to documents as in PDF files, PowerPoint presentations, Word documents and text files in the traditional sense, not "JSON documents").

Here I demonstrate how to use Oracle Text to create indexes and make such documents searchable.

I assume that Oracle Text (the "CTXSYS" schema) has been installed when the database was created.

First, I grant the appropriate privileges.

SQL> alter session set container=pdb1;

Session altered.

SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_DOC TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_QUERY TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_REPORT TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_THES TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO MYDOCDB;

Grant succeeded.

SQL>

Next, I create a CONTEXT Index on my documents table.

SQL> connect mydocdb/mydocdb@pdb1
Connected.
SQL> CREATE INDEX my_documents_ctx_idx ON my_documents(file_content)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SQL>

My documents are now indexed and searchable. The syntax for a context index query is slightly different.

SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'DBA Role') > 0
  4  /

DOC_TITLE                                  DOC_DESCRIPTION                            FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Oracle 12c Security                        Security Mechanisms in Oracle 12c          PDF
Monitoring and Diagnostics without OEM     Presentation at Oracle SOS Conference      PDF

SQL>
SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'sqlplus hemant/hemant@orclpdb1') > 0
  4  /

DOC_TITLE                                  DOC_DESCRIPTION                            FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Flashback Database and DG DR Testing       How to use Flasback Database in DataGuard  DOCX
                                           for DR Testing


SQL>
SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'Flex ASM') > 0
  4  /

DOC_TITLE                                  DOC_DESCRIPTION                            FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Oracle 12c Availability                    High Availability Options in Oracle 12c    PDF

SQL>
SQL>
SQL> -- unfortunately, some queries on my plain-text SQL file don't work as expected
SQL>
SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'doc_number number generated always as identity,') > 0
  4  /
select doc_title, doc_description, file_type
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 48


SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'doc_number number generated always as identity') > 0
  4  /

no rows selected

SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'doc_number number generated always') > 0
  4  /

no rows selected

SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, '(doc_number number generated always') > 0
  4  /
select doc_title, doc_description, file_type
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 36


SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'drop table my_documents') > 0
  4  /

no rows selected

SQL>

Also, apparently the inclusion of "(" and "," and such are tricky when creatimg a query.

Note : The "CONTAINS ... >0" clause is to check the SCORE returned by the filter in the CONTAINS section and to see that it is greater than zero.

Here I have created a CTXSYS.CONTEXT index and it works well for the PDF and DOCX files.  This type of Index is *not* automatically updated when you load a new document.  To update the Index you must call the "CTX_DDL.SYNC_INDEX" procedure.

Using the same method as in my previous blog post, I load a new PPT file (callled "Flashback.ppt").

C:\load_docs_to_oracle>sqlldr mydocdb/mydocdb@pdb1 control=my_documents.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 14 17:04:55 2021
Version 19.12.0.0.0

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

Path used:      Conventional
Commit point reached - logical record count 1

Table MY_DOCUMENTS:
  1 Row successfully loaded.

Check the log file:
  my_documents.log
for more information about the load.

C:\load_docs_to_oracle>

And then sync the index and query again

SQL> select doc_number, doc_title, file_type
  2  from my_documents
  3  order by doc_number
  4  /

DOC_NUMBER DOC_TITLE                                  FILE_TYPE
---------- ------------------------------------------ ----------------
         1 Oracle 12c Availability                    PDF
         2 Oracle 12c Security                        PDF
         3 Monitoring and Diagnostics without OEM     PDF
         4 Setting up Data Guard in 11gR2             DOCX
         5 Flashback Database and DG DR Testing       DOCX
         6 Create Docs Table                          SQL
         7 Flashback                                  PPT

7 rows selected.

SQL> exec CTX_DDL.SYNC_INDEX('my_documents_ctx_idx','4M');

PL/SQL procedure successfully completed.

SQL>
SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'Thomas Kyte') > 0
  4  /

DOC_TITLE                                  DOC_DESCRIPTION                            FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Flashback                                  Tom Kyte's Presentation on Flashback       PPT

SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'Rowids therefore will change') > 0
  4  /

DOC_TITLE                                  DOC_DESCRIPTION                            FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Flashback                                  Tom Kyte's Presentation on Flashback       PPT

SQL>

The call to CTX_DDL.SYNC_INDEX contains a memory parameter '4M' specifying the memory to be used for synchronization.

Besides the CTXSYS.CONTEXT IndexType, Oracle also allows a "CTXSYS.CTXCAT" IndexType which can be used to index small documents or text fragements.  The CTXSYS.CTXCAT Index is updated synchronously when a new document is loaded into the table (i.e. a call to CTX_DDL.SYNC_INDEX is not required).

Typically, with a document database with moderate to large documents, you would use CTXSYS.CONTEXT IndexType and configure a scheduler job to call CTX_DDL.SYNC_INDEX periodically (e.g. every 30minutes or every 4hours) to update the Index for new documents that are loaded infrequently and do not need to be searched immediately.

The IndexType automically excludes certain popular words called "STOPWORD"s that may be in "STOPLIST"s.  These are very commonly used words that do not need to be indexed (e.g. words like "the" or "this" or "that".  Such STOPLISTs can be customised as well.

So, your implementation of the indexes is customisable.

See the Oracle Text Application Developers Guide and  Oracle Text Reference for more examples and a detailed study.

Loading Documents into Oracle

A short demonstration of loading documents into an Oracle Database.

SQL> @create_docs_table
SQL> spool create_docs_table
SQL>
SQL> drop table my_documents purge;

Table dropped.

SQL> create table my_documents
  2  (doc_number number generated always as identity,
  3  doc_title varchar2(100),
  4  doc_description varchar2(256),
  5  os_file_name varchar2(256),
  6  file_content blob,
  7  file_type varchar2(16))
  8  lob(file_content) store as securefile (compress high keep_duplicates)
  9  /

Table created.

SQL>
SQL> spool off
SQL>

I have created table with a SecureFile LOB to hold the documents.

Next, I use sqlloader to load the documents, based on a "list file".

C:\load_docs_to_oracle>type List_of_Files_to_Upload.csv
Oracle 12c Availability,High Availability Options in Oracle 12c,12c_Availability.pdf,PDF
Oracle 12c Security,Security Mechanisms in Oracle 12c,12c_Security.pdf,PDF
Monitoring and Diagnostics without OEM,Presentation at Oracle SOS Conference,Monitoring and Diagnostics without OEM.pdf,PDF
Setting up Data Guard in 11gR2,My document on DG setup in 11gR2 ,Setting up DataGuard in 11gR2.docx,DOCX
Flashback Database and DG DR Testing,How to use Flasback Database in DataGuard for DR Testing,Using FLASHBACK DATABASE for Destructive DR Testing.docx,DOCX
Create Docs Table,Source Code for Script to create DOCS Table,create_docs_table.sql,SQL

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>type my_documents.ctl
load data
        infile 'List_of_Files_to_Upload.csv'
        into table my_documents
        fields terminated by ','
(
doc_title ,
doc_description ,
os_file_name,
file_content lobfile(os_file_name) terminated by EOF,
file_type
)

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>sqlldr mydocdb/mydocdb@pdb1 control=my_documents.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 7 17:01:33 2021
Version 19.3.0.0.0

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

Path used:      Conventional
Commit point reached - logical record count 6

Table MY_DOCUMENTS:
  6 Rows successfully loaded.

Check the log file:
  my_documents.log
for more information about the load.

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>type my_documents.log

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 7 17:01:33 2021
Version 19.3.0.0.0

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

Control File:   my_documents.ctl
Data File:      List_of_Files_to_Upload.csv
  Bad File:     List_of_Files_to_Upload.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     250 rows, maximum of 1048576 bytes
Continuation:    none specified
Path used:      Conventional

Table MY_DOCUMENTS, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DOC_TITLE                           FIRST     *   ,       CHARACTER
DOC_DESCRIPTION                      NEXT     *   ,       CHARACTER
OS_FILE_NAME                         NEXT     *   ,       CHARACTER
FILE_CONTENT                      DERIVED     *  EOF      CHARACTER
    Dynamic LOBFILE.  Filename in field OS_FILE_NAME
FILE_TYPE                            NEXT     *   ,       CHARACTER


Table MY_DOCUMENTS:
  6 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 258000 bytes(250 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             6
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Sun Nov 07 17:01:33 2021
Run ended on Sun Nov 07 17:01:35 2021

Elapsed time was:     00:00:01.17
CPU time was:         00:00:00.06

C:\load_docs_to_oracle>

Note : In "List_of_Files_to_Upload.csv" the incoming file names are *not* enclosed in quotes and there is no space-character before the filename.

Once you have loaded the documents, SQLDeveloper gives you the option of either opening the file (e.g. PDF files opened by Acrobat Reader) or downloading the file that you query.  (In my installation, DOCX and SQL files have to be downloaded for viewing as the Exernal Editor is not configured for these file-extensions -- the DOCX is identified as a "jar" file).

[click on the images below to view them better]