Oracle Notes by Mikhail: декабря 2020

Oracle made some interesting improvements in Database Configuration Assistant (DBCA) in 18c, namely this:

Creating a copy of an Oracle RAC database
A copy of an Oracle RAC database can be created using the -createDuplicateDB command option -databaseConfigType with the value of RAC or RACONENODE.

It has been possible to create a standby database using the createDuplicateDB DBCA command since 12.2.
18c added a new capability to specify the database configuration type of a new standby, such as: RAC, RAC One Node, or a regular single instance database.

This method by itself requires some prerequisite actions. I do not see that these are documented, so that I am writing this blog post.

Setup

Primary

Hostname: primary
db_name: orcl
db_unique_name: orcl
Configuration Type: Single Instance

Standby

Hostnames: rac1.example.com, rac2.example.com
db_unique_name: racdb
Configuration Type: RAC
SCAN port: 1521
Local listener port: 1522
Grid Home: /u01/app/19.3.0/grid, owner=grid
DB Home: /u01/app/oracle/product/19.3.0/dbhome_1, owner=oracle (role separation)
ASM disk groups: single disk group DATA - it is a sandbox

The naming convention might be a bit odd, but the main purpose of this configuration is to provide some initial steps to setup a new RAC database with a view to making it a new primary.

DBCA Command

This is a DBCA command that I will run to create a standby database in 19.9:

dbca -createDuplicateDB -silent \
     -gdbName orcl \
     -primaryDBConnectionString primary:1521/orcl \
     -sid racdb \
     -initParams "dg_broker_start=true" \
     -sysPassword change_on_install \
     -adminManaged \
     -nodelist rac1,rac2 \
     -recoveryAreaDestination +DATA \
       -recoveryAreaSize 10000 \
     -databaseConfigType RAC \
     -useOMF true \
     -storageType ASM \
       -datafileDestination +DATA \
     -createAsStandby \
       -dbUniqueName racdb \
     -createListener rac1.example.com:1522

Firstly, let me go over the keys that are worth mentioning.

-primaryDBConnectionString primary:1521/orcl - it should have a specific port number even if it is default 1521

-createListener rac1.example.com:1522 - this is the most interesting part. This is part of the RMAN block that is run to create a physical standby database:

duplicate target database
for standby
from active database
dorecover
nofilenamecheck
;

It is the push-based method of active database duplication. Therefore, the primary database should be able to connect to the new standby. I specified the local listener endpoint (1522) in this example. Then, DBCA always tries to create a new listener in this configuration. I do not know how to avoid that here. Even worse, it attempts to create a new listener in DB home. Thankfully, it silently swallows an error if the listener already exists. Here is a sample excerpt from the DBCA log file to substantiate that remark with some facts:

[progressPage.flowWorker] [ 2020-12-30 19:04:30.642 UTC ] [ClusterInfo.getHostName:462]  Hostname = rac1
INFO: Dec 30, 2020 7:04:30 PM oracle.install.commons.system.process.ProcessLauncher launchProcess
INFO: Executing [/u01/app/oracle/product/19.3.0/dbhome_1/bin/lsnrctl, start, rac1.example.com]

INFO: Dec 30, 2020 7:04:30 PM oracle.install.commons.system.process.ProcessLauncher launchProcess
INFO: Starting Output Reader Threads for process /u01/app/oracle/product/19.3.0/dbhome_1/bin/lsnrctl

INFO: Dec 30, 2020 7:04:30 PM oracle.assistants.common.util.NetworkConfigHelper$2 processLine
INFO:

INFO: Dec 30, 2020 7:04:30 PM oracle.assistants.common.util.NetworkConfigHelper$2 processLine
INFO: LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-DEC-2020 19:04:30

INFO: Dec 30, 2020 7:04:30 PM oracle.assistants.common.util.NetworkConfigHelper$2 processLine
INFO:

INFO: Dec 30, 2020 7:04:30 PM oracle.assistants.common.util.NetworkConfigHelper$2 processLine
INFO: Copyright (c) 1991, 2020, Oracle.  All rights reserved.

INFO: Dec 30, 2020 7:04:30 PM oracle.assistants.common.util.NetworkConfigHelper$2 processLine
INFO:

INFO: Dec 30, 2020 7:04:30 PM oracle.assistants.common.util.NetworkConfigHelper$2 processLine
INFO: TNS-01106: Listener using listener name LISTENER has already been started

INFO: Dec 30, 2020 7:04:30 PM oracle.install.commons.system.process.ProcessLauncher launchProcess
INFO: The process /u01/app/oracle/product/19.3.0/dbhome_1/bin/lsnrctl exited with code 1

INFO: Dec 30, 2020 7:04:30 PM oracle.install.commons.system.process.ProcessLauncher launchProcess
INFO: Waiting for output processor threads to exit.

INFO: Dec 30, 2020 7:04:30 PM oracle.install.commons.system.process.ProcessLauncher launchProcess
INFO: Output processor threads exited.

INFO: Dec 30, 2020 7:04:30 PM oracle.assistants.common.util.NetworkConfigHelper startListener
INFO: Exit code of lsnrctl is:1

INFO: Dec 30, 2020 7:04:30 PM oracle.assistants.dbca.driver.backend.steps.ListenerConfigStep createStaticListener
INFO: Static listener created.

INFO: Dec 30, 2020 7:04:30 PM oracle.assistants.dbca.driver.StepDBCAJob$1 update
INFO: Percentage Progress got for job:Listener config step progress:100.0

There is another little drawback with this approach - it creates listener.ora in DB home which uses a static configuration. It is harmless, but I would rather clean it up after the exercise to have a nice and tidy environment.

Prerequisite Steps

It should be possible to establish a connection from the primary host using the local listener endpoint: rac1.example.com:1522.
I do not discuss DNS setup - it is implied.
I need to configure a static registration for the new RAC instance. For that, I edit my GI listener.ora on the host I am going to run the duplicate command from (rac1):

# /u01/app/19.3.0/grid/network/admin/listener.ora
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=racdb1)
         (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
       )
   )

That is basically it. Once I reload the local listener, I am good to continue to the next step.

Creating RAC Physical Standby

[oracle@rac1 ~]$ dbca -createDuplicateDB -silent \
>      -gdbName orcl \
>      -primaryDBConnectionString primary:1521/orcl \
>      -sid racdb \
>      -initParams "dg_broker_start=true" \
>      -sysPassword change_on_install \
>      -adminManaged \
>      -nodelist rac1,rac2 \
>      -recoveryAreaDestination +DATA \
>        -recoveryAreaSize 10000 \
>      -databaseConfigType RAC \
>      -useOMF true \
>      -storageType ASM \
>        -datafileDestination +DATA \
>      -createAsStandby \
>        -dbUniqueName racdb \
>      -createListener rac1.example.com:1522
Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/racdb/racdb.log" for further details.

That is it:

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select instance_name, host_name from gv$instance;

INSTANCE_NAME    HOST_NAME
---------------- --------------------
racdb1           rac1.example.com
racdb2           rac2.example.com

Further Steps

Delete /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora. DBCA created this file with the following content:

SID_LIST_RAC1.EXAMPLE.COM =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = racdb1)
    )
  )

RAC1.EXAMPLE.COM =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.example.com)(PORT = 1522))
  )

Adding standby redo logs, creating a new Data Guard Configuration, enabling FORCE_LOGGING, Flashback database, etc. - I do not mention it here. The purpose of this post to show how to instantiate a new RAC standby database in one command after completing some simple preliminary steps.

Notable Downsides

There ain't no such thing as a free lunch.
Both slow connection between the sites, and a "huge" database size limits the applicability of this method.
I do not see how this method can utilize the restartable duplication too.
In all other cases, it is possible to quickly spin up a new RAC standby database using this excellent DBCA createDuplicateDB command.

Oracle Notes by Mikhail

Страницы

четверг, 31 декабря 2020 г.

Creating RAC Physical Standby for Single Instance Primary

Setup

Primary

Standby

DBCA Command

Prerequisite Steps

Creating RAC Physical Standby

Further Steps

Notable Downsides

Blog Archive

Страницы

четверг, 31 декабря 2020 г.

Creating RAC Physical Standby for Single Instance Primary

Setup

Primary

Standby

DBCA Command

Prerequisite Steps

Creating RAC Physical Standby

Further Steps

Notable Downsides

четверг, 31 декабря 2020 г.