Jupyter, Python, Oracle and Docker Part 3

You can find the Python Jupyter Notebook used in this exercise here.

OracleDockerDatabaseCreate

Create Oracle Database in a Docker Container

These Python (3.6) scripts walk through the creation of a database and standby server. The reason I've done this rather than just take the default configuration is that this approach gives me a lot more control over the build and enables me to change specifc steps. If you are just wanting to get the Oracle Database running inside of Docker I strongly suggest that you use the docker files and guides in the Oracle Github repository. The approach documented below is very much for someone who is interested in a high level of control over the various steps in the installation and configuration of the Oracle Database. This current version is build on top of Oracle's Internal GiaaS docker image but will be extended to support the public dockers images as well. It aims to build an Active Data Guard model with maximum performance but can be trivially changed to support any of the required models.

It uses a mix of calls to the Docker Python API and Calls direct to the databases via cx_Oracle.

The following code section imports the needed modules to setup up our Docker container to create the Oracle Database. After that we get a Docker client handle which enables use to call the API against our local Docker environment.

In [1]:
import docker
import humanize
import os
import tarfile
from prettytable import PrettyTable
import cx_Oracle
from IPython.display import HTML, display
import keyring
from ipynb.fs.full.OracleDockerDatabaseFunctions import list_images,list_containers,copy_to,create_and_run_script,containter_exec,containter_root_exec,copy_string_to_file

client = docker.from_env(timeout=600)
In [2]:
list_images(client)
Tag id Size
dominicgiles:db18.0.0 cc75a47617 10.8 GB
linux_for_db18.0.0:latest b350079361 926.8 MB
oraclelinux:7-slim c3d8693881 117.3 MB
giaas.us.oracle.com:5001/dominic.giles/104213:db19000 808b7de5b5 7.8 GB
dbtools-docker.dockerhub-den.oraclecorp.com/oracle/sdw:18.4.0 af2d2f4838 1.8 GB
dbtools-docker.dockerhub-den.oraclecorp.com/oracle/database:18.3.0-ee c9eae81d87 8.4 GB

Configuration Parameters

The following section contains the parameters for setting the configuration of the install. The following parameters image_name,host_oradata,sb_host_oradata need to be changed, although sb_host_oradata is only important if you are planning on installing a standby database.

In [3]:
# The following parameters are specific to your install and almost certainly need to be changed
image_name = 'cc75a47617' # Taken from the id value above
host_oradata = '/Users/dgiles/Downloads/dockerdbs/oradataprimary' # The directory on the host where primary database will be persisted
sb_host_oradata = '/Users/dgiles/Downloads/dockerdbs/oradatastby' # The direcotry on the host where the standby database will be persisted
#
# The rest are fairly generic and can be changed if needed
oracle_version = '18.0.0'
db_name = 'ORCL'
stby_name = 'ORCL_STBY'
sys_password =  keyring.get_password('docker','sys') # I'm just using keyring to hide my password but you can set it to a simple sting i.e. 'mypassword'
pdb_name = 'soe'
p_host_name = 'oracleprimary'
sb_host_name = 'oraclestby'
total_memory = 2048

container_oradata = '/u01/app/oracle/oradata'
o_base = '/u01/app/oracle'
r_area = f'{o_base}/oradata/recovery_area'
o_area = f'{o_base}/oradata/'
a_area = f'{o_base}/admin/ORCL/adump'
o_home = f'{o_base}/product/{oracle_version}/dbhome_1'
t_admin = f'{o_base}/oradata/dbconfig'
log_size = 200

Create Primary Database

This code does the heavy lifting. It creates a container oracleprimary (unless you changed the paramter) running the Oracle Database. The containers 1521 port is mapped onto the the hosts 1521 port. This means that to connect from the host, via a tool like sqlplus, all you'd need to do is sqlplus soe/soe@//locahost/soe.

In [4]:
path = f'{o_home}/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin'

