FORWARD_LISTENER demo steps

forward_listener_demo.txt

# Prepare source database

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv -s

/orasoft/scripts/fix-listener.sh
lsnrctl start
/orasoft/scripts/renamedb.sh cdb1 oldcdb1

export ORACLE_SID=oldcdb1
export ORAENV_ASK=NO
. oraenv -s

sqlplus / as sysdba <<EOF
create pluggable database oldpdb1  admin user pdbadmin identified by Welcome_1;
alter pluggable database oldpdb1 open;
alter pluggable database oldpdb1 save state;
create pluggable database staypdb1 admin user pdbadmin identified by Welcome_1;
alter pluggable database staypdb1 open;
alter pluggable database staypdb1 save state;
alter pluggable database pdb1 close;
drop pluggable database pdb1 including datafiles;
shutdown immediate;
startup;
exit
EOF


# Prepare new database

export ORACLE_SID=cdb1
export ORAENV_ASK=NO
. oraenv -s

/orasoft/scripts/fix-listener.sh
lsnrctl start
/orasoft/scripts/renamedb.sh cdb1 newcdb1

export ORACLE_SID=newcdb1
export ORAENV_ASK=NO
. oraenv -s

sqlplus / as sysdba <<EOF
create pluggable database newpdb1  admin user pdbadmin identified by Welcome_1;
alter pluggable database newpdb1 open;
alter pluggable database newpdb1 save state;
alter pluggable database pdb1 close;
drop pluggable database pdb1 including datafiles;
shutdown immediate;
startup;
exit
EOF

# on the new pdb create a service and start it.

alter session set container=newpdb1;
exec dbms_service.create_service('OLDPDB1','OLDPDB1')
exec dbms_service.start_service('OLDPDB1')

# create a database trigger to enable the service at startup.

CREATE OR REPLACE TRIGGER startup_services
AFTER STARTUP ON DATABASE
BEGIN
  dbms_service.start_service('OLDPDB1');
END;
/

shutdown immediate;
startup

# check lsnrctl status to see the service is registered.

#
#
# on the old database setup the listener parameters in the PDB that is moving to the new host.
# ******* WARNING ********* ******* WARNING ********* ******* WARNING *********
# ORDER IS IMPORTANT - set the LOCAL_LISTENER last, else you will get errors.
# *****************************************************************************
#

alter session set container=oldpdb1;

# set the remote_listener='TNS address of local listener of the database'
# get it by the following command on the OLD host
lsnrctl status | grep DESC| grep HOST | head -1 | awk '{ print $NF }'

alter system set remote_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vm100)(PORT=1521)))' scope=both sid='*';

set the forward_listener='TNS address of local listener of the NEW database'
# get it by the following command on the NEW host
lsnrctl status | grep DESC| grep HOST | head -1 | awk '{ print $NF }'

alter system set forward_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vm200)(PORT=1521)))' scope=both sid='*';

# set the local_listener to oracle-none-

alter system set local_listener='oracle-none-' scope=both sid='*';

# on the old database add tns entry for local oldpdb1 and staypdb1

# tnsping oldpdb1
oracle@vm100 admin]$ tnsping oldpdb1

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 24-FEB-2022 13:39:55

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vm100)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oldpdb1)))
OK (0 msec)

# test sqlplus connection and use show pdbs and show con_name

[oracle@vm100 admin]$ sqlplus system/Welcome_1@oldpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 24 13:40:13 2022
Version 19.14.0.0.0

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

Last Successful login time: Sun Feb 06 2022 01:14:33 -05:00

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

SQL> show parameter db_name

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_name 			     string	 newcdb1
SQL> show con_name

CON_NAME
------------------------------
NEWPDB1
SQL> 
SQL> 

# check the listener log on new host

[oracle@vm200 ~]$ cd /u01/app/oracle/diag/tnslsnr/vm200/listener/trace/
[oracle@vm200 trace]$ tail listener.log 
2022-02-24T13:35:39.060216-05:00
24-FEB-2022 13:35:39 * service_update * newcdb1 * 0
WARNING: Subscription for node down event still pending
24-FEB-2022 13:35:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=vm200)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=318767104)) * status * 0
2022-02-24T13:40:13.522467-05:00
24-FEB-2022 13:40:13 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=oldpdb1)(CID=(PROGRAM=sqlplus@vm100)(HOST=vm100)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.1.0.133)(PORT=53170)) * establish * oldpdb1 * 0
2022-02-24T13:40:16.257800-05:00
24-FEB-2022 13:40:16 * service_update * newcdb1 * 0
2022-02-24T13:40:34.287070-05:00
24-FEB-2022 13:40:34 * service_update * newcdb1 * 0
[oracle@vm200 trace]$ 


### PDB Clone

create user c##vrk identified by Welcome_1 container=all;
grant create session, sysoper, create pluggable database to c##vrk container=all;

create PUBLIC database link oldcdb1
connect to c##vrk  identified by Welcome_1
using '(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = &vmname)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = oldcdb1)))';

select * from tab@oldcdb1;

lsnrctl services

CREATE PLUGGABLE DATABASE staypdb1 from staypdb1@oldcdb1 RELOCATE AVAILABILITY MAX;

show pdbs

alter pluggable database staypdb1 open;