https://www.tecmint.com/linux-network-bandwidth-monitoring-tools/
Wednesday, 27 December 2023
Tuesday, 14 March 2023
19c Refreshable Clone - Alternate to Data Guard and High Availability Solution for Oracle Standard Edition
19c Refreshable Clone - Alternate to Data Guard and High Availability Solution for Oracle Standard Edition
Use Cases
1) Only HA solution for Standard Edition as Data Guard is not supported in Standard Edition.
2) Same process can be used in Enterprise Edition also.
3) Only supported in CDB - 19c architecture
4) Fast cloning process without using RMAN backup / restore process.
5) Easy to implement , even by junior DBA's
----------------------------Steps------------------
|
Source CDB (hostname) |
Target CDB (hostname) |
Source PDB |
Target PDB |
|
SRCCDB |
TRGCDB |
PROD |
PRODREF |
|
sourceserver |
targetserver |
|
|
Oracle Database 19c Standard Edition 2 Release
19.0.0.0.0 - Production
Version 19.10.0.0.0
Steps to create Refreshable PDB
1)
Create new CDB (Same OS, Same Patch
set level) on target host where you wanted to create refreshable PDB or you can
use any existing CDB database on any other server.
2)
Add tns entry in each other’s
tnsnames.ora
3) Creation of a refreshable clone PDB requires a database link in the source and target CDB root container, create db link in both sides.
4) Create a user in the source CDB SRCCDB (host-sourceserver) for database link purpose
sqlplus as sysdba
SQL> create user c##clone_user identified by <pass> temporary tablespace temp container=ALL;
User created.
SQL> grant create session, create pluggable database,
sysoper to c##clone_user container=ALL;
Grant succeeded.
5)
In the target server cdb – TRGCDB
(Host- targetserver) create database link pointing to source CDB using
c##clone_user created above in the source CDB as above
First add below entry in tnsnames.ora in target server
-->Refreshable PDB - CDB tns entry in target node
SRCCDB_ sourceserver =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = sourceserver.vij.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME = SRCCDB.vij.com)
)
)
àcreate database link , connect as sysdba to cdb
SQL> create database link clonesource connect to c##clone_user identified by <pass> using 'SRCCDB_sourceserver';
Database link
created.
SQL> select * from dual@clonesource;
D
-
X
6)
Create a refreshable clone on
target , connect to CDB as sysdba
--target - create below directory on target
/u01/oradata/PRODREF/datafiles
--Create Refreshable DB took 5 Min - DB size 8 GB
-bash-4.2$
sqlplus "/as sysdba"
SQL*Plus:
Release 19.0.0.0.0 - Production on Tue Sep 23 11:04:05 2022
Version
19.10.0.0.0
Copyright (c)
1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database
19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version
19.10.0.0.0
SQL> create pluggable database PRODREF from PROD@clonesource
refresh mode manual FILE_NAME_CONVERT=('/u01/oradata/PROD/datafiles/','/u01/oradata/PRODREF/datafiles/');
Pluggable
database created.
SQL> show
pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3
PRODREF MOUNTED
4 PRODSTG READ WRITE NO
SQL> select
PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs;
PDB_NAME
--------------------------------------------------------------------------------
REFRES
REFRESH_INTERVAL LAST_REFRESH_SCN
------
---------------- ----------------
PRODREF
MANUAL 142927690
PDB$SEED
NONE
PRODSTG
NONE
7)
We can open refreshable clone READ
ONLY to query from the tables to verify below as an example
--------on source
-bash-4.2$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 23
12:30:18 2022
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release
19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
---------- ------------------------------ ----------
----------
2
PDB$SEED READ
ONLY NO
3 PROD READ WRITE NO
SQL> alter session set container=PROD;
Session altered.
SQL> show user
USER is "SYS"
SQL> desc test
ERROR:
ORA-04043: object test does not exist
SQL> create table test(id number);
Table created.
SQL> insert into test values (1);
1 row created.
SQL> commit;
Commit complete.
SQL>
-------------------------on target check the table values
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ----------
----------
2
PDB$SEED READ ONLY
NO
3 PRODREF MOUNTED
4 PRODSTG READ WRITE NO
SQL>
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter pluggable database PRODREF refresh;
Pluggable database altered.
SQL>
-----------------at the same time alert log in the
target
2022-09-23T11:29:39.739979-04:00
alter pluggable database PRODREF refresh
2022-09-23T11:29:40.040005-04:00
Applying media recovery for pdb-4099 from SCN 142927690
to SCN 142933068
Remote log information: count-1
thr-1,seq-3315,logfile-/u01/oradata/archives/TRGCDB/foreign_archivelog/PROD/2022_08_23/o1_mf_1_3315_kj9x2wg2_.arc,los-142884167,nxs-18446744073709551615,maxblks-78629
PRODREF(3):Media Recovery Start
2022-09-23T11:29:40.040482-04:00
PRODREF(3):Serial Media Recovery started
PRODREF(3):max_pdb is 5
2022-09-23T11:29:40.067924-04:00
PRODREF(3):Media Recovery Log /u01/oradata/archives/TRGCDB/foreign_archivelog/PROD/2022_08_23/o1_mf_1_3315_kj9x2wg2_.arc
2022-09-23T11:29:40.335010-04:00
PRODREF(3):Incomplete Recovery applied until change
142933068 time 08/23/2022 11:38:04
2022-09-23T11:29:40.339377-04:00
PRODREF(3):Media Recovery Complete (TRGCDB)
Completed: alter pluggable database PRODREF refresh
-----------------------------target
SQL> show pdbs
CON_ID
CON_NAME OPEN
MODE RESTRICTED
---------- ------------------------------ ----------
----------
2
PDB$SEED READ
ONLY NO
3 PRODREF MOUNTED
4 PRODSTG READ WRITE NO
SQL>
Let’s now open PRODREF in Read Only mode to verify the
refresh
SQL> alter pluggable database PRODREF open read only;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ----------
----------
2
PDB$SEED READ ONLY NO
3 PRODREF READ ONLY NO
4 PRODSTG READ WRITE NO
SQL>
SQL> alter session set container=PRODREF;
Session altered.
SQL> select * from test;
ID
----------
1
SQL>
SQL> alter pluggable database PRODREF close
immediate;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ----------
----------
2 PDB$SEED READ ONLY NO
3 PRODREF MOUNTED
4 PRODSTG READ WRITE NO
8)
Set the refresh mode to refresh
at every 4 minutes
SQL> alter pluggable database PRODREF refresh mode
every 4 minutes;
Pluggable database altered.
SQL> select PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where PDB_NAME='PRODREF';
PDB_NAME
--------------------------------------------------------------------------------
REFRES REFRESH_INTERVAL LAST_REFRESH_SCN
------ ---------------- ----------------
PRODREF
AUTO
4 142935544
SQL>
SQL> select name from v$active_services;
NAME
----------------------------------------------------------------
TRGCDB.vij.com
PRODSTG
TRGCDBXDB
PRODREF
SYS$BACKGROUND
SYS$USERS
9)
We can follow above step no 7
to open it in Read only and query the data, once done need to close it again for
refresh to start automatically every 4 minutes
10)
Note that the automatic refresh
will success only if the PDB clone is mounted.
11)
Note also that a manual refresh
can be done even if the auto refresh is configured.
12) We can open the refreshable PDB in Read Write as below steps (Not performed in testing phase) , but once opened in Read Write can not be put back to refreshable mode and need to rebuild the refreshable clone. Used dummy PDB name - PDBRW as an example to show the commands to avoid accidental open of PRODREF.
To open the
database in a read write mode, we have to set the refresh mode to none, below steps
taken from the doc, not tested in our environment, change the PDB name
accordingly.
SQL> alter
pluggable database PDBRW refresh mode
none;
alter pluggable
database PDBRW refresh mode none
*
ERROR at line 1:
ORA-65025:
Pluggable database PDBRW is not closed on all instances.
SQL> alter pluggable database PDBRW close immediate;
Pluggable database altered.
SQL> alter pluggable database PDBRW refresh mode none;
Pluggable database altered.
SQL> col pdb_name for a15
SQL> select
PDB_NAME,REFRESH_MODE,REFRESH_INTERVAL,LAST_REFRESH_SCN from dba_pdbs where
PDB_NAME='PDBRW';
PDB_NAME REFRES REFRESH_INTERVAL
LAST_REFRESH_SCN
---------------
------ ---------------- ----------------
PDBRW NONE 39272683
SQL> alter
pluggable database PDBRW open read write;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBRW READ WRITE NO
13)
One other usage of refreshable
PDB is that the clone can be used as a golden master for snapshots at PDB
level. And these snapshots can be used for cloning environments for developers.
14)
You can create another PDB from
the Refreshed PDB and open it Read Write , thus your refreshable PDB would be intact and would be in refreshable
mode only.
E.g – On target server where you have created PRODREF refreshable PDB. Create Read Write PDB.
Sqlplus “/as sysdba”
Create pluggable database PRODRW from PRODREF;
alter pluggable database PRODRW open read write;
If there is Disaster at the source – primary server this can be served as Standby database for High Availability.
-------------------------------------------------------
Friday, 17 February 2023
Migrating Oracle Enterprise Edition Database to Standard Edition using full export and import process - 19c or Previous releases
Migrating Oracle Enterprise Edition Database to Standard Edition using full export and import process
Its 19c Enterprise with PDB architecture to 19c Standard Edition PDB architecture, Same can be used with NON-CDB architecture also.
Make sure RUP level is same in source and target.
Source PDB name – DEV and same PDB name would be used in target.
1) Take full export of DEV PDB on the source Enterprise Edition
During export sys schema objects are not exported.
Create dba directory EXPPUMP
nohup expdp system/manager@DEV directory=EXPPUMP FILESIZE=100GB COMPRESS=Y dumpfile=DEV%U.dmp FULL=Y logfile=FULLEXP_DEV.log &
Took 2 hrs for 600 GB DB size.
%U in dump file would automatically suffix the numbers like DEV01, DEV02 etc… after dump reaches FILESIZE=100GB
2) Use dbca to create 19c Standard Edition SE 2 CDB database with empty PDB.
Took 1 hour
3) Create PDB with same name as of source - DEV
Create pluggable database
Took 30 min
create pluggable database "DEV" admin user PDBADMIN identified by pass1234AB file_name_convert='/dev_data/oradata/pdb/pdbseed','/dev_data/oradata/DEV/datafiles');
4) Register the listener:
alter system set local_listener='(address=(protocol=tcp)(host=test.vij.com)(port=1521))' scope=spfile;
alter system register;
Bounce the database and listener
alter pluggable database DEV close immediate;
alter pluggable database DEV open;
alter pluggable database DEV save state;
5) Import the database:
Time taken 6-7 hrs
Create tablespaces in DEV (target) similar to those in DEV (source), you can check the tablespace list in export log above during full export.
Add 10 GB space temp tablespace to avoid import session from hanging.
Create dba directory EXPPUMP and copy the dump files during export in step 1.
nohup impdp system/manager@DEV directory= EXPPUMP dumpfile=DEV%U.dmp FULL=Y logfile=DEV_IMPORT_FULL.log &
Compile invalids using utlrp.sql
Check the invalids and compile using utlrp.sql
6) For pre 19c do full export and do full import , don’t use @DEV in expdp and impdp.
7) After the Import in the Standard Edition database, you only need to drop all user schemas related to Enterprise Edition features if exists.
Subscribe to:
Posts (Atom)