p_container = client.containers.create(image_name,
                                       command="/bin/bash",
                                       hostname=p_host_name,
                                       tty=True,
                                       stdin_open=True,
                                       auto_remove=False,
                                       name=p_host_name,
                                       shm_size='3G',
                                       # network_mode='host',
                                       ports={1521:1521,5500:5500},
                                       volumes={host_oradata: {'bind': container_oradata, 'mode': 'rw'}},
                                       environment={'PATH':path,'ORACLE_SID': db_name, 'ORACLE_BASE': o_base,'TNS_ADMIN': t_admin}
                                       )

p_container.start()
# Make all of the containers attributes available via Python Docker API
p_container.reload()

The next step uses DBCA and configures features like Automatic Memory Mangement, Oracle Managed Files and sets the size of the SGA and redo logs. It prints out the status of the creation during it's progression. NOTE : This step typically takes 10 to 12 minutes.

In [5]:
statement = f'''dbca -silent \
 -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname {db_name} -sid {db_name} -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword {sys_password} \
 -systemPassword {sys_password} \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName {pdb_name} \
 -pdbAdminPassword {sys_password} \
 -databaseType MULTIPURPOSE \
 -totalMemory {total_memory} \
 -memoryMgmtType AUTO_SGA \
 -recoveryAreaDestination "{r_area}" \
 -storageType FS \
 -useOMF true \
 -datafileDestination "{o_area}" \
 -redoLogFileSize {log_size} \
 -emConfiguration NONE \
 -ignorePreReqs\
 '''

containter_exec(p_container, statement)
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
53% complete

54% complete

Creating Pluggable Databases

58% complete

77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/ORCL.
Database Information:
Global Database Name:ORCL
System Identifier(SID):ORCL
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/ORCL/ORCL.log" for further details.

Create Primary Database's Listener

This step creates the database listener for the primary database. The tnsnames.ora will be over written in a later step if you choose to have a stand by configuration. NOTE : I could create a DNSMasq container or something similar and add container networking details to make the whole inter node communication simpler but it's a bit of an overkill and so we'll use IP addresses which are easily found.

In [6]:
p_ip_adress = p_container.attrs['NetworkSettings']['IPAddress']

p_listener = f'''LISTENER=
    (DESCRIPTION=
        (ADDRESS = (PROTOCOL=tcp)(HOST={p_ip_adress})(PORT=1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )    

SID_LIST_LISTENER=
    (SID_LIST=
        (SID_DESC=
            (GLOBAL_DBNAME={db_name}_DGMGRL)
            (ORACLE_HOME={o_home})
            (SID_NAME={db_name})
            (ENVS="TNS_ADMIN={t_admin}"Winking
        )
    ) 
'''
copy_string_to_file(p_listener, f'{t_admin}/listener.ora', p_container)

contents = '''NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)'''

copy_string_to_file(contents, f'{t_admin}/sqlnet.ora', p_container)

contents = f'''
ORCL = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = {p_ip_adress})(PORT=1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SID = {db_name}) 
    ) 
  ) 
'''
copy_string_to_file(contents, f'{t_admin}/tnsnames.ora', p_container)

And start the listener

In [7]:
containter_exec(p_container, 'lsnrctl start')
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 25-MAR-2019 17:05:35

Copyright (c) 1991, 2018, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/18.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /u01/app/oracle/oradata/dbconfig/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracleprimary/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.0.2)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.0.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                25-MAR-2019 17:05:35
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/oradata/dbconfig/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracleprimary/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.0.2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCL_DGMGRL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

At this stage you should have a fully functioning Oracle Database. In theory there's no need to go any further if thats all you want.

Create Stand By Container

This step creates another container to run the standby databases. It should be pretty much instant. NOTE : You'll only need to run the rest of the code from here if you need a standby database.

In [8]:
sb_container = client.containers.create(image_name,
                                       hostname=sb_host_name,
                                       command="/bin/bash",
                                       tty=True,
                                       stdin_open=True,
                                       auto_remove=False,
                                       name=sb_host_name,
                                       shm_size='3G',
                                       ports={1521:1522,5500:5501},
                                       volumes={sb_host_oradata: {'bind': container_oradata, 'mode': 'rw'}},
                                       environment={'PATH':path,'ORACLE_SID':db_name,'ORACLE_BASE':o_base,'TNS_ADMIN':t_admin}
                                       )
