Zero downtime database upgrade from 11g to 12c using Oracle Goldengate

Zero downtime database upgrade from 11g to 12c using Oracle Goldengate

Description:-

In this article we are going to see Zero downtime database upgrade from 11g to 12c using Oracle Goldengate
1)Using Goldengate we are going to upgrade from 11g to 12c database without any downtime.
2)Already Data is available source11g scheme Hari.

Environment Detail’s:-

High Level Steps  upgrade from 11g to 12c database

1)check network between source and target.
2)install goldengate Software both side
3)setup extract and datapump on source site
4)setup replict on target side
5)export and import initial load using SCN
6)start the replicat using on scn

Source 11g database side GG Configuration

Step1:-Login in to 11g server and connect to Goldengate 

[oracle@gg-11 gghome]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Aug 7 2014 10:21:34
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (gg-11.2.com) dblogin userid gguser,password gguser
Successfully logged into database.

Step2:-Configure manager 

GGSCI (gg-11.2.com)view param mgr
PORT 7809

Step3:-Add schematrandata

GGSCI (gg-11.2.com )  add schematrandata hari

2018-07-31 19:56:12 INFO OGG-01788 SCHEMATRANDATA has been added on schema hari.
2018-07-31 19:56:12 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema hari.

GGSCI (gg-11.2.com ) info schematrandata hari

2018-07-31 19:56:34 INFO OGG-01785 Schema level supplemental logging is enabled on schema HARI.
2018-07-31 19:56:34 INFO OGG-01980 Schema level supplemental logging is enabled on schema HARI for all scheduling columns.

Step4:-Configure EXTRACT Process 

GGSCI (gg-11.2.com) view param ext1

EXTRACT ext1
SETENV (ORACLE_SID=”SOURCE”)
SETENV (ORACLE_HOME = “/u01/app/oracle/product/11.2.0/db_1”)
USERID gguser@source, PASSWORD gguser
DDL INCLUDE ALL
EXTTRAIL /u01/gghome/dirdat/ac
TABLE hari.*;

GGSCI (gg-11.2.com ) add extract ext1 tranlog begin now
EXTRACT added.

GGSCI (gg-11.2.com )  add exttrail /u01/gghome/dirdat/ac extract ext1
EXTTRAIL added.

GGSCI (gg-11.2.com as gguser@source)  info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:00 00:00:40

Step4:-Configure Pump Process  

GGSCI (gg-11.2.com )  view param dpump1

EXTRACT dpump1
USERID gguser@source, PASSWORD gguser
RMTHOST gg-12.2, MGRPORT 7810
RMTTRAIL /u01/gghome/dirdat/ad
DDL INCLUDE ALL
TABLE hari.*;

GGSCI (gg-11.2.com) add extract dpump1 exttrailsource /u01/gghome/dirdat/ac
EXTRACT added.

GGSCI (gg-11.2.com ) add rmttrail /u01/gghome/dirdat/ad extract dpump1
RMTTRAIL added.

GGSCI (gg-11.2.com )  info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED DPUMP1 00:00:00 00:01:00
EXTRACT STOPPED EXT1 00:00:00 00:04:40

Step5:-Start all the process and Check

GGSCI (gg-11.2.com )  start ext1

Sending START request to MANAGER …
EXTRACT EXT1 starting

GGSCI (gg-11.2.com )  start dpump1

Sending START request to MANAGER …
EXTRACT DPUMP1 starting

GGSCI (gg-11.2.com )  info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:04:33
EXTRACT RUNNING EXT1 00:00:00 00:00:03

Target side 12c database GG Configuration

Step1:-Configure manager 

GGSCI (gg-12.2.com) 9> dblogin userid gguser,password gguser
Successfully logged into database.

GGSCI (gg-12.2.com ) info mgr

Manager is running (IP port gg-12.2.com.7810, Process ID 3999).

GGSCI (gg-12.2.com ) view param mgr

PORT 7810

Step2:-Checkpoint table creation

GGSCI (gg-12.2.com)add checkpointtable gguser.checkpoint
Successfully created checkpoint table gguser.checkpoint.

Step3:-Configure Replicat Process 

GGSCI (gg-12.2.com) view param rep1

REPLICAT rep1
ASSUMETARGETDEFS
HANDLECOLLISIONS
USERID gguser@source, PASSWORD gguser
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE
MAP hari.*, TARGET hari.*;

GGSCI (gg-12.2.com )  add replicat rep1 exttrail /u01/gghome/dirdat/ad checkpointtable gguser.checkpoint
REPLICAT added.

GGSCI (gg-12.2.com ) 12> info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP1 00:00:00 00:00:03

Source side check before datapump starts

GGSCI (gg-11.2.com ) info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP1 00:00:00 00:00:00
EXTRACT RUNNING EXT1 00:00:00 00:00:10

Start the initial dataload using Datapump on source side 11g database

SQL> select current_scn from v$database;

CURRENT_SCN
———–
1584462

SQL> select count(*) from hari.chennai;

COUNT(*)
———-
1835008

Step1:-Export the table “chennai” using flashback_scn

[oracle@gg-11 gghome]$ expdp system/oracle dumpfile=hari1.dmp schemas=hari directory=DATA_PUMP_DIR logfile=hari1.log FLASHBACK_SCN=1584462

Export: Release 11.2.0.3.0 - Production on Tue Jul 31 20:02:25 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=hari1.dmp schemas=hari directory=DATA_PUMP_DIR logfile=hari1.log FLASHBACK_SCN=1584462
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 88 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "HARI"."CHENNAI" 72.63 MB 1835008 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/source/dpdump/hari1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:02:45

Step2:-copy the datapump files to 11g server to 12c server

[oracle@gg-11 gghome]$ cd /u01/app/oracle/admin/source/dpdump/
[oracle@gg-11 dpdump]$ scp hari1.dmp oracle@192.168.2.157:/u01/app/oracle/admin/source/dpdump/
The authenticity of host ‘192.168.2.157 (192.168.2.157)’ can’t be established.
RSA key fingerprint is c2:3d:72:16:52:01:ae:5c:41:6b:34:f5:c7:a1:df:74.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.2.157’ (RSA) to the list of known hosts.
oracle@192.168.2.157’s password:
hari1.dmp 100% 73MB 72.8MB/s 00:01

Step3:-After export i am inserting more records in Chennai table

[oracle@gg-11 ~]$ sqlplus hari/hari
SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 31 20:06:47 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into chennai select * from chennai;
1835008 rows created.

SQL> commit;
Commit complete.

SQL> select count(*) from chennai;
COUNT(*)
———-
3670016     

Step4:-Now we are going to Import 12c database (1835008 Rows)

[oracle@gg-12 dpdump]$ impdp system/oracle dumpfile=hari1.dmp logfile=hari1.log directory=DATA_PUMP_DIR

Import: Release 12.2.0.1.0 - Production on Tue Jul 31 20:09:23 2018
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
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=hari1.dmp logfile=hari1.log directory=DATA_PUMP_DIR
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HARI"."CHENNAI" 72.63 MB 1835008 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Tue Jul 31 20:09:57 2018 elapsed 0 00:00:24

Step5:-Target (12c) side going to start replicat process Using CSN 

GGSCI (gg-12.2.com ) start replicat rep1 aftercsn 1584462    ---(this scn number we get from 11g database)

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (gg-12.2.com )info all

Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01

Note :-Already initial load was completed using datapump,
Now extract was capturing all the changes happening on the source database 11g. These changes will now get applied to the target database 12c by the replicat process

[oracle@gg-12 dpdump]$ sqlplus hari/hari

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 31 20:19:26 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Tue Jul 31 2018 20:14:07 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from chennai;

COUNT(*)
----------
3670016 

Repoint the users from 11g to 12c server.

Successfully completed without downtime upgrade

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

 

Oracle Automation-Creating Oracle 12c Database Using Ansible Tool

Description:-

In this article we are going to see Oracle Automation-Oracle Database Creation Using Ansible Tool

Let’s start the Demo:-

Steps to create Oracle database using Ansible :-

Step 1:- Download the ansible modules from  github site.Click Here

Step 2:-Move the ZIP file to ansible server.

[root@ansible Desktop]# unzip ansible-oracle-master.zip
Archive: ansible-oracle-master.zip
1bbb35d690c102e23488537754ff8d1e0a99dc15
creating: ansible-oracle-master/
[root@ansible Desktop]# ls -lrt
total 4272
drwxrwxr-x. 8 ansible ansible 4096 Jun 26 2015 ansible-1.9.2
-rwxrw-rw-. 1 ansible ansible 75962 Jan 8 2018 ansible-oracle-modules-master.zip
-rwxrw-rw-. 1 ansible ansible 1642329 Jul 19 23:21 get-pip.py
-rwxrw-rw-. 1 ansible ansible 927525 Jul 25 02:59 ansible-1.9.2.tar.gz
-rwxrw-rw-. 1 ansible ansible 1357336 Jul 25 03:14 ansible-1.9.2-1.mga5.noarch.rpm
drwxr-xr-x. 7 root root 4096 Jul 30 11:12 ansible-oracle-master
drwxrwxr-x. 3 ansible ansible 4096 Jul 30 18:04 ansible-oracle-modules-master
-rwxrw-rw-. 1 ansible ansible 349507 Jul 30 22:33 ansible-oracle-master.zip

Step 3:- Create a single instance database on filesystem level

Sample file:- 

[root@ansible ]# cd ansible-oracle-master

[root@ansible ansible-oracle-master]# cat single-instance-db-on-fs.yml

[root@ansible ansible-oracle-master]#cat single-instance-db-on-fs.yml

- name: Host configuration
hosts: "{{ hostgroup }}"
become: yes
roles:
- common
- orahost
- cxoracle

- name: Database Server Installation & Database Creation
hosts: "{{ hostgroup }}"
become: yes
roles:
- oraswdb-install
- oradb-manage-db

- name: Database Server Installation & Database Creation
hosts: database-servers
user: oracle 
sudo: yes
roles:
- oraswdb-install
- oradb-create

Note :I have already installed all prerequisites and oracle binaries hence i am not using that role oraswdb-install

Step 4:- Go to  ansible-oracle-master/roles folder.Check oradb-create role is present for database creation.

