3 Creating a Physical Standby Database

Before you create a standby database you must first ensure the primary database is properly configured.

This section describes the tasks you perform to create a physical standby database. It is written at a level of detail that requires you to already have a thorough understanding of the following topics:

• Database administrator authentication

• Database initialization parameters

• Managing redo logs, data files, and control files

• Managing archived redo logs

• Fast recovery areas

• Oracle Net configuration

Table 3-1 provides a checklist of the tasks that you perform to create a physical standby database and the database or databases on which you perform each task.

You can create a physical standby of a multitenant container database (CDB) just as you can create a physical standby of a regular primary database.

The following are some of the behavioral differences to be aware of when you create and use a physical standby of a CDB:

• If you execute a switchover or failover operation, the entire CDB undergoes the role change. If you used the ENABLED_PDBS_ON_STANDBY intialization parameter, then be aware of the possibility that not every PDB is present in both the primary and the standby databases.

• The database role is defined at the CDB level, not at the individual container level.

• Any DDL related to role changes must be executed in the root container because a role is associated with an entire CDB. Individual pluggable databases (PDBs) do not have their own roles.

• In a physical standby of a CDB, the syntax of SQL statements is generally the same as for noncontainer databases. However, the effect of some statements, including the following, may be different:

  • ALTER DATABASE RECOVER MANAGED STANDBY functions only in the root container; it is not allowed in a PDB.

  • A role is associated with an entire CDB; individual PDBs do not have their own roles. Therefore, the following role change DDL associated with physical standbys affect the entire CDB:

    ALTER DATABASE SWITCHOVER TO target_db_name

    ALTER DATABASE ACTIVATE PHYSICAL STANDBY

• The ALTER PLUGGABLE DATABASE [OPEN|CLOSE] SQL statement is supported on the standby, provided you have already opened the root container.

• The ALTER PLUGGABLE DATABASE RECOVER statement is not supported on the standby. (Standby recovery is always at the CDB level.)

• To administer a multitenant environment, you must have the CDB_DBA role.

• Oracle recommends that the standby database have its own keystore.

• In a multitenant environment, the redo must be shipped to the root container of the standby database.

  The following is an example of how to determine whether redo is being shipped to the root container. Suppose your primary database has the following settings:

  LOG_ARCHIVE_DEST_2='SERVICE=boston ASYNC VALID_FOR=(ONLINE_LOGFILES,
  PRIMARY_ROLE) DB_UNIQUE_NAME=boston'
  

  Redo is being shipped to boston. The container ID (CON_ID) for the root container is always 1, so you must make sure that the CON_ID is 1 for the service boston. To do this, check the service name in the tnsnames.ora file. For example:

  boston = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=boston-server)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=boston.us.example.com))
  

  The service name for boston is boston.us.example.com.

  On the standby database, query the CDB_SERVICES view to determine the CON_ID. For example:

  SQL> SELECT NAME, CON_ID FROM CDB_SERVICES;
  
  NAME                                 CON_ID
  ---------------------------------------------
  boston.us.example.com                 1
  

  The query result shows that the CON_ID for boston is 1.

In an Oracle Data Guard configuration, a PDB on a primary database is created in the same way that a PDB on a regular database is created.

This section discusses creating a pluggable database (PDB) in a primary database, when a physical standby is being used.

The steps to create a PDB on a regular database are documented in the Oracle Database Administrator's Guide. Before following those steps, note the following:

• In Oracle Database 12c Release 1 (12.1), you could only specify whether a PDB was created and recovered in all (ALL) standbys or in no (NONE) standbys when adding a PDB to the primary database. As of Oracle Database 12c Release 2 (12.2.0.1), you can specify a subset of PDBs to be replicated on a physical standby of a multitenant container database (CDB), instead of having to choose either all PDBs or none. To do so, use the ENABLED_PDBS_ON_STANDBY initialization parameter to specify a list of PDBs or use the enhanced STANDBYS qualifier on the CREATE PLUGGABLE DATABASE statement, or both. PDBs that are not enabled on a standby CDB can remain disabled (true SUBSET Standby) or they can be enabled at a later date when all the required files are available at the standby CDB.