sb_container.start()
# Make all of the containers attributes available via Python Docker API
sb_container.reload()

Display the running containers.

In [9]:
list_containers(client)
Name Short id Status IP Address
oraclestby 90e777d67f running 172.17.0.3
oracleprimary 51fb562d84 running 172.17.0.2

Configure the Standby Database

Create some additional directories on the standby so they are consistent with the primary.

In [10]:
containter_exec(sb_container, f'mkdir -p {o_area}/{db_name}')
containter_exec(sb_container, f'mkdir -p {t_admin}')
containter_exec(sb_container, f'mkdir -p {r_area}/{db_name}')
containter_exec(sb_container, f'mkdir -p {a_area}')

Create Standby Database's Listener

Create the standby listenrs network configuration and then start the listener. NOTE : We'll be overwriting the primary databases tnsnames.ora file in this step.

In [11]:
sb_ip_adress = sb_container.attrs['NetworkSettings']['IPAddress']

contents = f'''
ORCL = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = {p_ip_adress})(PORT=1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SID = {db_name}) 
    ) 
  ) 

ORCL_STBY =  
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = {sb_ip_adress})(PORT=1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SID = {db_name}) 
    ) 
  ) 
'''

copy_string_to_file(contents, f'{t_admin}/tnsnames.ora', p_container)
copy_string_to_file(contents, f'{t_admin}/tnsnames.ora', sb_container)

sb_listener = f'''LISTENER=
    (DESCRIPTION=
        (ADDRESS = (PROTOCOL=tcp)(HOST={sb_ip_adress})(PORT =1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )    

SID_LIST_LISTENER=
    (SID_LIST=
        (SID_DESC=
            (GLOBAL_DBNAME={stby_name}_DGMGRL)
            (ORACLE_HOME={o_home})
            (SID_NAME={db_name})
            (ENVS="TNS_ADMIN={t_admin}"Winking
        )
    ) 
'''
copy_string_to_file(sb_listener, f'{t_admin}/listener.ora', sb_container)

contents = '''NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)'''

copy_string_to_file(contents, f'{t_admin}/sqlnet.ora', sb_container)

And start the listener

In [12]:
containter_exec(sb_container, 'lsnrctl start')
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 25-MAR-2019 17:08:51

Copyright (c) 1991, 2018, Oracle.  All rights reserved.
Starting /u01/app/oracle/product/18.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /u01/app/oracle/oradata/dbconfig/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oraclestby/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.0.3)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.0.3)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                25-MAR-2019 17:08:51
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/oradata/dbconfig/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oraclestby/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.0.3)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCL_STBY_DGMGRL" has 1 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Configure the servers for Data Guard

It might be necessary to pause for a few seconds before moving onto the next step to allow the database to register with the listener...

The next step is to connect to primary and standby servers and set various parameters and configuration to enable us to run Data Guard.

First check the status of archive logging on the primary.

In [13]:
connection = cx_Oracle.connect("sys",sys_password,f"//localhost:1521/{db_name}", mode=cx_Oracle.SYSDBA)
cursor = connection.cursor();
rs = cursor.execute("SELECT log_mode FROM v$database")
for row in rs:
    print(f"Database is in {row[0]} mode")
Database is in NOARCHIVELOG mode

By default it will be in no archivelog mode so we need to shut it down and enable archive log mode

In [14]:
contents = '''sqlplus / as sysdba << EOF
SET ECHO ON;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
EOF
'''

create_and_run_script(contents, '/tmp/set_archivelog.sql',  '/bin/bash /tmp/set_archivelog.sql', p_container)
/bin/bash /tmp/set_archivelog.sql
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 25 17:09:15 2019
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> SQL>
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
ORACLE instance started.

