Oracle Database 19c: New Features for Administrators

 Oracle Database 19c: New Features for Administrators

Using General Database Overall Enhancement:

Automatic Execution of Fixups Scripts and root.sh

Oracle database 19c supports automatic execution of the orainstRoot.sh and root.sh shell script using the user-provided root or sudo credentials.

If the root option selected, enter the password for the root user. If the sudo option is selected, enter the password for the user defined in the username field. To enable sudo execution, you must ask the system administrator to enter the username as a member of the sudoers list in the /etc/sudoers file.

In silent mode installation, the user specifies values for the following variables in the response file used by the runInstaller executable:

§  Oracle.install.db.rootconfig.executeRootScript=TRUE/FALSE

§  Oracle.install.db.rootconfig.configMethod=SUDO/ROOT

§  Oracle.install.db.rootconfig.sudoPath=<path of the sudo program>

§  Oracle.install.db.rootconfig.sudoUserName=<sudo username>

The root or sudo password cannot be specified in the response file.

The user has to provide the passwords to the installer.

Cloning a Remote PDB (18c / 19c)

1.       Remote Source PDB should up and fully functional.

Sql> connect sys@CDB2 as sysdba

Create PUBLIC DATABASE LINK link_test1

Connect to system identified by password using ‘PDB1’;

2.       Clone the remote PDB1 from CDB1 TO PDB2

Sql> alter sesstion set DB_CREATE_FILE_DEST=’+DATA_01’;

SQL> Create pluggable database pdb2 from pdb1@dblink_pdb1;

3.       Open PDB2 in readonly or read/write mode

Alter pluggable database pdb2 open;

Using DBCA to Clone a Remote PDB:

Oracle database 19c offers to complete the PDB cloning by using DBCA in silent mode. The DBCA operation execute the following:

1.    Create a Common user with privileges in the remote CDB CDB1.

2.     Use DBCA to clone the remote PDB1 from CDB1 to PDB2.

Dbca –silent –createPluggableDatabase – createFromRemotePDB –remotePDBName PDB1 –remoteDBConnString CDB1 –sysDBAUserName system –sysDBAPassword password –remoteDBSYSDBAUserName SYS –remoteDBSYSDBAUserPassword password –dbLinkUserName c##remote_user –dbLinkuserPassword Password –sourceDB CDB2 –pdbName PDB2

1.       Check the presence of the database link. If the database link exist, DBCA drops it.

2.       Create the database link.

3.       Creates the PDB from the remote PDB.

4.       Check the status of the cloned PDB to verify that its in mounted mode.

5.       Open the cloned PDB

As the oracle 18c, the user in the local target CDB must have the Create Pluggable database privilege in the CDB root.

The remote CDB must use local undo mode. The remote CDB must be in archive log mode.

Relocating a Remote PDB (18c):

Use a single statement to relocate remote PDB1 from CDB1 into local CDB2

1.       Connect to  CDB2 as a common user to create the database link.

CONNECT sys@CDB2 AS SYSDBA

CREATE PUBLIC DATABASE LINK link_CDB1

Connect to system IDENTIFIED by password USING ‘CDB1’;

2.       Relocate remote PDB1 into local CDB2 .

SQL>  ALTER SESSION SET DB_CREATE_FILE_DEST=’+DATA_01’;

SQL> CREATE PLUGGABLE DATABASE pdb1 FROM pdb1@link_CDB1 RELOCATE;

3.       Open PDB1 in read/write mode.

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN; 

In oracle database 18c, to complete a PDB relocation, you must execute several SQL commands. The “pull” mode, connected to the CDB where the PDB exists, draining existing connections and migrating new connections without requiring any change to the application.

Using DBCA to Relocate a Remote PDB (19c)

1.       Use DBCA to relocate the remote PDB1 from CDB1 into CDB2.

$export ORACLE_SID=’CDB2’

Dbca –silent –relocatePDB –remotePDBName PDB1 –remoteDBConnString CDB1 –sysDBAUserName system sysDBAPassword password –remotDBsysDBAUsername SYS –remoteDBsysDBAUserPassword password –dblinkUserName c##remote_user –dbLinkUserPassword password

-sourcDB CDB2 –pdbname PDB1;

Oracle database 19c Using DBCA to Relocate a Remote PDB. DBCA operation executes the following steps:

1.       Checks the presence of database link. If the database link exists, DBCA drops it.

2.       Creates the database link.

3.       Creates the PDB from remote PDB.

4.       Checks the status of the cloned PDB to  verify that it is in mounted mode.

5.       Opens the relocated PDB.

