Replication methods in oracle and MSSQL server database

In oracle/MSSQL database we can have  multiple replication /standby database available for DR purpose.

oracle to oracle replication methods


1.RAC --Real application cluster for High availability
2.oracle Data guard for standby DR
3.oracle golden gate replication methods
4.we can achieve oracle to oracle replication with streams

oracle to other database replication methods

1.we can do oracle to MSSQL server & IBM DB2  replication using golden gate.
2..we can do oracle to MSSQL server & IBM DB2  replication using gateway server
3.we can do oracle to MSSQL server & IBM DB2  replication using  drivers to support
4.we can do oracle to MSSQL server & IBM DB2  replication using  third party tools like DBmoto

MSQL server to oracle replication methods
1.we can do MSSQL server to oracle DB replication using golden gate
2.we can do MSSQL server to oracle DB replication using native MSSQl server replication method with publisher/ditribuitor/subscriber
3.we can do MSSQL server to oracle DB replication using third party tools like DBmoto
4.we can do MSSQL server to oracle DB replication using gateway server

steps to clone PDB manually in oracle 12c

Clone a Pluggable Database (PDB) Manually

Cloning an existing local PDB is similar to creating a new PDB from the seed PDB, except now we are using non-seed PDB as the source, which we have to identify using the FROM clause. Make sure the source PDB is open in READ ONLY mode.

Here pdb1 is SOURCE pdb4 as target(new DB )


ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;--source DB needs to be read only mode

CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/');

ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;

-- Switch the source PDB back to read/write
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ WRITE;

SQL>  select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/users01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/system01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/pdb2_users01.dbf

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf

14 rows selected.

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY
*
ERROR at line 1:
ORA-65019: pluggable database PDB1 already open


SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;

Pluggable database altered.

SQL> CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/');
  2  CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode


SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;

Pluggable database altered.

SQL> CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/');
  2


Pluggable database created.

SQL> SQL> SQL>
SQL> ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ WRITE;
Pluggable database altered.

SQL>

Pluggable database altered.


SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE
PDB4                           READ WRITE

 Delete a Pluggable Database (PDB) Manually

When dropping a pluggable database, you must decide whether to keep or drop the associated datafiles. The PDBs must be closed before being dropped.

ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

ALTER PLUGGABLE DATABASE pdb4 CLOSE;
DROP PLUGGABLE DATABASE pdb4 INCLUDING DATAFILES;  2  SQL> SP2-0734: unknown command beginning "When dropp..." - rest of line ignored.
SQL> SQL>
Pluggable database altered.

SQL>
Pluggable database dropped.

SQL> SQL>
Pluggable database altered.

SQL>
Pluggable database dropped.

SQL> SQL>
Pluggable database altered.


SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB4                           MOUNTED

SQL> alter PLUGGABLE DATABASE  pdb4  open read write;

Pluggable database altered.

SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB4                           READ WRITE

table space administration on PDB in oracle 12c

adding  tablespace to PDB database
connect to the PDB

SQL> ALTER SESSION SET CONTAINER = pdb4;

Session altered.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB4
SQL> create tablespace test datafile '/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/test.dbf' size 200m;

Tablespace created.

SQL> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/system01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/sysaux01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/pdb1_users01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/test.dbf

SQL> DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/system01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/sysaux01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/pdb1_users01.dbf

SQL> SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS

scrip to check the query execution time in oracle database

You can use below query to find out the execution time of the query. Just replace the SQL_ID with your SQL_ID.

SELECT
SQL_ID,
EXECUTIONS,
ELAPSED_TIME/1000000 TOTAL_ELAPSED_TIME_SEC,
ELAPSED_TIME/1000000/EXECUTIONS ELAPSED_TIME_SEC_PER_EXEC,
CPU_TIME/1000000 TOTAL_CPU_TIME_SEC,
CPU_TIME/1000000/EXECUTIONS CPU_TIME_SEC
FROM
V$SQL WHERE SQL_ID='dx647nxstqu260';

