Wednesday, June 8, 2016
Saturday, June 4, 2016
Oracle Active Data Guard and Oracle GoldenGate
Oracle Active Data Guard and Oracle GoldenGate http://www.oracle.com/technetwork/database/features/availability/dataguardgoldengate-096557.html
Uni-Directional Replication b/n Oracle 11g and Oracle 12c Using Oracle GoldenGate
Source Database:
Operating System: Oracle Enterprise Linux 5.8 (x86-64)
Oracle Database: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Oracle GoldenGate: Oracle GoldenGate for Oracle - 11.2.1.0.0
Target Database:
Operating System: Oracle Enterprise Linux 5.8 (x86-64)
Oracle Database: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
Oracle GoldenGate: Oracle GoldenGate for Oracle - 12.1.2.0.0
Note-1: No need to set the parameter "enable_goldengate_replication"
Note-2: If target database version in Oracle 12c (12.1.0.2.0) then we have to set TRUE for the following parameter
otherwise Replicat Process (rorcl) will not start and you can find the error in ggserr.log
SQL> alter system set enable_goldengate_replication=TRUE scope=both;
System altered.
SQL> sho parameter enable_
NAME TYPE VALUE
------------------------------------------ ----------- -----------
enable_goldengate_replication boolean TRUE
Thursday, June 2, 2016
Creating Pluggable Database (PDB) and import data to PDB
Creating PDB and Import Data
[oracle@localhost ~]$ ps -ef | grep pmon
oracle 1756 1 0 23:16 ? 00:00:00 ora_pmon_cdb1
oracle 2538 1 0 23:19 ? 00:00:00 ora_pmon_cdb2
oracle 2970 2945 0 23:37 pts/1 00:00:00 grep pmon
[oracle@localhost ~]$ cd /u01/app/oracle/oradata/cdb2
[oracle@localhost cdb2]$ ls -lrth
[oracle@localhost cdb2]$ mkdir pdb3
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [oracle] ? cdb2
The Oracle base has been set to /u01/app/oracle
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect sys/oracle@192.168.56.101:1521/cdb2 as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE pdb3
ADMIN USER pdb3_admin
IDENTIFIED BY pdb3 ROLES=(CONNECT)
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb2/pdbseed', '/u01/app/oracle/oradata/cdb2/pdb3');
Pluggable database created.
(OR) - > If it is OMF format check the below script
SQL> connect sys/oracle@192.168.56.101:1521/cdb3 as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE pdb3
ADMIN USER pdb3_admin
IDENTIFIED BY pdb3 ROLES=(CONNECT)
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB3/datafile', '/u01/app/oracle/oradata/CDB3/pdb3/datafile/');
2 3 4 CREATE PLUGGABLE DATABASE pdb3
*
ERROR at line 1:
ORA-01276: Cannot add file
/u01/app/oracle/oradata/CDB3/pdb3/datafile//o1_mf_system_b7chl7yq_.dbf. File
has an Oracle Managed Files file name.
SQL> CREATE PLUGGABLE DATABASE pdb3
2 ADMIN USER pdb3_admin
3 IDENTIFIED BY pdb3 ROLES=(CONNECT)
4 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB3/datafile/pdbseed_temp01.dbf',
'/u01/app/oracle/oradata/CDB3/pdb3/datafile/temp01.dbf',
5 '/u01/app/oracle/oradata/CDB3/datafile/o1_mf_system_b7chl7yq_.dbf',
'/u01/app/oracle/oradata/CDB3/pdb3/datafile/system01.dbf',
6 '/u01/app/oracle/oradata/CDB3/datafile/o1_mf_sysaux_b7chl7xy_.dbf',
'/u01/app/oracle/oradata/CDB3/pdb3/datafile/sysaux01.dbf');
Pluggable database created.
SQL> !
[oracle@localhost datafile]$ cd /u01/app/oracle/oradata/CDB3/pdb3/datafile/
[oracle@localhost datafile]$ ls -lrth
total 841M
-rw-r-----. 1 oracle oinstall 21M Nov 26 14:12 temp01.dbf
-rw-r-----. 1 oracle oinstall 251M Nov 26 14:12 system01.dbf
-rw-r-----. 1 oracle oinstall 591M Nov 26 14:12 sysaux01.dbf
[oracle@localhost datafile]$
Check the open mode of PDB3 in CDB2:
====================================
SQL> select CON_ID, NAME, OPEN_MODE from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 PDB3 MOUNTED
SQL> alter pluggable database pdb3 open;
Pluggable database altered.
SQL> exit
Note: Add the entries in tnsnames.ora for the database ‘PDB3’
[oracle@localhost cdb2]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin
[oracle@localhost admin]$ ls -lrth
[oracle@localhost admin]$ vi tnsnames.ora
[oracle@localhost admin]$ exit
SQL> !lsnrctl services
Connect to PDB3 under the pdb3_admin user
=========================================
SQL> connect pdb3_admin/pdb3@pdb3
Connected.
List the datafiles created
===========================
SQL> !ls /u01/app/oracle/oradata/cdb2/pdb3
pdbseed_temp01.dbf sysaux01.dbf system01.dbf
Before importing data into pdb3 & Create the directory on the filesystem.
=========================================================================
SQL> !mkdir /u01/app/oracle/oradata/cdb2/pdb3/dump
SQL> !ls /u01/app/oracle/oradata/cdb2/pdb3/dump
SQL> exit
Create a directory in noncdb
=============================
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [cdb2] ? noncdb
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect sys/oracle as sysdba
SQL> startup;
SQL> exit
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect system/oracle@192.168.56.101:1521/noncdb
Connected.
SQL> create directory dpdump_pdb1 as '/u01/app/oracle/oradata/cdb2/pdb3/dump';
Directory created.
SQL> grant read, write on directory dpdump_pdb1 to PUBLIC;
Grant succeeded.
SQL> connect sys/oracle@192.168.56.101:1521/pdb3 as sysdba
Connected.
SQL> create directory dpdump_pdb1 as '/u01/app/oracle/oradata/cdb2/pdb3/dump';
Directory created.
SQL> grant read, write on directory dpdump_pdb1 to PUBLIC;
Grant succeeded.
SQL> create user SOE identified by soe;
User created.
SQL> grant dba to SOE;
Grant succeeded.
SQL> GRANT EXECUTE on DBMS_LOCK to PUBLIC;
Grant succeeded.
SQL> connect system/oracle@192.168.56.101:1521/noncdb
Connected.
SQL> create user SOE identified by soe;
User created.
SQL> grant dba to SOE;
Grant succeeded.
SQL> connect sys/oracle@192.168.56.101:1521/noncdb as sysdba
Connected.
SQL> GRANT EXECUTE on DBMS_LOCK to PUBLIC;
Grant succeeded.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/noncdb/system01.dbf
/u01/app/oracle/oradata/noncdb/sysaux01.dbf
/u01/app/oracle/oradata/noncdb/undotbs01.dbf
/u01/app/oracle/oradata/noncdb/users01.dbf
/u01/app/oracle/oradata/noncdb/example01.dbf
/u01/app/oracle/oradata/noncdb/sh_001.dbf
/u01/app/oracle/oradata/noncdb/sh_index_001.dbf
/u01/app/oracle/oradata/noncdb/ilmtbs1.dbf
/u01/app/oracle/oradata/noncdb/lowcoststore1.dbf
9 rows selected.
SQL> alter tablespace example read only;
Tablespace altered.
SQL> exit
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [noncdb] ? noncdb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost cdb2]$ expdp system/oracle DIRECTORY= dpdump_pdb1 DUMPFILE=tts_mydump TRANSPORT_TABLESPACES=EXAMPLE TRANSPORT_FULL_CHECK=YES
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory= DUMPFILE=tts_mydump TRANSPORT_TABLESPACES=EXAMPLE TRANSPORT_FULL_CHECK=YES
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/oradata/cdb2/pdb3/dump/tts_mydump.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
/u01/app/oracle/oradata/noncdb/example01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Nov 12 04:21:44 2014 elapsed 0 00:01:37
Note: Before importing the tablespace, copy the datafile from noncbd to pdb3 directory
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [noncdb] ? cdb2
[oracle@localhost cdb2]$ cp /u01/app/oracle/oradata/noncdb/example01.dbf /u01/app/oracle/oradata/cdb2/pdb3/example01.dbf
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect sys/oracle@192.168.56.101:1521/pdb3 as sysdba
Connected.
SQL> create user hr identified by oracle;
User created.
SQL> grant dba to hr;
Grant succeeded.
SQL> GRANT EXECUTE on DBMS_LOCK to PUBLIC;
Grant succeeded.
SQL> grant connect,resource to hr;
Grant succeeded.
SQL> create user pm identified by oracle;
SQL> create user oe identified by oracle;
SQL> create user ix identified by oracle;
SQL> exit
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [cdb2] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost cdb2]$ impdp system/oracle@PDB3 DIRECTORY=dpdump_pdb1 DUMPFILE=tts_mydump TRANSPORT_DATAFILES='/u01/app/oracle/oradata/cdb2/pdb3/example01.dbf'
Import: Release 12.1.0.1.0 - Production on Wed Nov 12 04:31:49 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source timezone version is +00:00 and target timezone version is -07:00.
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@PDB3 DIRECTORY=dpdump_pdb1 DUMPFILE=tts_mydump TRANSPORT_DATAFILES=/u01/app/oracle/oradata/cdb2/pdb3/example01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 33 error(s) at Wed Nov 12 04:32:55 2014 elapsed 0 00:01:04
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect sys/oracle@192.168.56.101:1521/pdb3 as sysdba
Connected.
SQL> connect hr/oracle@pdb3
Connected.
SQL> select table_name from user_tables ;
TABLE_NAME
--------------------------------------------------------------------------------
JOB_HISTORY
EMPLOYEES
DEPARTMENTS
LOCATIONS
REGIONS
JOBS
COUNTRIES
7 rows selected.
SQL> connect sys/oracle@192.168.56.101:1521/noncdb as sysdba
Connected.
SQL> connect hr/hr@noncdb
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
COUNTRIES
JOB_HISTORY
EMPLOYEES
JOBS
DEPARTMENTS
LOCATIONS
REGIONS
EMP_TEMP
8 rows selected.
SQL> connect sys/oracle@192.168.56.101:1521/noncdb as sysdba
Connected.
SQL> alter tablespace example read write;
Tablespace altered.
Cheers !!!!!!
Subscribe to:
Posts (Atom)