[root@ansible ansible-oracle-master]# cd roles/
[root@ansible roles]# ls -lrt
total 0
drwxr-xr-x. 5 root root 61 Jul 30 11:12 oraswracdb-clone
drwxr-xr-x. 3 root root 21 Jul 30 11:12 orasw-meta
drwxr-xr-x. 5 root root 49 Jul 30 11:12 oraswgi-opatch
drwxr-xr-x. 6 root root 60 Jul 30 11:12 oraswgi-manage-patches
drwxr-xr-x. 5 root root 64 Jul 30 11:12 oraswgi-install
drwxr-xr-x. 6 root root 72 Jul 30 11:12 oraswgi-clone
drwxr-xr-x. 6 root root 60 Jul 30 11:12 oraswdb-manage-patches
drwxr-xr-x. 6 root root 60 Jul 30 11:12 oraswdb-install
drwxr-xr-x. 6 root root 61 Jul 30 11:12 orahost-storage
drwxr-xr-x. 4 root root 49 Jul 30 11:12 orahost-ssh
drwxr-xr-x. 5 root root 65 Jul 30 11:12 orahost-logrotate
drwxr-xr-x. 4 root root 49 Jul 30 11:12 orahost-cron
drwxr-xr-x. 7 root root 76 Jul 30 11:12 orahost
drwxr-xr-x. 5 root root 49 Jul 30 11:12 oraemagent-install
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-users
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-tablespace
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-services
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-roles
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-redo
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-pdb
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-parameters
drwxr-xr-x. 5 root root 44 Jul 30 11:12 oradb-manage-grants
drwxr-xr-x. 6 root root 76 Jul 30 11:12 oradb-manage-db
drwxr-xr-x. 5 root root 49 Jul 30 11:12 oradb-failover
drwxr-xr-x. 4 root root 33 Jul 30 11:12 oradb-delete
drwxr-xr-x. 5 root root 60 Jul 30 11:12 oradb-datapatch
drwxr-xr-x. 5 root root 49 Jul 30 11:12 oradb-create
drwxr-xr-x. 4 root root 49 Jul 30 11:12 oraasm-manage-diskgroups
drwxr-xr-x. 5 root root 49 Jul 30 11:12 oraasm-createdg
drwxr-xr-x. 4 root root 33 Jul 30 11:12 cxoracle
drwxr-xr-x. 5 root root 49 Jul 30 11:12 common

[root@ansible roles]# cd oradb-create
[root@ansible oradb-create]# ls -lrt
total 4
drwxr-xr-x. 2 root root 4096 Jul 30 11:12 templates
drwxr-xr-x. 2 root root 40 Jul 30 11:12 tasks
drwxr-xr-x. 2 root root 21 Jul 30 11:12 defaults
[root@ansible oradb-create]# ls -lrt templates/
total 332
-rwxr-xr-x. 1 root root 6119 Jul 30 11:12 netca.rsp.12.2.0.1.j2
-rw-r--r--. 1 root root 6131 Jul 30 11:12 netca.rsp.12.1.0.2.j2
-rw-r--r--. 1 root root 6131 Jul 30 11:12 netca.rsp.12.1.0.1.j2
-rwxr-xr-x. 1 root root 5954 Jul 30 11:12 netca.rsp.11.2.0.4.j2
-rwxr-xr-x. 1 root root 5954 Jul 30 11:12 netca.rsp.11.2.0.3.j2
-rw-r--r--. 1 root root 1283 Jul 30 11:12 dotprofile-db.j2
-rw-r--r--. 1 root root 29254 Jul 30 11:12 dbca-create-db.rsp.12.2.0.1.j2
-rwxr-xr-x. 1 root root 78121 Jul 30 11:12 dbca-create-db.rsp.12.1.0.2.j2
-rwxr-xr-x. 1 root root 78122 Jul 30 11:12 dbca-create-db.rsp.12.1.0.1.j2
-rwxr-xr-x. 1 root root 47467 Jul 30 11:12 dbca-create-db.rsp.11.2.0.4.j2
-rwxr-xr-x. 1 root root 47394 Jul 30 11:12 dbca-create-db.rsp.11.2.0.3.j2
[root@ansible oradb-create]# ls -lrt tasks/
total 8
-rwxr-xr-x. 1 root root 4014 Jul 30 11:12 main.yml
-rw-r--r--. 1 root root 706 Jul 30 11:12 listener.yml
[root@ansible oradb-create]# ls -lrt defaults
total 8
-rw-r--r--. 1 root root 6860 Jul 30 11:12 main.yml

Roles in ansible tool :-

Roles expect files to be in certain directory names. Roles must include at least one of these directories, however it is perfectly fine to exclude any which are not being used. When in use, each directory must contain a main.yml file, which contains the relevant content:

Role directory structure :-

roles/
   common/
     tasks/
     handlers/
     files/
     templates/
     vars/
     defaults/
     meta/
   webservers/
     tasks/
     defaults/
     meta/
  • tasks – contains the main list of tasks to be executed by the role.
  • defaults – default variables for the role 
  • templates- contains templates which can be deployed via this role.

Install tree rpm to see the execution of .yml file :-

[root@ansible roles]# yum install tree
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package tree.x86_64 0:1.6.0-10.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================================================
Package Arch Version Repository Size
==========================================================================================================================
Installing:
tree x86_64 1.6.0-10.el7 ol7_latest 46 k

Transaction Summary
==========================================================================================================================
Install 1 Package
Total download size: 46 k
Installed size: 83 k
Is this ok [y/d/N]: y
Downloading packages:
tree-1.6.0-10.el7.x86_64.rpm SUCCESS

Step 5:- Run the tree command and see the execution of .yml files to create a database

[root@ansible roles]# tree oraswdb-install
oraswdb-install
├── defaults
│   └── main.yml
├── meta
│   └── main.yml
├── tasks
│   ├── 11.2.0.3.yml
│   ├── 11.2.0.4.yml
│   ├── 12.1.0.1.yml
│   ├── 12.1.0.2.yml
│   ├── 12.2.0.1.yml
│   ├── 18.3.0.0.yml
│   ├── curl.yml
│   ├── get_url.yml
│   ├── init.yml
│   ├── install-home-db.yml
│   ├── main.yml
│   ├── remove-home.yml
│   └── systemd.yml
└── templates
├── db-install.rsp.11.2.0.3.j2
├── db-install.rsp.11.2.0.4.j2
├── db-install.rsp.12.1.0.1.j2
├── db-install.rsp.12.1.0.2.j2
├── db-install.rsp.12.2.0.1.j2
├── db-install.rsp.18.3.0.0.j2
├── dbora.j2
├── dotprofile-home.j2
├── glogin.sql.j2
├── oracle-rdbms-service.j2
├── oraInst.loc.j2
└── run-db-install.sh.j2

4 directories, 27 files

Step 6:- I have copied the single-instance-db-on-fs.yml file and named as dbcreation.yml as per the host entry and environment.

Change the main.yml entry present in defaults directory  as below:-

[root@ansible ~]# cd /home/ansible/Desktop/ansible-oracle-master/roles/oradb-create/defaults

The below data is used to create a new database using ansible tool.

[root@ansible ansible-oracle-master]# cat dbcreation.yml 

- name: Database Server Installation & Database Creation
hosts: db
user: oracle 
sudo: yes
roles:
- oradb-create

Step 7:- Run the playbook dbcreation.yml file to create a database.

[root@ansible ansible-oracle-master]# ansible-playbook dbcreation.yml

PLAY [Database Server Installation & Database Creation] *******************************************************

GATHERING FACTS ***************************************************************
ok: [test]

TASK: [oradb-create | set fact] ***********************************************
ok: [test] => (item={'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'})

TASK: [oradb-create | listener | Create responsefile for listener configuration] ***
ok: [test] => (item={'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'})

TASK: [oradb-create | listener | Create listener] **************************************************
ok: [test] => (item={'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'})

TASK: [oradb-create | Check if database is already created] *************************************************************
changed: [test] => (item={'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'})

TASK: [oradb-create | Copy custom dbca Templates to ORACLE_HOME/assistants/dbca/templates] ***
skipping: [test] => (item=[{'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'}])

TASK: [oradb-create | Create responsefile for dbca] ****************************************************
ok: [test] => (item=[{'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'}, {u'cmd': u'grep orcl:/u01/app/oracle/product/12.1.0/dbhome_1 /etc/oratab |wc -l', u'end': u'2018-07-30 17:41:58.213870', u'stderr': u'', u'stdout': u'0', u'changed': True, u'rc': 0, 'item': {'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'}, u'warnings': [], u'delta': u'0:00:00.060829', 'invocation': {'module_name': u'shell', 'module_args': u'grep orcl:/u01/app/oracle/product/12.1.0/dbhome_1 /etc/oratab |wc -l'}, 'stdout_lines': [u'0'], u'start': u'2018-07-30 17:41:58.153041'}])

TASK: [oradb-create | Create database(s)] ******************************************

changed: [test] => (item=[{'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'}, {u'cmd': u'grep orcl:/u01/app/oracle/product/12.1.0/dbhome_1 /etc/oratab |wc -l', u'end': u'2018-07-30 17:41:58.213870', u'stderr': u'', u'stdout': u'0', u'changed': True, u'rc': 0, 'item': {'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'}, u'warnings': [], u'delta': u'0:00:00.060829', 'invocation': {'module_name': u'shell', 'module_args': u'grep orcl:/u01/app/oracle/product/12.1.0/dbhome_1 /etc/oratab |wc -l'}, 'stdout_lines': [u'0'], u'start': u'2018-07-30 17:41:58.153041'}])

TASK: [oradb-create | debug var=item] **************************************
ok: [test] => (item=[u'Copying database files', u'1% complete', u'3% complete', u'11% complete', u'18% complete', u'26% complete', u'37% complete', u'Creating and starting Oracle instance', u'40% complete', u'45% complete', u'50% complete', u'55% complete', u'56% complete', u'60% complete', u'62% complete', u'Completing Database Creation', u'66% complete', u'70% complete', u'73% complete', u'85% complete', u'96% complete', u'100% complete', u'Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log" for further details.']) => {
"item": [
"Copying database files",
"1% complete",
"3% complete",
"11% complete",
"18% complete",
"26% complete",
"37% complete",
"Creating and starting Oracle instance",
"40% complete",
"45% complete",
"50% complete",
"55% complete",
"56% complete",
"60% complete",
"62% complete",
"Completing Database Creation",
"66% complete",
"70% complete",
"73% complete",
"85% complete",
"96% complete",
"100% complete",
"Look at the log file \"/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log\" for further details."
],
"var": {
"item": [
"Copying database files",
"1% complete",
"3% complete",
"11% complete",
"18% complete",
"26% complete",
"37% complete",
"Creating and starting Oracle instance",
"40% complete",
"45% complete",
"50% complete",
"55% complete",
"56% complete",
"60% complete",
"62% complete",
"Completing Database Creation",
"66% complete",
"70% complete",
"73% complete",
"85% complete",
"96% complete",
"100% complete",
"Look at the log file \"/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log\" for further details."
]
}
}

