Shema
Refresh (ashwani)
1.
Check
Listener
[oracle@0PROD ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on
24-MAY-2017 17:31:34
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0PROD)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias
LISTENER
Version
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date
20-APR-2017 16:39:37
Uptime
34 days 0 hr. 51 min. 57 sec
Trace Level
off
Security
ON: Local OS Authentication
SNMP
OFF
Listener Parameter File
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File
/u01/app/oracle/diag/tnslsnr/0PROD/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0PROD)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "501c3dcd870d7f1ae0534c3a1dac7310" has 1
instance(s).
Instance "smrp", status READY,
has 1 handler(s) for this service...
Service "db12c" has 2 instance(s).
Instance
"db12c", status UNKNOWN, has 1 handler(s) for this service...
Instance
"db12c", status READY, has 1 handler(s) for this service...
Service "db12cXDB" has 1 instance(s).
Instance
"db12c", status READY, has 1 handler(s) for this service...
Service "db12cdr" has 1 instance(s).
Instance
"db12cdr", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
Instance
"orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance
"orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance
"orcl", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance
"smrp", status READY, has 1 handler(s) for this service...
Service "smrp" has 1 instance(s).
Instance "smrp", status READY,
has 1 handler(s) for this service...
Service "smrpXDB" has 1 instance(s).
Instance "smrp", status READY,
has 1 handler(s) for this service...
The command completed successfully
2.
export ORACLE_SID=CDB_name
[oracle@0PROD ~]$ export ORACLE_SID=smrp
[oracle@0PROD ~]$ echo $ORACLE_SID
Smrp
[oracle@0PROD ~]$ sqlplus / as
sysdba
SQL*Plus: Release 12.2.0.1.0
Production on Wed May 24 17:42:47 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 pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
3.
Adding tnsname.ora pdb1 (as highlighted) connection
[oracle@0PROD admin]$ cd $ORACLE_HOME/network/admin/
[oracle@0PROD admin]$ vi
tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_DB12C =
(ADDRESS = (PROTOCOL = TCP)(HOST
= PROD)(PORT = 1521))
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST
= PROD)(PORT = 1521))
LISTENER =
(ADDRESS = (PROTOCOL = TCP)(HOST
= PROD)(PORT = 1521))
DB12CDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = TEST)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db12cdr)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = PROD)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = TEST)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldr)
)
)
DB12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = PROD)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db12c)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PROD)(PORT
= 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)
4.
Create directory for PDB
[oracle@0PROD
~]$ mkdir p6_dir
[oracle@0PROD ~]$ cd p6_dir/
[oracle@0PROD p6_dir]$ pwd
/home/oracle/p6_dir
[oracle@0PROD p6_dir]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed May 24 17:55:19 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 pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> alter session set container=pdb1;
Session altered.
SQL> create or replace
directory p6_dir as '/home/oracle/p6_dir';
Directory created.
SQL>exit
5. SCP the dumpfile to test database
server
[oracle@0PROD p6_dir]$ pwd
/home/oracle/p6_dir
[oracle@0PROD p6_dir]$ ls –lrth
-rw-r-----. 1 oracle oinstall
340K May 24 17:26 ashwani.dmp
-rw-r--r--. 1 oracle oinstall
1.4K May 24 17:26 ashwani.log
[oracle@0PROD p6_dir]$ scp ashwani.dmp
TEST:/home/oracle/p6_dir
ashwani.dmp 100% 340KB 340.0KB/s 00:00
[oracle@TEST
p6_dir]$ impdp system/oracle_4U@pdb1 directory=pdb1_dir dumpfile=ashwani.dmp
logfile=ashwani_imp1.log
Import: Release 12.2.0.1.0 - Production on Wed May 24 18:18:45 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01"
successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@pdb1 directory=pdb1_dir
dumpfile=ashwani.dmp logfile=ashwani_imp1.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ASHWANI"."TEST" 5.531 KB 4 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type
SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully
completed at Wed May 24 18:19:04 2017 elapsed 0 00:00:19
No comments:
Post a Comment