Total System Global Area 1610612704 bytes
Fixed Size		    8896480 bytes
Variable Size		  520093696 bytes
Database Buffers	 1073741824 bytes
Redo Buffers		    7880704 bytes
Database mounted.
SQL>
Database altered.

SQL>
Database altered.

SQL>
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

And check again

In [15]:
connection = cx_Oracle.connect("sys",sys_password,f"//localhost:1521/{db_name}", mode=cx_Oracle.SYSDBA)
cursor = connection.cursor();
rs = cursor.execute("SELECT log_mode FROM v$database")
for row in rs:
    print(f"Database is in {row[0]} mode")
Database is in ARCHIVELOG mode

And then force a log switch

In [16]:
cursor.execute("ALTER DATABASE FORCE LOGGING")
cursor.execute("ALTER SYSTEM SWITCH LOGFILE")

Add some Standby Logging Files

In [17]:
cursor.execute("ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M")
cursor.execute("ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M")
cursor.execute("ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M")
cursor.execute("ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M")

Enable Flashback and standby file management

In [18]:
cursor.execute("ALTER DATABASE FLASHBACK ON")
cursor.execute("ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO")

Start an instance

Create a temporary init.ora file to enable us to start an instance on the standby

In [19]:
contents = f"DB_NAME='{db_name}'\n"
copy_string_to_file(contents, f'/tmp/init{db_name}.ora', sb_container)

Create a password file on the standby with the same parameters as the primary

In [20]:
containter_exec(sb_container, f'orapwd file=$ORACLE_HOME/dbs/orapw{db_name} password={sys_password} entries=10 format=12')

And start up the standby instance

In [21]:
contents = f'''STARTUP NOMOUNT PFILE='/tmp/init{db_name}.ora';
EXIT;
'''
create_and_run_script(contents, '/tmp/start_db.sql', 'sqlplus / as sysdba @/tmp/start_db.sql', sb_container)
sqlplus / as sysdba @/tmp/start_db.sql
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 25 17:11:01 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area  268433000 bytes
Fixed Size		    8656488 bytes
Variable Size		  201326592 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    8118272 bytes
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Duplicate the Primary database to the Standby database

Duplicate the primary to the standby. For some reason the tnsnames isn't picked up on the primary/standby in the same location so an explicit connection string is needed.

In [22]:
contents = f'''rman TARGET sys/{sys_password}@{db_name} AUXILIARY sys/{sys_password}@{stby_name} << EOF
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='{stby_name}' COMMENT 'Is standby'
  NOFILENAMECHECK;
EOF
'''

create_and_run_script(contents, '/tmp/duplicate.sh', "/bin/bash /tmp/duplicate.sh", sb_container)
/bin/bash /tmp/duplicate.sh
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Mar 25 17:11:27 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1531636684)
connected to auxiliary database: ORCL (not mounted)

RMAN>
2>
3> 4>
5> 6>
7>

Starting Duplicate Db at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
current log archived

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format  '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/orapwORCL'   ;
restore clone from service  'ORCL' spfile to
'/u01/app/oracle/product/18.0.0/dbhome_1/dbs/spfileORCL.ora';
sql clone "alter system set spfile= ''/u01/app/oracle/product/18.0.0/dbhome_1/dbs/spfileORCL.ora''";
}
executing Memory Script

Starting backup at 25-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK
Finished backup at 25-MAR-19

Starting restore at 25-MAR-19
using channel ORA_AUX_DISK_1

...
...
...

released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/oradata/recovery_area/ORCL_STBY/archivelog/2019_03_25/o1_mf_1_6_g9l30xtn_.arc RECID=1 STAMP=1003857181
deleted archived log
archived log file name=/u01/app/oracle/oradata/recovery_area/ORCL_STBY/archivelog/2019_03_25/o1_mf_1_7_g9l30yxy_.arc RECID=2 STAMP=1003857182
Deleted 2 objects

Finished Duplicate Db at 25-MAR-19

RMAN>

Recovery Manager complete.

Start the Data Guard Broker

It's best practice to use Data Guard Broker and so we'll need to start it on both instances and then create a configuration.