TASK: [oradb-create | Add dotprofile (1)] *****************************************
ok: [test] => (item={'oracle_init_params': '', 'oracle_db_type': 'SI', 'service_name': 'orcl_serv', 'listener_name': 'LISTENER_ORCL', 'storage_type': 'FS', 'oracle_db_passwd': 'oracle', 'home': 'dbhome_1', 'is_container': False, 'redolog_size_in_mb': 100, 'num_pdbs': 1, 'pdb_prefix': 'pdb', 'oracle_db_name': 'orcl', 'state': 'present', 'oracle_edition': 'EE', 'oracle_db_mem_totalmb': 1024, 'oracle_database_type': 'MULTIPURPOSE', 'oracle_version_db': '12.1.0.2'})

test : ok=10 changed=2 unreachable=0 failed=0

Step 8:- Check the database and listener status in remote host test.localdomain.com

[oracle@test ~]$ ps -ef|grep pmon
oracle 1943 1875 0 19:19 pts/5 00:00:00 grep --color=auto pmon
oracle 22982 1 0 Jul29 ? 00:00:04 ora_pmon_test
oracle 64776 1 0 18:15 ? 00:00:00 ora_pmon_orcl

[oracle@test ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 30-JUL-2018 19:20:35
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 26-JUL-2018 15:59:24
Uptime 4 days 3 hr. 21 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test.localdomain.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/test/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
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 "test.localdomain.com" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB.localdomain.com" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step by Step to Configure Oracle 18c Database using DBCA Method

Description:-

In this article we are going to see Step by Step to Configure Oracle 18c Database using DBCA Method.

  1. we are going to create a 18.3 new database using dbca method
  2. Database name orcl101
  3. Oracle 18.3 Software installation here

Let’s start the demo:-

bash_profile

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=prod101.oracledbwr.com; export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl101; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01; export ORACLE_HOME
ORACLE_SID=orcl101; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Step1:- Select Create database

Step2:-select Advance Configuration

Step3:-Click next

Step4:-Database name “orcl101”

Step5:-Click next

Step6:-click next

Step7:-select listener 

Step8:-click next

Step9:-select ASMM ad click nextStep:10 Click next

Step11:- Set Password Step12:-Create database

Step13:-Click Finesh

Step14:-Database is in progress Step15:-Oracle 18c Database Creation completed. Step16:-Check the database Status

[oracle@prod101:~ orcl101] sqlplus '/as sysdba'
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jul 29 19:59:05 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select name from v$database;
NAME
---------
ORCL101

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Oracle Automation-Create a DBA User Using Ansible Tool

Steps to create DBA user using ansible tool :-

 

 

 

Presteps have to be done in SOURCE (ansible) server,

[root@ansible ansible-oracle-modules-master]# cat /etc/ansible/hosts

[database-servers]
test
[root@ansible ansible-oracle-modules-master]# ansible all -m ping
test | success >> {
"changed": false,
"ping": "pong"
}

Presteps have to be done in  TARGET (TEST database) server,

Step 1:- Install PIP package in TARGET side.

[root@test oracle]# curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py"
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 1604k 100 1604k 0 0 71531 0 0:00:22 0:00:22 --:--:-- 203k


[root@test oracle]# python get-pip.py
Collecting pip
Downloading https://files.pythonhosted.org/packages/5f/25/e52d3f31441505a5f3af41213346e5b6c221c9e086a166f3703d2ddaf940/pip-18.0-py2.py3-none-any.whl (1.3MB)
100% |████████████████████████████████| 1.3MB 164kB/s
Collecting wheel
Downloading https://files.pythonhosted.org/packages/81/30/e935244ca6165187ae8be876b6316ae201b71485538ffac1d718843025a9/wheel-0.31.1-py2.py3-none-any.whl (41kB)
100% |████████████████████████████████| 51kB 156kB/s
Installing collected packages: pip, wheel
Successfully installed pip-18.0 wheel-0.31.1

Step 2:- Install and upgrade the cx_oracle package,

[root@test oracle]# pip -V
pip 18.0 from /usr/lib/python2.7/site-packages/pip (python 2.7)

[root@test oracle]# python -m pip install cx_Oracle --upgrade
Collecting cx_Oracle
Downloading https://files.pythonhosted.org/packages/3b/09/6b10675a6db7c7da1b8d23225f0a95b2a45248c56a1e8f711d59809278d3/cx_Oracle-6.4.1-cp27-cp27mu-manylinux1_x86_64.whl (590kB)
100% |████████████████████████████████| 593kB 256kB/s
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-6.4.1

Step 3:- Import the cx_oracle package,

[root@test oracle]# python
Python 2.7.5 (default, Sep 5 2016, 02:30:38)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-9)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> exit()

SOURCE :-

Step 1:- Download oracle user from github site.Click Here

Step 2:- Move the ZIP file to ansible server.

Step 3:- Create a directory library where .yml is created.

Step 4:- Copy the oracle_user role to library directory

[root@ansible ansible-oracle-modules-master]# mkdir -p library
[root@ansible ansible-oracle-modules-master]# cp oracle_user /home/ansible/Desktop/ansible-oracle-modules-master/library/

Step 5:- Modify the variables as per your environment.

[root@ansible ansible-oracle-modules-master]# cat usercreation.yml
- hosts: database-servers
user: root
gather_facts: false

vars:
oracle_home: /u01/app/oracle/product/12.1.0/dbhome_1/
hostname: test.localdomain.com
service_name: test.localdomain.com
user: system
password: oracle
oracle_env:
ORACLE_HOME: "{{ oracle_home }}"
LD_LIBRARY_PATH: "{{ oracle_home }}/lib"

dba_user:
- schema: india
schema_password: india
state: present
default_tablespace: users
grants:
- "dba"
tasks:

- name: Create DBA user
oracle_user: hostname={{ hostname }} service_name={{ service_name }} mode=normal user={{ user }} password={{ password }} schema={{ item.0.schema }} schema_password={{ item.0.schema_password }} state={{ item.0.state }} default_tablespace={{ item.0.default_tablespace }} grants={{ item.1 }}
environment: "{{ oracle_env }}"
with_subelements:
- dba_user
- grants

In TARGET side,check the user in TEST database

Step 6:- Run the playbook file usercreation.yml

Output:-

[root@ansible ansible-oracle-modules-master]# ansible-playbook usercreation.yml

PLAY [database-servers] *******************************************************

TASK: [Create DBA user] *******************************************************
changed: [test] => (item=({'schema_password': 'india', 'state': 'present', 'default_tablespace': 'users', 'schema': 'india'}, 'dba'))

PLAY RECAP ********************************************************************
test : ok=1 changed=1 unreachable=0 failed=0

TARGET :-

Step 7 :- Check the user INDIA is created in TARGET database.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step by Step Oracle 18c Installation On Linux

Step by Step Oracle 18c Installation On Linux

Description:-

In this article we are going to see new On-Premise Oracle Database 18c (18.3) for Linux x86-64 installation.

Linux x86-64 installation click  here

Let’s start the oracle binaries installation and oracle 18c database creation.  

Step:1 Click create and configure a single instance database

Step:2 Click Desktop

Step3:-Enter database name “Chennai”

Step4:-check Prerequisite 

Step5:-Click install

Step6:-install in progress 

step7:-execute the below script in root user

Step8:-execute script

[root@prod101 ~]# /u01/root.sh
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01
Enter the full pathname of the local bin directory: [/usr/local/bin]: /u01/app/oracle/product/18.3.0.0/db_1
Copying dbhome to /u01/app/oracle/product/18.3.0.0/db_1 ...
Copying oraenv to /u01/app/oracle/product/18.3.0.0/db_1 ...
Copying coraenv to /u01/app/oracle/product/18.3.0.0/db_1 ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] : 
yes
Installing Oracle Trace File Analyzer (TFA).
Log File: /u01/install/root_prod101.oracledbwr.com_2018-07-24_03-25-22-749677449.log
Finished installing Oracle Trace File Analyzer (TFA)

Step9:-Database creation in progress 

Step10:-Installation completed

Step11:check database status

[oracle@prod101:bin chennai] sqlplus '/as sysdba'

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jul 24 03:50:44 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0


SQL> select instance_name,version from v$instance;

INSTANCE_NAME VERSION
---------------- -----------------
chennai 18.0.0.0.0

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Installation and Configuration of Ansible

Installation and Configuration of Ansible

Introduction to Ansible :-

Ansible is an agent-less IT automation tool developed in 2012 by Michael DeHaan, a former Red Hat associate. The Ansible design goals are : minimal, consistent, secure, highly reliable, and easy to learn.Ansible is Written on Python language.It also available as Open Source.

Why to use Ansible as automation tool ?

  • There are many other IT automation tools available, including more mature ones like Puppet and Chef, so why would we choose Ansible? The main reason is simplicity.
  • Ansible is using YAML syntax. It’s simple configuration language.
    Puppet and Chef are using Ruby Syntax. which is more difficult to learn.
  • Ansible is an agentless tool.Instead of install an agent on every target machine, Ansible just requires that target systems have Python (on Linux servers) or PowerShell (on Windows servers) and SSH.But puppet and chef have to install agent on each target machines.

Architecture Diagram :-

High Level Steps :-

  • Install Python software
  • Install PIP to install Cx_Oracle package to perform SQL commands
  • Install ansible rpm
  • Configure SSH to connect remote hosts
  • Run the ansible command to access remote hosts.

Step 1:- Check the Current version of Python in OEL 7:-

[root@ansible ~]$ python -V
Python 2.7.5

Note:- Minimum version of python 2.7.6 is needed to install ansible.

Step 2:- Download the Python 2.7.6 package

[root@ansible ansible]# wget -c https://www.python.org/ftp/python/2.7.6/Python-2.7.6.tgz --no-check-certificate
--2018-07-19 02:16:25-- https://www.python.org/ftp/python/2.7.6/Python-2.7.6.tgz
Resolving www.python.org (www.python.org)... 151.101.184.223, 2a04:4e42:2c::223
Connecting to www.python.org (www.python.org)|151.101.184.223|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 14725931 (14M) [application/octet-stream]
Saving to: ‘Python-2.7.6.tgz’

100%[============================================================================================================>] 14,725,931 240KB/s in 65s

2018-07-19 02:17:32 (221 KB/s) - ‘Python-2.7.6.tgz’ saved [14725931/14725931]


Step 3:- Unzip the downloaded python package

[root@ansible ~]$ tar -xvzf Python-2.7.6.tgz 

[root@ansible ~]$ cd Python-2.7.6