The user in the local database must have the CREATE PLUGGABLE DATABASE privilege in the CDB root container. The remote and local database must in archive log mode. The common user in the remote database that the database link connects to must have the CREATE PLUGGABLE DATABASE, SESSION and SYSOPER privileges.

The local and remote database must either have the same options installed, or the remote database must have a subset of those present on the local database.

Duplicating a CDB (18c)

To duplicate CDB1 as CDB2:

1.       Create PFILE from CDB1 SPFILE for CDB2.

2.       Set values for initialization parameters for CDB2.

3.       Create directories and the password file required for the CDB2 instance to start.

4.       Create CDB2 SPFILE from CDB2 PFILE required for the CDB2 instance.

5.       Connect to the source CDB1 and auxiliary CDB2 instance before you start duplicating.

$rman AUXILIARY sys TARGET sys@CDB1

Duplicate target database to CDB2 from active database db_file_name_convert (‘CDB1’,’CDB2’);

Using DBCA to Duplicate a CDB (19c)

1.       Use DBCA to duplicate CDB1 to CDB2

Export ORACLE_SID=CDB2

Dbca –silent –createDuplicateDB –gdbNameCDB2 –sid CDB2 –primary DBConnectionSring host01:1521/CDB1 –databaseConfigType SI –initParams db_unique_name=CDB2 –sysPassword password –datafileDestination /u01/oracle/oradata

Another example: Duplicating single instance CDB to RAC CDB:

Dbca –silent –createDuplicateDB –gdbName CDB2_DUP –primary DBConnectionSring PRIMSI –sid dup –databaseConfigType RAC –adminManaged –nodelist node1,node2 –initParams db_unique_name=cdb2_dup sysPassword password –storageType ASM –datafileDestination +DATA_01  -useOMF  true –createListener LISTENERCDB2_DUP:1530;

In Oracle Multitenant, the term snapshot means different things depending on context.

·         Storage level snapshot, which is used to create a snapshot copy PDB.

·         PDB-level snapshot, which is used to create a standard clone PDB. A PDB-level snapshot does not involve storage-level snapshots.

Different Types of Snapshots:

·       A storage-level snapshot is only supported on specific files systems. The storage and security credential requirements depends on the setting of the CLONEDB initialization parameter.

·         A snapshot copy PDB does not include a complete copy of the source data files. Rather, Oracle Database creates a storage-level snapshot of the underlying file system, and then creates the clone PDB from the snapshot. Unlike a standard clone PDB, from 

OPERATING SYSTEM GROUPS USED IN ORACLE SOFTWARE:

OPERATING SYSTEM GROUPS USED IN ORACLE SOFTWARE:

ORACLE GRID INFRASTRUCTURE:

- OSASM Group for Oracle ASM Administration.
- OSDBA Group for ASM Database Administrator group for Oracle ASM
- OSOPER Group for Oracle ASM group for Oracle ASM operators (Optional)

Oracle Database Software Group:

- OSDBA GROUP   ==> Full DBA privileges
- OSOPER GROUP - 
- OSBACKUPDBA
- OSKMDBA GROUP
-OSRACDBA GROUP (New in 12.2, grid user must be part of it if it exist.)

Oracle Grid Infrastructure Groups:

Group	Typical Name	Purpose
Install	Oinstall	User have Oracle installation owner system privileges, also known as the orainventory group.
OSASM	DBA OR ASMADMIN	Users are granted the SYSASM privilege access to Oracle ASM instance for administrative function such as instance startup and shutdown along with mounting and dismounting of diskgroup.
OSDBA	DBA OR ASMDBA	Users are granted read and write access to files managed by Oracle ASM. All Oracle database software owners must be a member of this group.
OSOPER	DBA OR ASMOPER	Users are granted the SYSOPER privilege access to Oracle ASM instances providing a limited subset of administrator functions such as starting and stopping Oracle ASM instances.

Oracle Database Group:

INSTALL	OINSTALL	User have Oracle installation owner system privileges, also known as the orainventory group.
OSDBA	DBA	Users are granted the SYSDBA Database Administrator privilege allowing access to all user data, startup/shutdown, create/drop database, backup/recovery and open/mount functions.
OSOPER	DBA OR OPER	Users are granted the SYSOPER privilege providing a limited subset of administrator functions without access to user data. It allows startup/shutdown,open/mount, backup, and only complete recovery operations as compared to partial or limited recovery.

CONFIGURING HOSTNAME & PORT NUMBER PER PDB

CONFIGURING HOSTNAME & PORT NUMBER PER PDB

