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