[ansible@ansible Python-2.7.6]$ ls -lrt
total 956
-rw-r--r--. 1 ansible ansible 12749 Nov 10 2013 LICENSE
drwxr-xr-x. 2 ansible ansible 4096 Nov 10 2013 Include
drwxr-xr-x. 2 ansible ansible 20 Nov 10 2013 Grammar
drwxr-xr-x. 22 ansible ansible 4096 Nov 10 2013 Demo
drwxr-xr-x. 46 ansible ansible 8192 Nov 10 2013 Lib
drwxr-xr-x. 22 ansible ansible 4096 Nov 10 2013 Tools
-rw-r--r--. 1 ansible ansible 97308 Nov 10 2013 setup.py
drwxr-xr-x. 5 ansible ansible 4096 Nov 10 2013 RISCOS
-rw-r--r--. 1 ansible ansible 53972 Nov 10 2013 README
drwxr-xr-x. 2 ansible ansible 4096 Nov 10 2013 Python
-rw-r--r--. 1 ansible ansible 34829 Nov 10 2013 pyconfig.h.in
drwxr-xr-x. 2 ansible ansible 4096 Nov 10 2013 PCbuild
drwxr-xr-x. 10 ansible ansible 4096 Nov 10 2013 PC
drwxr-xr-x. 2 ansible ansible 4096 Nov 10 2013 Parser
drwxr-xr-x. 3 ansible ansible 4096 Nov 10 2013 Objects
drwxr-xr-x. 9 ansible ansible 4096 Nov 10 2013 Modules
drwxr-xr-x. 5 ansible ansible 4096 Nov 10 2013 Misc
-rw-r--r--. 1 ansible ansible 44172 Nov 10 2013 Makefile.pre.in
drwxr-xr-x. 11 ansible ansible 4096 Nov 10 2013 Mac
-rwxr-xr-x. 1 ansible ansible 7122 Nov 10 2013 install-sh
-rw-r--r--. 1 ansible ansible 133573 Nov 10 2013 configure.ac
-rwxr-xr-x. 1 ansible ansible 426595 Nov 10 2013 configure
-rwxr-xr-x. 1 ansible ansible 35639 Nov 10 2013 config.sub
-rwxr-xr-x. 1 ansible ansible 44851 Nov 10 2013 config.guess
drwxr-xr-x. 16 ansible ansible 4096 Nov 10 2013 Doc

Step 4:- Configuring python 2.7.6 software

Create the below directory ‘/home/ansible/python’ and configure,install the software.

[ansible@ansible Python-2.7.6]# ./configure --prefix=/home/ansible/python

[ansible@ansible Python-2.7.6]# make

[ansible@ansible Python-2.7.6]# make install

After installation,the following files are created under ‘/home/ansible/python’

[ansible@ansible Python-2.7.6]# ls -lrt /home/ansible/python
total 4
drwxr-xr-x. 3 ansible ansible 22 Jul 19 21:10 include
drwxr-xr-x. 4 ansible ansible 59 Jul 19 21:10 lib
drwxr-xr-x. 3 ansible ansible 16 Jul 19 21:10 share
drwxr-xr-x. 2 ansible ansible 4096 Jul 19 21:10 bin

Now,check the new python version 2.7.6

[root@ansible ~]$ cd python/bin/
[root@ansible bin]$ python -V
Python 2.7.6

[root@ansible bin]# which python
/home/ansible/bin/python

Step 5:- Download PIP package

[root@ansible ~]$ curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py"
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
100 1603k 100 1603k 0 0 164k 0 0:00:09 0:00:09 --:--:-- 222k

[root@ansible ansible]# python get-pip.py
Collecting pip
Downloading https://files.pythonhosted.org/packages/0f/74/ecd13431bcc456ed390b44c8a6e917c1820365cbebcb6a8974d1cd045ab4/pip-10.0.1-py2.py3-none-any.whl (1.3MB)
100% |████████████████████████████████| 1.3MB 709kB/s
Collecting wheel
Downloading https://files.pythonhosted.org/packages/81/30/e935244ca6165187ae8be876b6316ae201b71485538ffac1d718843025a9/wheel-0.31.1-py2.py3-none-any.whl (41kB)
100% |████████████████████████████████| 51kB 1.2MB/s
Installing collected packages: pip, wheel
Successfully installed pip-10.0.1 wheel-0.31.1

[root@ansible ansible]# python -m pip install cx_Oracle --upgrade
Collecting cx_Oracle
Downloading https://files.pythonhosted.org/packages/3b/09/6b10675a6db7c7da1b8d23225f0a95b2a45248c56a1e8f711d59809278d3/cx_Oracle-6.4.1-cp27-cp27mu-manylinux1_x86_64.whl (590kB)
100% |████████████████████████████████| 593kB 1.2MB/s
Installing collected packages: cx-Oracle
Successfully installed cx-Oracle-6.4.1

Check the PIP version :-

[root@ansible ansible]# pip -V
pip 10.0.1 from /usr/lib/python2.7/site-packages/pip (python 2.7)

We need the Cx_Oracle Package for executing SQL’s , performing any task inside SQL prompt.

[root@ansible ansible]# python
Python 2.7.5 (default, Sep 5 2016, 02:30:38)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-9)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> exit()

Installation of Ansible software :-

Step 6:-To get Ansible for Linux, first ensure that the RHEL7 EPEL repository is installed:

[root@ansible ~]# rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
Retrieving https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
warning: /var/tmp/rpm-tmp.dwLJVq: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:epel-release-7-11 ################################# [100%]

Step 7:- Once the repository is installed, install Ansible with yum:

[root@ansible ~]# yum install ansible

Step 8:- Check the ansible version and configuration file path

[root@ansible ~]$ ansible --version
ansible 2.6.1
config file = /etc/ansible/ansible.cfg
configured module search path = [u'/home/ansible/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python2.7/site-packages/ansible
executable location = /bin/ansible
python version = 2.7.5 (default, Sep 5 2016, 02:30:38) [GCC 4.8.5 20150623 (Red Hat 4.8.5-9)]

Step 9:- Configuring ansible hosts and create inventory file.

The ‘databaseservers‘ in the brackets indicates as group names to list number of remote hosts.

[root@ansible ~]# vi /etc/ansible/hosts

Added the below remote host entry to the /etc/ansible/hosts file

[database-servers]
demo.localdomain.com

Step 10:- List the remote hosts which are able to use ansible commands

[root@ansible ~]$ ansible --list-host all
hosts (1):
192.168.86.141

[ansible@ansible ~]$ ansible --list-host database-servers
hosts (1):
192.168.86.141

SSH Key creation :-

Step 11:- Create the SSH user equivalence in the ansible server.

[ansible@ansible ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/ansible/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/ansible/.ssh/id_rsa.
Your public key has been saved in /home/ansible/.ssh/id_rsa.pub.
The key fingerprint is:
3f:58:1b:ca:6c:2a:66:0d:63:ea:c3:18:a3:45:45:43 ansible@ansible.localdomain.com
The key's randomart image is:
+--[ RSA 2048]----+
| oE |
| .. |
| . |
| . |
| . S o |
|o . + o = o |
|.* o + * + |
|o + + .o . |
| ..+ .. |
+-----------------+

[ansible@ansible ~]$ cd .ssh/
[ansible@ansible .ssh]$ ls -lrt
total 8
-rw-r--r--. 1 ansible ansible 413 Jul 20 19:40 id_rsa.pub
-rw-------. 1 ansible ansible 1675 Jul 20 19:40 id_rsa

Step 12:- Copy the SSH key to the remote servers

[root@ansible .ssh]# ssh-copy-id root@demo.localdomain.com
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@demo.localdomain.com's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh 'root@demo.localdomain.com'"
and check to make sure that only the key(s) you wanted were added.

Step 13:- Now,we are able to ping the remote hosts,

[root@ansible .ssh]# ansible all -m ping
demo.localdomain.com | SUCCESS => {
"changed": false,
"ping": "pong"

Step 14 :- To check the partitions on all remote hosts

[root@ansible ~]# ansible -m command -a "df -h" database-servers
demo.localdomain.com | SUCCESS | rc=0 >>
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_rac1-lv_root
36G 19G 16G 55% /
tmpfs 2.0G 276K 2.0G 1% /dev/shm
/dev/sda1 485M 82M 378M 18% /boot

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Oracle Database Rolling Upgrade From 11g to 12c Using a Data Guard

Description:- 

  • Database Rolling Upgrade using Transient Logical Standby (SQL Apply)
  • Allows for running different database versions during rolling upgrade window.
  • Gives you more time to test the new version, before activating it.
  • In case you already use Data Guard – no additional hardware/storage(*) footprint,license fees and knowledge(*) necessary.
  • Near zero downtime – reduced to one switchover (two, if you go back).
  • Fairly simple method, compared to some other replication technologies.But with some restrictions.

High Level Steps:-

Environment Details:- Lets Start the Demo:-

  • We have primary & standby database running in 11.2.0.4 Here
  • Already installed oracle 12.2.0.1 binaries on both nodes

Step1:-Check the Standby database status

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1                      14              14             0

Step2:-Stop the mrp process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 3:-Open the database in read only mode

SQL>ALTER DATABASE OPEN READ ONLY;

Database altered.

Step 4:-Ensure Flashback is ON in both primary and standby databases

SQL>SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
————–
YES

Step 5:-Create restore point at Primary database

SQL>CREATE RESTORE POINT BEFORE_UPGRADE GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>select GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;

GUARANTEE_FLASHBACK_DATABASE              Name
———————————–    ——————–
YES                                                                                          BEFORE_UPGRADE

START APPLY PROCESS:

Step 6:- Start the log apply process by executing the below query in standby and make sure the standby is in sync with primary

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

LOGICAL STANDBY DATABASE CONVERSION:

Step 1:-Stop the media recovery at standby database

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 2:-Build data dictionary at primary database for logical standby database

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Step 3:-Convert physical standby database to logical standby database (ensure database is not in open state)

SQL>ALTER DATABASE CLOSE;

Database altered.

SQL>ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;

Database altered.

Step 4:-Restart the database, once it gets started logical standby role will be enabled

SQL> SHUT IMMEDIATE
SQL> STARTUP
SQL>SELECT DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;

DB Unique Name Database Role
———————— —————-
ORCL_PROD102 LOGICAL STANDBY

Step 5:-Start the log shipping process at logical standby database

SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

Check the alert log for the Logminer process would have been started in the background:-

$tail -100f alert_ORCL.log

LOGSTDBY Apply process AS02 started with server id=2 pid=46 OS id=38615
LOGMINER: End mining logfile for session 1 thread 1 sequence 16, /oradb/app/oracle/archive/orcl_prod102/orclp102_1_16_981941460.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 17, /oradb/app/oracle/archive/orcl_prod102/orclp102_1_17_981941460.arc
LOGMINER: End mining logfile for session 1 thread 1 sequence 17, /oradb/app/oracle/archive/orcl_prod102/orclp102_1_17_981941460.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 18, /oradb/app/oracle/archive/orcl_prod102/orclp102_1_18_981941460.arc
LOGMINER: End mining logfile for session 1 thread 1 sequence 18, /oradb/app/oracle/archive/orcl_prod102/orclp102_1_18_981941460.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 19, /oradb/app/oracle/oradata/ORCL_PROD102/redo05.log

Also you can notice archive log sequence got reset in Standby:-

SQL>ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradb/app/oracle/archive/orcl_prod102
Oldest online log sequence         1
Next log sequence to archive    2
Current log sequence                  2

Step 6:-Check if the data dictionary build is completed by executing below query in primary

SQL>SELECT * FROM V$LOGSTDBY_STATE WHERE STATE = ‘LOADING DICTIONARY’;

no rows selected

START THE UPGRADE PROCESS:

Step 1:-Upgrade Logical standby database to Oracle 12c (12.2.0.1.0) database version

Create the below directory before starting the upgrade and run the preupgrade.jar from Oracle 12.2.0.1 home as below and check the logs in the below created log directory if any prerequisite as to be done before the upgrade:-

$ mkdir -p /oradb/app/oracle/scripts/logs/12cR2
$ cd /oradb/app/oracle/scripts/logs/12cR2
$ sqlplus ‘/ as sysdba’
$ /oradb/app/oracle/product/11.2.0.4/db_1/jdk/bin/java -jar /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/admin/preupgrade.jar FILE TEXT DIR /oradb/app/oracle/scripts/logs/12cR2

Upgrade can be done either using DBUA or Manual upgrade. Here we followed manual upgrade:
Shutdown logical standby database and it’s listener at Oracle 11g (11.2.0.4)

SQL>SHUTDOWN IMMEDIATE

$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 21-JUL-2018 03:54:21

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod102.oracledbwr.com)(PORT=1624)))
The command completed successfully