In [23]:
cursor.execute("ALTER SYSTEM SET dg_broker_start=true")

sb_connection = cx_Oracle.connect("sys",sys_password,f"//localhost:1522/{stby_name}", mode=cx_Oracle.SYSDBA)
sb_cursor = sb_connection.cursor()
sb_cursor.execute("ALTER SYSTEM SET dg_broker_start=true")

Create a configuration

In [24]:
contents = f'''export TNS_ADMIN={t_admin};
dgmgrl sys/{sys_password}@{db_name} << EOF
SET ECHO ON;
CREATE CONFIGURATION orcl_stby_config AS PRIMARY DATABASE IS {db_name} CONNECT IDENTIFIER IS {db_name};
EXIT;
EOF
'''

create_and_run_script(contents, '/tmp/dgconfig.sh', "/bin/bash /tmp/dgconfig.sh", p_container)
/bin/bash /tmp/dgconfig.sh
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Mon Mar 25 17:13:53 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL>
DGMGRL> CREATE CONFIGURATION orcl_stby_config AS PRIMARY DATABASE IS ORCL CONNECT IDENTIFIER IS ORCL;
Configuration "orcl_stby_config" created with primary database "orcl"
DGMGRL> EXIT;

Add the standby

In [25]:
contents = f'''export TNS_ADMIN={t_admin};
dgmgrl sys/{sys_password}@{db_name} << EOF
SET ECHO ON;
ADD DATABASE {stby_name} AS CONNECT IDENTIFIER IS {stby_name} MAINTAINED AS PHYSICAL;
EXIT;
EOF
'''

create_and_run_script(contents, '/tmp/dgconfig2.sh', "/bin/bash /tmp/dgconfig2.sh", p_container)
/bin/bash /tmp/dgconfig2.sh
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Mon Mar 25 17:14:05 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
ADD DATABASE ORCL_STBY AS CONNECT IDENTIFIER IS ORCL_STBY
MAINTAINED AS PHYSICAL;
Database "orcl_stby" added
DGMGRL> EXIT;

Enable the configuration

In [26]:
contents = f'''export TNS_ADMIN={t_admin};
dgmgrl sys/{sys_password}@{db_name} << EOF
SET ECHO ON;
ENABLE CONFIGURATION;
EXIT;
EOF
'''

create_and_run_script(contents, '/tmp/dgconfig3.sh', "/bin/bash /tmp/dgconfig3.sh", p_container)
/bin/bash /tmp/dgconfig3.sh
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Mon Mar 25 17:14:17 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
ENABLE CONFIGURATION;
Enabled.
DGMGRL>
EXIT;

Display the configuration

In [27]:
contents = f'''export TNS_ADMIN={t_admin};
dgmgrl sys/{sys_password}@{db_name} << EOF
SET ECHO ON;
SHOW CONFIGURATION;
SHOW DATABASE {db_name};
SHOW DATABASE {stby_name};
EOF
'''

create_and_run_script(contents, '/tmp/dgconfig4.sh', "/bin/bash /tmp/dgconfig4.sh", p_container)
/bin/bash /tmp/dgconfig4.sh
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Mon Mar 25 17:14:40 2019
Version 18.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "ORCL"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
SHOW CONFIGURATION;

Configuration - orcl_stby_config
Protection Mode: MaxPerformance
Members:
orcl      - Primary database
  orcl_stby - Physical standby database
Warning: ORA-16854: apply lag could not be determined

Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 7 seconds ago)
DGMGRL>
SHOW DATABASE ORCL;
Database - orcl
Role:             PRIMARY
Intended State:   TRANSPORT-ON
Instance(s):
  ORCL
Database Status:
SUCCESS
DGMGRL>
SHOW DATABASE ORCL_STBY;
Database - orcl_stby
Role:             PHYSICAL STANDBY
Intended State:   APPLY-ON
Transport Lag:    0 seconds (computed 2 seconds ago)
Apply Lag:        (unknown)
Average Apply RateSadunknown)
Real Time Query:  OFF
Instance(s):
  ORCL


