IMPDP for one single table in multitenant database in a different custom schema

Few days back, we got a request to import one table to our PDB. The main point to note is import will run through sys user and it has to be imported under a custom schema. The source was 19c and target DB version was 12c. The dump file along with log files were shared with us. Now, the environment where we need to do the import consists of the DB running on VM DB system on OCI. It consists of one root and one pluggable container. 

                Image source:https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89236

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 15 11:36:36 2022

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 and Real Application Testing options

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 RPPDB                       READ WRITE NO

SQL>

The table to be imported already exist in our system and it was owned by an another schema FUSION. We created a par file, the contents of which are given below

cat impdp.par

tables=FUSION."table name"

logfile=impdp_tables_expdp.log

parallel=4

encryption_password=****

dumpfile=tables-expdp-%U.dmp

directory=IMPORT

job_name=job_NDE

table_exists_action=truncate

Now using the par file, run the IMPDP command

impdp \"sys/*****@RPPDB as sysdba\" parfile=impdp.par

The post was an small example on how we can do a import of one single table in a multitenant Database. Hope you find it useful. Till then happy learning.

 

FAILED: enableSSH adop ebs 12.2

We recently upgraded our EBS 12.1.3 to EBS 12.2.10 and multi node deployment was done. One was secondary internal nodes and 2 were external DMZ nodes. SSH was manually enabled using native authentication by using the command ssh-keygen. And then configurations were done by copying the primary application tier backups.  For details on how to setup the SSH, you can follow the EBS 12.2 documentation https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531065.htm#adsshsetup

Now, in order to sync the patch file system and run file system for all the nodes, we ran the command adop phase=fs_clone in the primary node. Ideally it should have done the ssh to all the nodes and perform the sync. But in our case, inspite of having the manual SSH setup done earlier, adop was not working. It gave an error during validation phase:-SEVERE: com.jcraft.jsch.JSchException: Algorithm negotiation fail.

  at com.jcraft.jsch.Session.receive_kexinit(Session.java:510)

Now in EBS 12.2, either you can setup manual SSH or use the perl script.

 perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl enablessh -contextfile=<Context_file> -hosts=<node1>,<node2>.

We cannot club both of them together. If manual setup has been done, then no need to run the perl script. For all the other application nodes, the bash profile was pointing to EBS 12.1.3 environment and thus adop SSH validation was failing. We removed that part first from the bash profile and repointed it to use EBS 12.2. Adop still failed. Then we remember, someone changed the application OS password after the SSH keys generation. The EBS 12.2 maintenance guide suggests that if the OS password is changed, we need to regenerate the keys again. Then copy the .pub to the authorized_keys file in all the other nodes. Upon doing the changes again, adop phase=fs_clone validation worked fine and rest patching cycle went through.

Through this post, i wanted to highlight my experience. Hope it helps someone.

References:-https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531065.htm#adsshsetup