Step 2:-Copy listener.ora and tnsnames.ora to 12c ORACLE_HOME and edit listener.ora to 12c ORACLE_HOME. Copy pfile and password file to Oracle 12c (12.2.0.1.0) home

$ export ORACLE_HOME=/oradb/app/oracle/product/12.2.0.1/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_SID=ORCL
$ sqlplus ‘/ as sysdba’

SQL> STARTUP UPGRADE

Step 3:-Start the manual upgrade process using catctl.pl as below

$ cd $ORACLE_HOME/rdbms/admin
$ /oradb/app/oracle/product/12.2.0.1/db_1/perl/bin/perl catctl.pl -n 4 -l /oradb/app/oracle/scripts/logs/12cR2 catupgrd.sql

Argument list for [catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /oradb/app/oracle/scripts/logs/12cR2
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 4
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [12.2.0.1.0]
STATUS: [production]
BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125]
/oradb/app/oracle/product/12.2.0.1/db_1/rdbms/admin/orahome = [/oradb/app/oracle/product/12.2.0.1/db_1]
/oradb/app/oracle/product/12.2.0.1/db_1/bin/orabasehome = [/oradb/app/oracle/pro
catctlGetOrabase = [/oradb/app/oracle/product/12.2.0.1/db_1]
Analyzing file /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/admin/catupgrd.sql
Log file directory = [/oradb/app/oracle/scripts/logs/12cR2]
catcon: ALL catcon-related output will be written to [/oradb/app/oracle/scripts/
catcon: See [/oradb/app/oracle/scripts/logs/12cR2/catupgrd*.log] files for outpu
catcon: See [/oradb/app/oracle/scripts/logs/12cR2/catupgrd_*.lst] files for spoo
Number of Cpus = 1
Database Name = ORCL_PROD102
DataBase Version = 11.2.0.4.0
Parallel SQL Process Count = 4
Components in [ORCL_PROD102]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO
Not Installed [DV MGW ODM OLS RAC WK]
------------------------------------------------------
Phases [0-115] Start Time:[2018_07_21 04:07:28]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [ORCL_PROD102] Files:1 Time: 142s
*************** Catalog Core SQL ***************
Serial Phase #:1 [ORCL_PROD102] Files:5 Time: 44s
Restart Phase #:2 [ORCL_PROD102] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [ORCL_PROD102] Files:19 Time: 22s
Restart Phase #:4 [ORCL_PROD102] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [ORCL_PROD102] Files:6 Time: 16s
***************** Catproc Start ****************
Serial Phase #:6 [ORCL_PROD102] Files:1 Time: 13s
***************** Catproc Types ****************
Serial Phase #:7 [ORCL_PROD102] Files:2 Time: 12s
Restart Phase #:8 [ORCL_PROD102] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [ORCL_PROD102] Files:69 Time: 48s
Restart Phase #:10 [ORCL_PROD102] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [ORCL_PROD102] Files:1 Time: 42s
Restart Phase #:12 [ORCL_PROD102] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [ORCL_PROD102] Files:97 Time: 21s
Restart Phase #:14 [ORCL_PROD102] Files:1 Time: 0s
Parallel Phase #:15 [ORCL_PROD102] Files:118 Time: 24s
Restart Phase #:16 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:17 [ORCL_PROD102] Files:13 Time: 4s
Restart Phase #:18 [ORCL_PROD102] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [ORCL_PROD102] Files:33 Time: 34s
Restart Phase #:20 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:21 [ORCL_PROD102] Files:3 Time: 9s
Restart Phase #:22 [ORCL_PROD102] Files:1 Time: 0s
Parallel Phase #:23 [ORCL_PROD102] Files:24 Time: 154s
Restart Phase #:24 [ORCL_PROD102] Files:1 Time: 1s
Parallel Phase #:25 [ORCL_PROD102] Files:11 Time: 50s
Restart Phase #:26 [ORCL_PROD102] Files:1 Time: 1s
Serial Phase #:27 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:28 [ORCL_PROD102] Files:3 Time: 3s
Serial Phase #:29 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:30 [ORCL_PROD102] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:32 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:34 [ORCL_PROD102] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [ORCL_PROD102] Files:283 Time: 30s
Serial Phase #:36 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:37 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:38 [ORCL_PROD102] Files:1 Time: 4s
Restart Phase #:39 [ORCL_PROD102] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [ORCL_PROD102] Files:3 Time: 56s
Restart Phase #:41 [ORCL_PROD102] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [ORCL_PROD102] Files:13 Time: 79s
Restart Phase #:43 [ORCL_PROD102] Files:1 Time: 1s
Parallel Phase #:44 [ORCL_PROD102] Files:12 Time: 31s
Restart Phase #:45 [ORCL_PROD102] Files:1 Time: 0s
Parallel Phase #:46 [ORCL_PROD102] Files:2 Time: 1s
Restart Phase #:47 [ORCL_PROD102] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [ORCL_PROD102] Files:1 Time: 7s
Restart Phase #:49 [ORCL_PROD102] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [ORCL_PROD102] Files:1 Time: 25s
************ Upgrade Component Start ***********
Serial Phase #:51 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:52 [ORCL_PROD102] Files:1 Time: 0s
**************** Upgrading Java ****************
Serial Phase #:53 [ORCL_PROD102] Files:1 Time: 342s
Restart Phase #:54 [ORCL_PROD102] Files:1 Time: 0s
***************** Upgrading XDK ****************
Serial Phase #:55 [ORCL_PROD102] Files:1 Time: 38s
Restart Phase #:56 [ORCL_PROD102] Files:1 Time: 1s
********* Upgrading APS,OLS,DV,CONTEXT *********
Serial Phase #:57 [ORCL_PROD102] Files:1 Time: 76s
***************** Upgrading XDB ****************
Restart Phase #:58 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:60 [ORCL_PROD102] Files:3 Time: 35s
Serial Phase #:61 [ORCL_PROD102] Files:3 Time: 6s
Parallel Phase #:62 [ORCL_PROD102] Files:9 Time: 3s
Parallel Phase #:63 [ORCL_PROD102] Files:24 Time: 4s
Serial Phase #:64 [ORCL_PROD102] Files:4 Time: 6s
Serial Phase #:65 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:66 [ORCL_PROD102] Files:30 Time: 4s
Serial Phase #:67 [ORCL_PROD102] Files:1 Time: 0s
Parallel Phase #:68 [ORCL_PROD102] Files:6 Time: 3s
Serial Phase #:69 [ORCL_PROD102] Files:2 Time: 20s
Serial Phase #:70 [ORCL_PROD102] Files:3 Time: 76s
Restart Phase #:71 [ORCL_PROD102] Files:1 Time: 1s
********* Upgrading CATJAVA,OWM,MGW,RAC ********
Serial Phase #:72 [ORCL_PROD102] Files:1 Time: 86s
**************** Upgrading ORDIM ***************
Restart Phase #:73 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:75 [ORCL_PROD102] Files:1 Time: 1s
Parallel Phase #:76 [ORCL_PROD102] Files:2 Time: 75s
Serial Phase #:77 [ORCL_PROD102] Files:1 Time: 59s
Restart Phase #:78 [ORCL_PROD102] Files:1 Time: 0s
Parallel Phase #:79 [ORCL_PROD102] Files:2 Time: 17s
Serial Phase #:80 [ORCL_PROD102] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:81 [ORCL_PROD102] Files:1 Time: 1s
Serial Phase #:83 [ORCL_PROD102] Files:1 Time: 81s
Serial Phase #:84 [ORCL_PROD102] Files:1 Time: 2s
Restart Phase #:85 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:86 [ORCL_PROD102] Files:1 Time: 28s
Restart Phase #:87 [ORCL_PROD102] Files:1 Time: 1s
Parallel Phase #:88 [ORCL_PROD102] Files:3 Time: 135s
Restart Phase #:89 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:90 [ORCL_PROD102] Files:1 Time: 4s
Restart Phase #:91 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:92 [ORCL_PROD102] Files:1 Time: 2s
Restart Phase #:93 [ORCL_PROD102] Files:1 Time: 1s
Parallel Phase #:94 [ORCL_PROD102] Files:4 Time: 89s
Restart Phase #:95 [ORCL_PROD102] Files:1 Time: 1s
Serial Phase #:96 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:97 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:98 [ORCL_PROD102] Files:2 Time: 40s
Restart Phase #:99 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:100 [ORCL_PROD102] Files:1 Time: 0s
Restart Phase #:101 [ORCL_PROD102] Files:1 Time: 0s
*********** Upgrading Misc. ODM, OLAP **********
Serial Phase #:102 [ORCL_PROD102] Files:1 Time: 36s
**************** Upgrading APEX ****************
Restart Phase #:103 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:104 [ORCL_PROD102] Files:1 Time: 581s
Restart Phase #:105 [ORCL_PROD102] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:106 [ORCL_PROD102] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:107 [ORCL_PROD102] Files:1 Time: 166s
********** End PDB Application Upgrade *********
Serial Phase #:108 [ORCL_PROD102] Files:1 Time: 0s
******************* Migration ******************
Serial Phase #:109 [ORCL_PROD102] Files:1 Time: 66s
Serial Phase #:110 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:111 [ORCL_PROD102] Files:1 Time: 62s
***************** Post Upgrade *****************
Serial Phase #:112 [ORCL_PROD102] Files:1 Time: 334s
**************** Summary report ****************
Serial Phase #:113 [ORCL_PROD102] Files:1 Time: 2s
Serial Phase #:114 [ORCL_PROD102] Files:1 Time: 0s
Serial Phase #:115 [ORCL_PROD102] Files:1 Time: 34s
------------------------------------------------------
Phases [0-115] End Time:[2018_07_21 05:04:04]
------------------------------------------------------
Grand Total Time: 3402s
LOG FILES: (/oradb/app/oracle/scripts/logs/12cR2/catupgrd*.log)
Upgrade Summary Report Located in:
/oradb/app/oracle/scripts/logs/12cR2/upg_summary.log
Grand Total Upgrade Time: [0d:0h:56m:42s]
Once the upgrade is completed, check the log files from the log directory. Then update the timezone by Doc ID 1585343.1 and run the postupgrade_fixups.sql

Step 4:-Once the Upgrade is completed start the database and listener and check the upgraded database

$ lsnrctl start
$ sqlplus ‘/ as sysdba’
SQL> STARTUP
SQL> col COMP_ID format A10
SQL> col COMP_NAME format A30
SQL> col VERSION format A10
SQL> col STATUS format A15
SQL> SELECT SUBSTR(COMP_ID,1,15) COMP_ID,
SUBSTR(COMP_NAME,1,30) COMP_NAME,
SUBSTR(VERSION,1,10) VERSION,STATUS
FROM DBA_REGISTRY
/

COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------ ---------- ---------------
CATALOG Oracle Database Catalog Views 12.2.0.1.0 UPGRADED
CATPROC Oracle Database Packages and T 12.2.0.1.0 UPGRADED
JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED
XML Oracle XDK 12.2.0.1.0 UPGRADED
CATJAVA Oracle Database Java Packages 12.2.0.1.0 UPGRADED
APS OLAP Analytic Workspace 12.2.0.1.0 UPGRADED
OWM Oracle Workspace Manager 12.2.0.1.0 UPGRADED
CONTEXT Oracle Text 12.2.0.1.0 UPGRADED
XDB Oracle XML Database 12.2.0.1.0 UPGRADED
ORDIM Oracle Multimedia 12.2.0.1.0 UPGRADED
SDO Spatial 12.2.0.1.0 UPGRADED
XOQ Oracle OLAP API 12.2.0.1.0 UPGRADED
APEX Oracle Application Express 5.0.4.00.1 UPGRADED

Step 5:-Start the log apply process at Logical standby which is now Oracle 12c (12.2.0.1.0) version. Primary still in Oracle 11g (11.2.0.4.0) version

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

SWITCHOVER FROM LOGICAL STANDBY TO PRIMARY:-

Step 1:-Switchover the roles. Now primary database will become Oracle 11g (11.2.0.4.0) Logical standby and other side Oracle 12c (12.2.0.1.0) will be as primary database

1) Execute the below query at primary(Oracle 11.2.0.4):

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

2) Execute the below query at standby(Oracle 12.2.0.1):

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