• The ENABLED_PDBS_ON_STANDBY parameter is valid only on a physical standby; it is ignored by primary databases. (It can be set on a primary database to be used if that database ever becomes a standby database.) It can be used to specify which PDBs should or should not be enabled on a physical standby database. If the parameter is not specified, then all PDBs in the CDB are created on the standby unless the STANDBYS clause is used. See ENABLED_PDBS_ON_STANDBY for more information about this parameter.

• To specify in which standby CDBs the new PDB being created is to be included, you can also use the STANDBYS clause of the SQL CREATE PLUGGABLE DATABASE statement. The syntax is as follows:

  create pluggable database … STANDBYS={('cdb_name', 'cdb_name', ...) | NONE | ALL [EXCEPT ('cdb_name', 'cdb_name', ...)]}
  

  • cdb_name is the DB_UNIQUE_NAME for the physical standbys in which the PDB is to be included

  • NONE excludes the PDB being created from all standby CDBs. When a PDB is excluded from all standby CDBs, the PDB's data files are offline and marked as unnamed on all of the standby CDBs. Any new standby CDBs that are instantiated after the PDB has been created must disable the PDB for recovery explicitly to exclude it from the standby CDB. It is possible to enable a PDB on a standby CDB after it was excluded on that standby CDB.

  • ALL (the default) includes the PDB being created in all standby CDBs.

  • EXCEPT cdb_name includes the PDB being created in all standby CDBs except for those CDBs listed in this clause by their DB_UNIQUE_NAME.

  Parentheses are required around the list of CDB names and each name must be enclosed within single quotation marks. The value of DB_UNIQUE_NAME can be up to 30 characters and is case insensitive. The following characters are valid in a database name: alphanumeric characters, underscore (_), number sign (#), and dollar sign ($).

  Note:

  The EXCEPT clause is available starting with Oracle Database 12c Release 2 (12.2.0.1).

• To create a PDB as a local clone from a different PDB or from the seed PDB within the same primary CDB, copy the data files that belong to the source PDB over to the standby database. (This step is not necessary in an Oracle Active Data Guard environment because the data files are copied automatically at the standby when the PDB is created on the standby database.)

  To perform a remote clone of a PDB from another CDB into the primary CDB, you must use the STANDBY=NONE clause and then copy the files and enable recovery by following the steps in the My Oracle Support note 2049127.1 at http://support.oracle.com.

• To create a PDB from an XML file, copy the data files specified in the XML file to the standby database.

  If your standby database has the Oracle Active Data Guard option enabled (open read-only), then copy to it the same set of PDB data files that are to be plugged into the primary database. To minimize disruptions to managed standby recovery or database sessions running on systems that have Oracle Active Data Guard enabled, you must copy these files to the standby database before plugging in the PDB at the primary database. Ensure that the files are copied to an appropriate location where they can be found by managed standby recovery:

  • If data files reside in standard operating system file systems, then the location of the files at the standby database are based on the value of the DB_FILE_NAME_CONVERT parameter. For more details about setting primary database initialization parameters, see Set Primary Database Initialization Parameters

  • If data files reside in ASM, then use the ASMCMD utility to copy the files to the following location at the standby database:

    <db_create_file_dest>/<db_unique_name>/<GUID>/datafile
    

    The GUID parameter is the global unique identifier assigned to the PDB; once assigned, it does not change. To find the value of the GUID parameter, query the V$CONTAINERS view before unplugging the PDB from its original source container. The following example shows how to find the value of the GUID parameter for the PDB whose PDB container ID in the source container is 3:

    SELECT guid
      FROM V$CONTAINERS
     WHERE con_id=3;
     
    GUID
     
    D98C12257A951FC4E043B623F00A7AF5
    

    In this example, if the value of the DB_CREATE_FILE_DEST parameter is +DATAFILE and the value of the DB_UNIQUE_NAME parameter is BOSTON, then the data files are copied to:

    +DATAFILE/BOSTON/D98C12257A951FC4E043B623F00A7AF5/datafile
    

The path name of the data files on the standby database must be the same as the resultant path name when you create the PDB on the primary, unless the DB_FILE_NAME_CONVERT database initialization parameter has been configured on the standby. In that case, the path name of the data files on the standby database is the path name on the primary with DB_FILE_NAME_CONVERT applied.