The hostname and port number settings for a PDB are important only if proxy PDBs will reference the PDB.

SQL> ALTER PLUGGABLE DATABASE CONTAINER HOST = <host_name>;
SQL> ALTER PLUGGABLE DATABASE CONTAINER PORT = <port_number>;


SQL> ALTER PLUGGABLE DATABASE CONTAINER HOST RESET;
SQL> ALTER PLUGGABLE DATABASE CONTAINER PORT RESET;

Initialisation Parameters

Initialisation Parameters

A single SPFILE for CDB

PDB values change :

- Loaded in memory after PDB close- Store in dictionary after CDB shutdown- Only for parameter ISPDB_MODIFIABLE =TRUE;

CONNECT / AS SYS@PDB1 AS SYSDBA
ALTER SYSTEM SET DDL_LOCK_TIMEOUT=10;
SHOW PARAMETER DDL_LOCK_TIMEOUT;

CONNECT / AS SYS@PDB2 AS SYSDBA

ALTER SYSTEM SET DDL_LOCK_TIMEOUT=20 SCOPE=BOTH;

CONNECT / AS SYSDBA

SELECT VALUE, ISPDB_MODIFIABLE, CON_ID FROM V$SYSTEM_PARAMETER WHERE  NAME='ddl_lock_timeout';

Using ALTER SYSTEM Statement on PDB

AFFECTING CURRENT PDB ONLY:

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE;

ALTER SYSTEM ENABLE / DISABLE RESTRICTED SESSION;

ALTER SYSTEM KILL SESSION

ALTER SYSTEM SET PARAMTER 

ALTER SYSTEM EXECUTING IN A PDB BUT AFFECT THE WHOLE CDB:

ALTER SYSTEM CHECKPOINT; -- Affects all datafiles except those in read only or offline.

ALL OTHER ALTER SYSTEM STATEMENT AFFECT THE WHOLE CDB & MUST BE RUN BY A COMMON USER IN THE CDB ROOT.

ALTER SYSTEM SWTICH LOGFILE; -- this is not allowed from PDB database.

MODIFYING PDB SETTINGS

MODIFYING PDB SETTINGS

SQL> CONNECT AS SYS@PDB1 AS SYSDBA

SQL> ALTER PLUGGABLE DATABASE DATAFILE '+DATA01/PDB1/DATAFILE/PDB1_test01.dbf' ONLINE;

SQL> ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE PDB1_TBS;

SQL> ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;SQL> ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 2G);

SQL> ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO pdb1_app1;

CHANGING PDB MODE

CHANGING PDB MODE

RESTRICTED MODE

SQL> CONNECT SYS@PDB1 AS SYSDBA

SQL> ALTER DATABASE CLOSE;

SQL> ALTER DATABASE OPEN RESTRICTED;

READ ONLY MODE

SQL>  CONNECT / AS SYSDBA

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;

READ WRITE:

SELECT NAME,OPEN_MODE FROM V$PDBS;

SHUTTING DOWN A CDB INSTANCE

SHUTTING DOWN A CDB INSTANCE

SQL> CONNECT SYS@CDB1 AS SYSDBA
SQL> SHUTDOWN IMMEDIATE;

• ALL PDBs closed (no new specific message)
• CDB closed.
• CDB dismounted.
• Instance shutdown.

SQL> CONNECT SYS@PDB1 AS SYSDBA

SHUTDOWN IMMEDIATE;

• PDB closed.

CLOSING A PDBS

CLOSING A PDBS

SQL> CONNEXT / AS SYSDBA;
SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASEALL EXCEPT  PDB1,PDB3  CLOSE;
SQL> ALTER PLUGGABLE DATABASEALL EXCEPT  ALL  CLOSE;

SQL> CONNECT AS SYS@PDB1 AS SYSDBA
SQL> ALTER PLUGGABLE DATABASE CLOSE;
or
SQL> SHUTDOWN IMMEDIATE;

AUTOMATIC PDB OPENING

Automatically keep PDBs State after CDB STARTUP:

SQL> ALTER PLUGGABLE DATABASE PDB1 SAVE STATE;
SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;
SQL> ALTER PLUGGABLE DATABASE PDB1,PDB3,PDB6 SAVE STATE;

Automatically keep PDBs State after CDB STARTUP:

SQL> ALTER PLUGGABLE DATABASE PDB1 DISCARD STATE;

SQL> ALTER PLUGGABLE DATABASE ALL DISCARD STATE;
SQL> ALTER PLUGGABLE DATABASE PDB1,PDB3,PDB6 DISCARD STATE;