3) Now the Primary database become as Standby database:

SQL> SELECT DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;

DB Unique Name Database Role
———————— —————-
ORCL_PROD101 LOGICAL STANDBY

4) Standby database become Primary database:

SQL> SELECT DB_UNIQUE_NAME,DATABASE_ROLE FROM V$DATABASE;

DB_UNIQUE_NAME DATABASE_ROLE
—————————— —————-
ORCL_PROD102 PRIMARY

Step 2:-We cannot directly convert Logical standby database to physical standby database, hence flashback to guarantee restore point which was created prior to upgrade

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE;

Flashback complete.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

Now the current logical standby database Oracle 11g (11.2.0.4.0) becomes physical standby database. i.e Logical to physical conversion:-

Step 1:-Shutdown database and start Physical standby database with Oracle 12c (12.2.0.1.0) binary

SQL> SHUTDOWN IMMEDIATE

$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 21-JUL-2018 05:34:56

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod101.oracledbwr.com)(PORT=1624)))
The command completed successfully

Step 2:-Copy listener.ora and tnsnames.ora to 12c ORACLE_HOME and edit listener.ora to 12c ORACLE_HOME. Copy pfile and password file to Oracle 12c (12.2.0.1.0) home

$ export ORACLE_HOME=/oradb/app/oracle/product/12.2.0.1/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_SID=ORCL
$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 21-JUL-2018 05:39:46
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /oradb/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Log messages written to /oradb/app/oracle/diag/tnslsnr/prod101/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod101.oracledbwr.com)(PORT=1624)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod101.oracledbwr.com)(PORT=1624)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 21-JUL-2018 05:39:46
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /oradb/app/oracle/diag/tnslsnr/prod101/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod101.oracledbwr.com)(PORT=1624)))
Services Summary...
Service "ORCL_PROD101" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

$ sqlplus ‘/ as sysdba’
SQL> STARTUP MOUNT

SQL> SELECT DATABASE_ROLE FROM V$DATABASE;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

ENABLE LOG SHIPPING:

Step 3:-Execute the below statement to apply all the archived log in current standby which are generated in current primary during the upgrade

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE THROUGH NEXT SWITCHOVER DISCONNECT;

Database altered.

$ tail -f alert_ORCL.log

Attempt to start background Managed Standby Recovery process (ORCL)
Starting background process MRP0
2018-07-21T05:46:17.785256+05:30
MRP0 started with pid=46, OS id=41380
2018-07-21T05:46:17.788222+05:30
MRP0: Background Managed Standby Recovery process started (ORCL)
2018-07-21T05:46:22.822202+05:30
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 1079326
2018-07-21T05:46:23.303437+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_15_981941460.arc
2018-07-21T05:46:23.667888+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_16_981941460.arc
2018-07-21T05:46:23.903688+05:30
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE THROUGH NEXT SWITCHOVER DISCONNECT
2018-07-21T05:46:24.209195+05:30
Resize operation completed for file# 1, old size 757760K, new size 768000K
2018-07-21T05:46:24.667692+05:30
Resize operation completed for file# 1, old size 768000K, new size 778240K
2018-07-21T05:46:24.947548+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_17_981941460.arc
2018-07-21T05:46:25.354518+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_1_982035897.arc
2018-07-21T05:46:26.299705+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_2_982035897.arc
2018-07-21T05:46:29.570245+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_3_982035897.arc
2018-07-21T05:46:31.737226+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_4_982035897.arc
2018-07-21T05:46:36.087151+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_5_982035897.arc
2018-07-21T05:46:37.658848+05:30

In Current Primary:-

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradb/app/oracle/archive/orcl_prod102
Oldest online log sequence 133
Next log sequence to archive 135
Current log sequence 135

In Current Standby:-

$ tail -20f alert_ORCL.log

Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_130_982035897.arc
2018-07-21T06:02:35.974947+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_131_982035897.arc
2018-07-21T06:02:37.840241+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_132_982035897.arc
2018-07-21T06:02:38.248442+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_133_982035897.arc
2018-07-21T06:02:43.617234+05:30
Resize operation completed for file# 2, old size 962560K, new size 972800K
2018-07-21T06:02:43.880337+05:30
Media Recovery Log /oradb/app/oracle/archive/orcl_prod101/orclp101_1_134_982035897.arc
2018-07-21T06:02:53.665249+05:30
Media Recovery Waiting for thread 1 sequence 135 (in transit)
2018-07-21T06:02:53.677109+05:30
Recovery of Online Redo Log: Thread 1 Group 4 Seq 135 Reading mem 0
Mem# 0: /oradb/app/oracle/oradata/ORCL_PROD101/redo04.log

Step 4:-Check the flashback restore point and drop it

SQL> select GUARANTEE_FLASHBACK_DATABASE,NAME from v$restore_point;
GUARANTEE_FLASHBACK_DATABASE Name
—————————— ————————
YES BEFORE_UPGRADE

SQL> select flashback_On from v$database;
FLASHBACK_ON
——————
YES

SQL> alter database flashback off;
Database altered.

SQL> select flashback_On from v$database;
FLASHBACK_ON
——————
RESTORE POINT ONLY

SQL> drop restore point BEFORE_UPGRADE;
Restore point dropped.

SQL> select flashback_On from v$database;
FLASHBACK_ON
——————
NO

SQL> ALTER SYSTEM SET COMPATIBLE = ‘12.2.0.1.0’ SCOPE=SPFILE;
System altered.

SQL> ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
Session altered.

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
———- ——————– ——————– ———
132 21-JUL-2018 05:25:25 21-JUL-2018 05:25:28 YES
133 21-JUL-2018 05:25:28 21-JUL-2018 05:40:51 YES
134 21-JUL-2018 05:40:51 21-JUL-2018 06:01:46 YES
135 21-JUL-2018 06:01:46 21-JUL-2018 06:11:50 NO

CURRENT PRIMARY DATABASE:-

Step 1:-Convert primary database to physical standby database and shutdown the database. It can be used in future

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.

CURRENT STANDBY DATABASE:-

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.

Step 2:-Now open the database

SQL> ALTER DATABASE OPEN;
Database altered.

Step 3:-Check the components have been upgraded to 12c

SQL> col COMP_ID format A10
SQL> col COMP_NAME format A30
SQL> col VERSION format A10
SQL> col STATUS format A15
SQL> SELECT SUBSTR(COMP_ID,1,15) COMP_ID,
SUBSTR(COMP_NAME,1,30) COMP_NAME,
SUBSTR(VERSION,1,10) VERSION,STATUS
FROM DBA_REGISTRY
/

COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------ ---------- ---------------
CATALOG Oracle Database Catalog Views 12.2.0.1.0 UPGRADED
CATPROC Oracle Database Packages and T 12.2.0.1.0 UPGRADED
JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED
XML Oracle XDK 12.2.0.1.0 UPGRADED
CATJAVA Oracle Database Java Packages 12.2.0.1.0 UPGRADED
APS OLAP Analytic Workspace 12.2.0.1.0 UPGRADED
OWM Oracle Workspace Manager 12.2.0.1.0 UPGRADED
CONTEXT Oracle Text 12.2.0.1.0 UPGRADED
XDB Oracle XML Database 12.2.0.1.0 UPGRADED
ORDIM Oracle Multimedia 12.2.0.1.0 UPGRADED
SDO Spatial 12.2.0.1.0 UPGRADED
XOQ Oracle OLAP API 12.2.0.1.0 UPGRADED
APEX Oracle Application Express 5.0.4.00.1 UPGRADED