Database Warning(s):
ORA-16854: apply lag could not be determined

Database Status:
WARNING

DGMGRL>
DGMGRL>

Start the Standby in managed recovery

We now need to start the standby so it begins applying redo to keep it consistent with the primary.

In [28]:
contents='''sqlplus / as sysdba << EOF
SET ECHO ON;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
EOF
'''

create_and_run_script(contents, '/tmp/convert_to_active.sh', "/bin/bash /tmp/convert_to_active.sh", sb_container)
/bin/bash /tmp/convert_to_active.sh
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 25 17:15:08 2019
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> SQL>
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
ORACLE instance started.
Total System Global Area 1610612704 bytes
Fixed Size		    8896480 bytes
Variable Size		  520093696 bytes
Database Buffers	 1073741824 bytes
Redo Buffers		    7880704 bytes
Database mounted.
SQL>

Database altered.

SQL>
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Standby Database Creation Complete

We now have a primary and standby database that we can begin testing with.

Additional Steps

At this point you should have a physical standby database that is running in maximum performance mode. This might be enough for the testing you want to carry out but there's a number of possible changes that you might want to consider.

  • Change the physical standby database to an Active Standby
  • Convert the current mode (Maximum Performance) to Maximum Protection or Maximum Availability
  • Configure the Oracle Database 19c Active Data Guard feature, DML Redirect

I'll cover these in the following sections but they "icing on the cake" rather than required.

Active Data Guard

This is a relatively trivial change. We just need to alter the standby database to open readonly and then start managed recovery as before

In [29]:
contents='''sqlplus / as sysdba << EOF
SET ECHO ON;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
EOF
'''

create_and_run_script(contents, '/tmp/convert_to_active.sh', "/bin/bash /tmp/convert_to_active.sh", sb_container)
/bin/bash /tmp/convert_to_active.sh
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 25 17:16:16 2019
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>
SQL>
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
ORACLE instance started.

Total System Global Area 1610612704 bytes
Fixed Size		    8896480 bytes
Variable Size		  520093696 bytes
Database Buffers	 1073741824 bytes
Redo Buffers		    7880704 bytes
Database mounted.
SQL>
Database altered.
SQL>
Database altered.

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Maximum Performance to Maximum Availability

For this change we'll use the Database Guard Broker command line tool to make the change

In [ ]:
contents = f'''
dgmgrl sys/{sys_password}@{db_name} << EOF
SET ECHO ON;
SHOW CONFIGURATION;
edit database {stby_name} set property logxptmode=SYNC;
edit configuration set protection mode as maxavailability;
SHOW CONFIGURATION;
EOF
'''

create_and_run_script(contents, '/tmp/max_avail.sh', "/bin/bash /tmp/max_avail.sh", p_container)

Maximum Performance to Maximum Protection

As before we'll use the Database Guard Broker command line tool to make the change.

In [ ]:
contents = f'''
dgmgrl sys/{sys_password}@{db_name} << EOF
SET ECHO ON;
SHOW CONFIGURATION;
edit database {stby_name} set property logxptmode=SYNC;
edit configuration set protection mode as maxprotection;
SHOW CONFIGURATION;
EOF
'''

create_and_run_script(contents, '/tmp/max_prot.sh', "/bin/bash /tmp/max_prot.sh", p_container)

Back to Max Perfromance

We'll use Database Guard Broker to change us back to asynchronus mode.

In [ ]:
contents = f'''
dgmgrl sys/{sys_password}@{db_name} << EOF
SET ECHO ON;
SHOW CONFIGURATION;
edit configuration set protection mode as maxperformance;
edit database {stby_name} set property logxptmode=ASYNC;
SHOW CONFIGURATION;
EOF
'''

create_and_run_script(contents, '/tmp/max_prot.sh', "/bin/bash /tmp/max_prot.sh", p_container)

Oracle Database 19c Acvtive Data Guard DML Redirect

On Oracle Database 19c we can also enable DML redirect from the standby to the primary. I'll add this on the release of the Oracle Database 19c software for on premises.

blog comments powered by Disqus