To calculate the execution time from the AWR tables, you can use below query.

SELECT
SNAP_ID,
SQL_ID,
EXECUTIONS_DELTA EXECUTIONS,
ELAPSED_TIME_DELTA/1000000 TOTAL_ELAPSED_TIME_SEC,
ELAPSED_TIME_DELTA/1000000/EXECUTIONS_DELTA ELAPSED_TIME_SEC_PER_EXEC,
CPU_TIME_DELTA/1000000 TOTAL_CPU_TIME_SEC,
CPU_TIME_DELTA/1000000/EXECUTIONS_DELTA CPU_TIME_SEC
FROM
DBA_HIST_SQLSTAT WHERE SQL_ID='dx647nxstqu260';

steps to Plugin a Pluggable Database (PDB) Manually in oracle 12c

Plugin a Pluggable Database (PDB) Manually

Plugging in a PDB into the CDB is similar to creating a new PDB. First check the PBD is compatible with the CDB by calling the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function, passing in the XML metadata file and the name of the PDB you want to create using it.


SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/media/sf_12cR1/12cDB/cdb1/pdb2/pdb2.xml',
                pdb_name       => 'pdb2');

  IF l_result THEN
    DBMS_OUTPUT.PUT_LINE('compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('incompatible');
  END IF;
END;
/

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14  compatible -->it supposed to return compatible to plug in the database again to the container.

PL/SQL procedure successfully completed.


we want to plug the database back into the same container, so we don't need to copy the files or recreate the temp file, so we can do the following.

CREATE PLUGGABLE DATABASE pdb2 USING '/media/sf_12cR1/12cDB/cdb1/pdb2/pdb2.xml'
  NOCOPY
  TEMPFILE REUSE;

ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;

Pluggable database created.

SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           MOUNTED
PDB3                           READ WRITE

steps to unpug the PDB database from container in oracle 12c

Unplug a Pluggable Database (PDB) Manually

Before attempting to unplug a PDB, you must make sure it is closed. To unplug the database use the ALTER PLUGGABLE DATABASE command with the UNPLUG INTO clause to specify the location of the XML metadata file.


ALTER PLUGGABLE DATABASE pdb2 CLOSE;
SQL> ALTER PLUGGABLE DATABASE pdb2 CLOSE;

Pluggable database altered.

ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/media/sf_12cR1/12cDB/cdb1/pdb2/pdb2.xml';

SQL> ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/media/sf_12cR1/12cDB/cdb1/pdb2/pdb2.xml';

Pluggable database altered.

SQL> SELECT name, open_mode

FROM   v$pdbs

ORDER BY name;  2    3

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           MOUNTED

PDB3                           READ WRITE

SQL> DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

Pluggable database dropped.

SQL> SELECT name, open_mode

FROM   v$pdbs

ORDER BY name;  2    3

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB3                           READ WRITE

steps to create PDB manually in 12c

Create a Pluggable Database (PDB) Manually

To create a new pluggable database from the seed database, all we have to do is tell Oracle where the file should be placed. We can do this using one of two methods. The first method uses the FILE_NAME_CONVERT clause in the CREATE PLUGGABLE DATABASE statement.

CONN / AS SYSDBA

SQL>CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1

  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/media/sf_12cR1/12cDB/cdb1/pdb2/');

database created.

SQL> ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;

SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;
  2    3
NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE

How to create manual profile in oracle 10g database?

issue:
oracle 10g database sql execution plan got changed and sql performing badly.
root cause:
oracle picked up the new plan which is a bad plan and same time we have good plan in AWR.
we have to force oracle to pick the good plan. but in 10g we dont have baseline features,so we cant forcefully oracle to pick the new plan.
solution:
In oracle 10g database version we don't have SPM and we have some  other  method to pick  the old plan.

we have SQLT feature is there and using it we can create manual profile by passing the SQL_ID and plan_hash_value.

coe_xfr_sql_profile.sql

SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
it will create new SQL file to create a profile on the database and we can create the profile on the database.
check the profile status using dba_sql_profiles