Done…!!! We have successfully upgrade the database from 11g to 12c.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Step by Step Configure Oracle 12c Data Guard Far Sync Standby Database

Description:-

  • In this article we are going to see Step by Step Configure Oracle 12c Data Guard Far Sync Standby Database
  • An Oracle Data Guard far sync instance is a remote Oracle Data Guard destination that accepts redo from the primary database and then ships that redo to other members of the Oracle Data Guard configuration.
  • A far sync instance manages a control file, receives redo into standby redo logs (SRLs), and archives those SRLs to local archived redo logs, but that is where the similarity with standbys ends. A far sync instance does not have user data files, cannot be opened for access, cannot run redo apply, and can never function in the primary role or be converted to any type of standby database.
  • Far sync instances are part of the Oracle Active Data Guard Far Sync feature, which requires an Oracle Active Data Guard license.
  • Creating a far sync instance close to the primary has the benefit of minimizing impact on commit response times to an acceptable threshold (due to the smaller network latency between primary and far sync instance) while allowing for higher data protection guarantees — if the primary were to fail, and assuming the far sync instance was synchronized at the time of the failure, the far sync instance and the terminal standby would coordinate a final redo shipment from the far sync instance to the standby to ship any redo not yet available to the standby and then perform a zero-data-loss failover.
  • Summary:-
    • A light-weight/remote standby instance
    • It contains instance, standby controlfile and set of redo logs only Not datafile
    • Far sync standby database consumes minimal resources over the server (CPU, Memory, I/O etc)
    • Overcome network latency issues while shipping redo synchronously
      Guarantees zero data loss capabilities
    • Keep it close to the primary site, 40-150 miles
    • Far sync instance doesn’t have any physical structure
    • Multiple Far sync instance can be configured for active/passive purpose
    • Supports a maximum of 29 remote standby databases

Far Sync Standby Architecture:-

High Level Steps:-

  1. Adding TNS Entry and listener
  2. Creating the Standby Database
  3. create Far Sync Instance
  4. Adding Standby Logfiles in all the Databases
  5. Creating our Data Guard configuration with far sync instance

Let’s start the demo:-

Environment Details:-

Adding TNS entry (check all the nodes)

[oracle@prod1 admin]$ tnsping oradbwrp

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:25
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwr)))
OK (10 msec)

[oracle@prod1 admin]$ tnsping oradbwrs

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:28
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.26)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwrs)))
OK (10 msec)

[oracle@prod1 admin]$ tnsping oradbwrf

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:31
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwrf)))
OK (10 msec)

Adding  Listener (check all the nodes) Mark Red colour (DG Broker) 

[oracle@prod1 dbs]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-JUL-2018 04:18:57
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/prod1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 19-JUL-2018 04:18:58
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/prod1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradbwr" has 1 instance(s).
Instance "oradbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwr_DGMGRL" has 1 instance(s).
Instance "oradbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrf" has 1 instance(s).
Instance "oradbwrf", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrf_DGMGRL" has 1 instance(s).
Instance "oradbwrf", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrs" has 1 instance(s).
Instance "oradbwrs", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrs_DGMGRL" has 1 instance(s).
Instance "oradbwrs", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Enabling archivelog & flashback

[oracle@prod1 ~]$ export ORACLE_SID=oradbwr
[oracle@prod1 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 21:27:26 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> startup mount
ORACLE instance started.

Total System Global Area 935329792 bytes
Fixed Size 8627344 bytes
Variable Size 343935856 bytes
Database Buffers 578813952 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database force logging;
Database altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 935329792 bytes
Fixed Size 8627344 bytes
Variable Size 343935856 bytes
Database Buffers 578813952 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/oradbwr/redo04.log' size 50m;
Database altered.

SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/oradbwr/redo05.log' size 50m;
Database altered.

SQL>alter database add standby logfile group 6 '/u01/app/oracle/oradata/oradbwr/redo06.log' size 50m; 
Database altered.

Changing parameters in primary database

SQL> ALTER SYSTEM SET db_unique_name='oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_config='dg_config=(oradbwr,oradbwrs)' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2='service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrs' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET remote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET fal_server='oradbwrs' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET fal_client='oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_format='oradbwr_%t_%s_%r.arc' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/oradbwrs','/u01/app/oracle/oradata/oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/oradbwrs','/u01/app/oracle/oradata/oradbwr' SCOPE=SPFILE;
System altered.

Standby database side

Step:1 Password file creation

copy the remote login password file (orapworadbwr) from the primary database server to the $ORACLE_HOME/dbs directory on the
standby database server, renaming it to orapworadbwrs.

Step:2 Changing parameters in standby database

In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstand.ora
Containing a single parameter: DB_NAME=oradbwrs

Step:3 Create directory Structure in Standby database

cd $ORACLE_BASE/admin/
mkdir oradbwrs
cd oradbwrs
mkdir adump pfile dpdump
mkdir -p /u01/app/oracle/oradata/oradbwrs

Step:4 start the standby database using pfile

startup nomount pfile=$ORACLE_HOME/dbs/initoradbwrs.ora

Step:5 connect to the rman

[oracle@prod3 ~]$ rman target sys/oracle@oradbwrp auxiliary sys/oracle@oradbwrs

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jul 18 23:34:41 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADBWR (DBID=1408230468)
connected to auxiliary database: ORADBWR (not mounted)

RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'oradbwr','oradbwrs'
set db_name='oradbwr'
set db_unique_name='oradbwrs' 
set db_file_name_convert='/u01/app/oracle/oradata/oradbwr','/u01/app/oracle/oradata/oradbwrs'
set log_file_name_convert='/u01/app/oracle/oradata/oradbwr','/u01/app/oracle/oradata/oradbwrs'
set control_files='/u01/app/oracle/oradata/oradbwrs/standby1.ctl'
set log_archive_max_processes='5'
set fal_client='oradbwrs'
set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/oradbwrs'
set db_recovery_file_dest_size='8000m'
set fal_server='oradbwr'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(oradbwrs,oradbwr)'
set compatible='12.2.0.1.0'
set sga_target='1500m'
nofilenamecheck;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=75 device type=DISK
allocated channel: p2
channel p2: SID=76 device type=DISK
allocated channel: p3
channel p3: SID=71 device type=DISK
allocated channel: p4
channel p4: SID=63 device type=DISK
allocated channel: s1
channel s1: SID=36 device type=DISK
Starting Duplicate Db at 18-JUL-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.2.0.1/db_1/dbs/orapworadbwr' auxiliary format 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/orapworadbwrs' targetfile 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwr.ora' auxiliary format 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora''";
}
executing Memory Script
Starting backup at 18-JUL-18
Finished backup at 18-JUL-18
sql statement: alter system set spfile= ''/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest = 
''/u01/app/oracle/admin/oradbwrs/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers = 
''(PROTOCOL=TCP) (SERVICE=oradbwrsXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 = 
''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 = 
''service=oradbwrs async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrss'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_format = 
''oradbwrs_%t_%s_%r.arc'' comment=
'''' scope=spfile";
sql clone "alter system set db_name = 
''oradbwr'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name = 
''oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert = 
''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert = 
''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set control_files = 
''/u01/app/oracle/oradata/oradbwrs/standby1.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes = 
5 comment=
'''' scope=spfile";
sql clone "alter system set fal_client = 
''oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest = 
''/u01/app/oracle/fast_recovery_area/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest_size = 
8000m comment=
'''' scope=spfile";
sql clone "alter system set fal_server = 
''oradbwr'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management = 
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config = 
''dg_config=(oradbwrs,oradbwr)'' comment=
'''' scope=spfile";
sql clone "alter system set compatible = 
''12.2.0.1.0'' comment=
'''' scope=spfile";
sql clone "alter system set sga_target = 
1500m comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/oradbwrs/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=oradbwrsXDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrss'' comment= '''' scope=spfile
sql statement: alter system set log_archive_format = ''oradbwrs_%t_%s_%r.arc'' comment= '''' scope=spfile
sql statement: alter system set db_name = ''oradbwr'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/oradbwrs/standby1.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''/u01/app/oracle/fast_recovery_area/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest_size = 8000m comment= '''' scope=spfile
sql statement: alter system set fal_server = ''oradbwr'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(oradbwrs,oradbwr)'' comment= '''' scope=spfile
sql statement: alter system set compatible = ''12.2.0.1.0'' comment= '''' scope=spfile
sql statement: alter system set sga_target = 1500m comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1577058304 bytes
Fixed Size 8621136 bytes
Variable Size 503317424 bytes
Database Buffers 1056964608 bytes
Redo Buffers 8155136 bytes
allocated channel: s1
channel s1: SID=34 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/oradbwrs/standby1.ctl';
}
executing Memory Script
Starting backup at 18-JUL-18
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/12.2.0.1/db_1/dbs/snapcf_oradbwr.f tag=TAG20180718T233622
channel p1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-JUL-18
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to 
"/u01/app/oracle/oradata/oradbwrs/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to 
"/u01/app/oracle/oradata/oradbwrs/system01.dbf";
set newname for datafile 3 to 
"/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf";
set newname for datafile 4 to 
"/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf";
set newname for datafile 7 to 
"/u01/app/oracle/oradata/oradbwrs/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/system01.dbf" datafile 
3 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf" datafile 
4 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf" datafile 
7 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/oradbwrs/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-JUL-18
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/oradbwr/system01.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/oradbwr/sysaux01.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/oradbwr/undotbs01.dbf
channel p4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/oradbwr/users01.dbf
output file name=/u01/app/oracle/oradata/oradbwrs/users01.dbf tag=TAG20180718T233628
channel p4: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf tag=TAG20180718T233628
channel p3: datafile copy complete, elapsed time: 00:00:07
output file name=/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf tag=TAG20180718T233628
channel p2: datafile copy complete, elapsed time: 00:01:15
output file name=/u01/app/oracle/oradata/oradbwrs/system01.dbf tag=TAG20180718T233628
channel p1: datafile copy complete, elapsed time: 00:01:25
Finished backup at 18-JUL-18
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/users01.dbf
Finished Duplicate Db at 18-JUL-18
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1

Step6 :- Post check status for Standby database,

SQL> select name,database_role,open_mode from v$database;

NAME               DATABASE_ROLE                           OPEN_MODE
——— —————- ——————–
ORADBWR             PHYSICAL STANDBY                        MOUNTED

Step 7:- Enable the recovery:

SQL> alter database recover managed standby database disconnect from session;

Step 8:- Check the Standby database sync status with primary:

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1                 24                   24                 0

Deploying Far Sync instance

Step1:-We need to create a controlfile in primary database that will be used to create far sync instance

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/home/oracle/stbyfarsync.ctl';
Database altered.

Step2:-Copy the far sync controlfile & passwordfile

Copy the far sync controlfile & passwordfile
[oracle@prod1 ~]$ scp stbyfarsync.ctl oracle@192.168.1.25:/u01/app/oracle/oradata/oradbwrf
oracle@192.168.1.25's password: 
stbyfarsync.ctl 100% 10MB 10.1MB/s 00:00 
[oracle@prod1 ~]$ cd $ORACLE_HOME/dbs
[oracle@prod1 dbs]$ scp orapworadbwr oracle@192.168.1.25:$ORACLE_HOME/dbs
oracle@192.168.1.25's password: 
orapworadbwr

step3:-Farsync instance Pfile

audit_file_dest='/u01/app/oracle/oradbwrf/adump'
audit_trail='db'
compatible='12.2.0.1.0'
control_files='/u01/app/oracle/oradata/oradbwrf/stbyfarsync.ctl'
db_block_size=8192
db_domain=''
db_name='oradbwr'
db_unique_name='oradbwrf'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/oradbwrf'
db_recovery_file_dest_size=4800m
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=oradbwrfXDB)'
log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrf'
log_archive_dest_2='service=oradbwrs ASYNC valid_for=(standby_logfiles,standby_role) db_unique_name=oradbwrs'
log_archive_config='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)'
open_cursors=300
pga_aggregate_target=300m
processes=300
remote_login_passwordfile='EXCLUSIVE'
sga_target=1300m
undo_tablespace='UNDOTBS1'
fal_server='oradbwr'
db_file_name_convert='/u01/app/oracle/oradata/oradbwr/','/u01/app/oracle/oradata/oradbwrf/'
log_file_name_convert='/u01/app/oracle/oradata/oradbwr/','/u01/app/oracle/oradata/oradbwrf/'

Step4:- startup the Far sync instance and check

[oracle@prod2 ~]$ sqlplus ‘/as sysdba’;

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 19 03:54:43 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1375731712 bytes
Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

SQL> alter database mount;
Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
FAR SYNC

SQL> select DB_UNIQUE_NAME,DEST_ROLE from V$DATAGUARD_CONFIG;

DB_UNIQUE_NAME                    DEST_ROLE
------------------------------ -----------------
oradbwrf                        FAR SYNC INSTANCE
oradbwrs                        PHYSICAL STANDBY
oradbwr                         PRIMARY DATABASE

Data Guard configuration with far sync instance

Parameters change 

Primary:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbwr' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=oradbwrf SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradbwrf' scope=both;

Far sync Standby:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ordbwrf' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ordbwrs ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=ordbwrs' scope=both;

Standby:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbwrs' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=oradbwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradbwr' scope=both;
[oracle@prod1 admin]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Thu Jul 19 06:26:35 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@oradbwrp
Connected to "oradbwr"
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION 'chennai' AS PRIMARY DATABASE IS 'oradbwr' CONNECT IDENTIFIER IS 'oradbwr';
Configuration "chennai" created with primary database "oradbwr"
DGMGRL> ADD DATABASE 'oradbwrs' AS CONNECT IDENTIFIER IS 'oradbwrs' maintained as physical;
Database "oradbwrs" added
DGMGRL> ADD FAR_SYNC 'oradbwrf' AS CONNECT IDENTIFIER IS 'oradbwrf';
far sync instance "oradbwrf" added
DGMGRL> show configuration;

Configuration - chennai

Protection Mode: MaxAvailability
Members:
oradbwr - Primary database
oradbwrs - Physical standby database 
oradbwrf - Far sync instance 
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

Check the Standby database sync status with primary:

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied     Difference
---------- ---------------------- --------------------- ----------
1                 37                     37                0

Successfully configured Far sync standby database.

Reference:-

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sbydb/creating-oracle-data-guard-far-sync-instance.html#GUID-8AD7FBA2-42B0-46CF-852B-1AF0CB4A36E8

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Oracle 18c Datapump-Compression Parameter

Oracle 18c Datapump-Compression Parameter

Description:-

  • In this article we are going to see the Oracle 18c Datapump-Compression Parameter

Default: METADATA_ONLY

Syntax and Description

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}
ALL enables compression for the entire export operation. The ALL option requires that the Oracle Advanced Compression option be enabled.
DATA_ONLY results in all data being written to the dump file in compressed format. The DATA_ONLY option requires that the Oracle Advanced Compression option be enabled.
METADATA_ONLY results in all metadata being written to the dump file in compressed format. This is the default.
NONE disables compression for the entire export operation.

Let’s start the Demo:-

Demo:-METADATA_ONLY

[oracle@testdb datapump]$ expdp hari/hari@pdb1 DIRECTORY=test_dir DUMPFILE=demo.dmp compression=metadata_only

Export: Release 18.0.0.0.0 - Production on Sun Jul 15 03:27:41 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HARI"."SYS_EXPORT_SCHEMA_02": hari/********@pdb1 DIRECTORY=test_dir DUMPFILE=demo.dmp compression=metadata_only
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "HARI"."CHENNAI" 17.08 KB 107 rows
. . exported "HARI"."DEPT" 17.08 KB 107 rows
. . exported "HARI"."EMP" 17.08 KB 107 rows
. . exported "HARI"."EMP1" 17.08 KB 107 rows
. . exported "HARI"."EMP2" 17.08 KB 107 rows
. . exported "HARI"."HR" 17.08 KB 107 rows
. . exported "HARI"."INDIA" 17.08 KB 107 rows
. . exported "HARI"."SALES" 17.08 KB 107 rows
. . exported "HARI"."SAM" 8.539 KB 512 rows
. . exported "HARI"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows
Master table "HARI"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for HARI.SYS_EXPORT_SCHEMA_02 is:
/u01/app/oracle/datapump/demo.dmp
Job "HARI"."SYS_EXPORT_SCHEMA_02" successfully completed at Sun Jul 15 03:28:43 2018 elapsed 0 00:01:01

Demo:-ALL

[oracle@testdb datapump]$ expdp hari/hari@pdb1 DIRECTORY=test_dir DUMPFILE=demo2.dmp compression=all

Export: Release 18.0.0.0.0 - Production on Sun Jul 15 03:28:49 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HARI"."SYS_EXPORT_SCHEMA_02": hari/********@pdb1 DIRECTORY=test_dir DUMPFILE=demo2.dmp compression=all
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "HARI"."CHENNAI" 8.757 KB 107 rows
. . exported "HARI"."DEPT" 8.773 KB 107 rows
. . exported "HARI"."EMP" 8.781 KB 107 rows
. . exported "HARI"."EMP1" 8.765 KB 107 rows
. . exported "HARI"."EMP2" 8.773 KB 107 rows
. . exported "HARI"."HR" 8.781 KB 107 rows
. . exported "HARI"."INDIA" 8.742 KB 107 rows
. . exported "HARI"."SALES" 8.765 KB 107 rows
. . exported "HARI"."SAM" 4.765 KB 512 rows
. . exported "HARI"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows
Master table "HARI"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for HARI.SYS_EXPORT_SCHEMA_02 is:
/u01/app/oracle/datapump/demo2.dmp
Job "HARI"."SYS_EXPORT_SCHEMA_02" successfully completed at Sun Jul 15 03:29:35 2018 elapsed 0 00:00:45

Demo:-DATA_ONLY

[oracle@testdb datapump]$ expdp hari/hari@pdb1 DIRECTORY=test_dir DUMPFILE=demo3.dmp compression=data_only

Export: Release 18.0.0.0.0 - Production on Sun Jul 15 03:31:19 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HARI"."SYS_EXPORT_SCHEMA_02": hari/********@pdb1 DIRECTORY=test_dir DUMPFILE=demo3.dmp compression=data_only
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "HARI"."CHENNAI" 8.757 KB 107 rows
. . exported "HARI"."DEPT" 8.773 KB 107 rows
. . exported "HARI"."EMP" 8.781 KB 107 rows
. . exported "HARI"."EMP1" 8.765 KB 107 rows
. . exported "HARI"."EMP2" 8.773 KB 107 rows
. . exported "HARI"."HR" 8.781 KB 107 rows
. . exported "HARI"."INDIA" 8.742 KB 107 rows
. . exported "HARI"."SALES" 8.765 KB 107 rows
. . exported "HARI"."SAM" 4.765 KB 512 rows
. . exported "HARI"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows
Master table "HARI"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for HARI.SYS_EXPORT_SCHEMA_02 is:
/u01/app/oracle/datapump/demo3.dmp
Job "HARI"."SYS_EXPORT_SCHEMA_02" successfully completed at Sun Jul 15 03:31:51 2018 elapsed 0 00:00:31

Demo:-NONE

[oracle@testdb datapump]$ expdp hari/hari@pdb1 DIRECTORY=test_dir DUMPFILE=demo4.dmp compression=none

Export: Release 18.0.0.0.0 - Production on Sun Jul 15 03:33:25 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HARI"."SYS_EXPORT_SCHEMA_02": hari/********@pdb1 DIRECTORY=test_dir DUMPFILE=demo4.dmp compression=none
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "HARI"."CHENNAI" 17.08 KB 107 rows
. . exported "HARI"."DEPT" 17.08 KB 107 rows
. . exported "HARI"."EMP" 17.08 KB 107 rows
. . exported "HARI"."EMP1" 17.08 KB 107 rows
. . exported "HARI"."EMP2" 17.08 KB 107 rows
. . exported "HARI"."HR" 17.08 KB 107 rows
. . exported "HARI"."INDIA" 17.08 KB 107 rows
. . exported "HARI"."SALES" 17.08 KB 107 rows
. . exported "HARI"."SAM" 8.539 KB 512 rows
. . exported "HARI"."SYS_EXPORT_SCHEMA_01" 0 KB 0 rows
Master table "HARI"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for HARI.SYS_EXPORT_SCHEMA_02 is:
/u01/app/oracle/datapump/demo4.dmp
Job "HARI"."SYS_EXPORT_SCHEMA_02" successfully completed at Sun Jul 15 03:33:56 2018 elapsed 0 00:00:30

[oracle@testdb datapump]$ ls -lrt
total 2260
-rw-r—– 1 oracle oinstall 851968 Jul 15 03:28 demo.dmp
-rw-r—– 1 oracle oinstall 245760 Jul 15 03:29 demo2.dmp
-rw-r—– 1 oracle oinstall 294912 Jul 15 03:31 demo3.dmp
-rw-r—– 1 oracle oinstall 901120 Jul 15 03:33 demo4.dmp

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter Group:https://www.linkedin.com/groups/10387079                Twitter:   https://twitter.com/hariprasathdba