Andre Luiz Dutra Ontalba (Board Member)
Create Autonomous Database
Category: Cloud Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

Create Autonomous Database

This article provides a run through of creating a new Autonomous Database service on the Oracle Cloud.
 
Log into Oracle Cloud and click the “Create Instance” link.
Click on the “Create” button in the Autonomous Transaction Processing

 

Select the compartment you want to build the service in, then click the “Create Autonomous Database” button.

 

Enter the details of the service you want to create. The default sizes are 1 CPU core and 1TB of storage. Remember to select the appropriate licensing model. Click the “Create Autonomous Database” button.

 

Wait while the service is provisioned. You will see the state is marked as “Provisioning”.
The details screen allows you to perform some basic operations with the service, including scale up/down, manual backups and restores from backups. Click on the “Service Console” button.

 

You are presented with the dashboard, which will look quite empty as the service has just been provisioned. Click the “Activity” link on the left menu.

 

 

You are presented with the activity screen, which will look relatively quiet as the service has just been provisioned. Click the “Administration” link on the left menu.

 

The administration screen allows you to perform some basic administration of the service.

 

Connecting to the Autonomous Database Service Using SQL Developer
Go to the administration screen for the service and click the “Download Client Credentials (Wallet)”.

 

Enter the password to protect the credentials store.

 

Open SQL Developer and create a new connection. Use the username and password specified when you provisioned the service. Use a connection type of “Cloud Wallet” and enter the zip file location. You can now click the “Test” or “Connect” button.

 

Now all ready to use

 

Hope this helps. See you !!!
 
André  Ontalba  – www.dbadutra.com
 
Rodrigo Mufalani (OCM)
Oracle ACE Member  –  https://mufalani.wordpress.com

Creating a Compute Instance in OCI
Category: Cloud Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

Creating a Compute Instance on Oracle OCI

Let’s start our journey in the Oracle cloud

 
 
 
 

First we must log into the console the cloud.
In my case I created my account in Frankfurt, because I am using the services here in Europe.
 
 

You will be directed to the login of your Tenancy.  Enter your user name and password and click “Sign in”.
 
 
Now that you are logged in, let’s start the creation of the Compute Instance.
 
 
Click Compute and then select Instances.
 
 
 
 
Click in Create Instance.
 
 
 
 
First of a name to your Compute Instance, I put Database_19c.

Then select in which Availability domain you want to create your Compute Instance. I chose the AD2.

After that we choose the Instance type, we can choose VM (Virtual Machine) or Bare Metal Machine (dedicated computing instance).

I chose the VM because it is cheaper in terms of using credits and also now I do not need to in a dedicated computing instance.

I will write an article later, giving an overview of the main principles and basic understandings to work with OCI.

Now we will generate the keys to use in our compute instance

I recommend you generate a key using the Putty Key Generator.
 
 
 
 
 
 Click Generate and move the mouse until the key is created.

 
After it was created save a copy as Public Key and another as Private Key.
 
 
Now let’s add the public key to access the server after Compute Instance is created through SSH. Click Choose Files.

 
Select the file saved as Public Key, in my case Public_Keys.
 
After that, if you did not create any Virtual Network Circuit (VNC), it will be created automatically. In my case I already created then it has already been selected.
Now click Create and wait a few minutes.
 
 
 
Screen while creating the resource.
 
 
 
Ready your compute instance is OK.
 
Now we will access the VM through SSH.
 
 
 
 Take the IP that looks like for you in this item and open in an SSH client, remembering that we should use the private key now to make the connection.

In my case I access using mobaXterm, in Remote host I put the IP, select the username and type “OPC” and select Use the Private Key that was generated.
 
 
 
Ready server connected and ready to install Oracle Database.
 
 

 
 
 
Hope this helps. See you !!!
 
André  Ontalba  – www.dbadutra.com
 
Rodrigo Mufalani (OCM)
Oracle ACE Member  –  https://mufalani.wordpress.com
 
 

 


New Feature Released in Oracle 12c for EE – Privilege Analysis
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

New Feature Released in Oracle 12c for EE – Privilege Analysis

 
Well to begin the year, Oracle has already released some features that were once sold separately in the Oracle Database 19c, 18c products.
 
It has now been released to the 12c version of the Privilege Analysis feature that is part of the Security area in particular DB Vault.
 
 Here is the link that talks about the Licensing part – click here and also the documentation to use this feature that greatly helps the collection part for auditing – click here.
 
“Privilege Analysis feature of Database Vault has had its licensing changed from this week to now be free as part of an Enterprise Edition database (EE). This feature used to be a cost option and required a Database Vault license. To use privilege analysis you didn’t actually need to enabled Database Vault but you needed the license. This is no longer the case from this week; Also i have been told that the licensing is also retroactive to all 12c versions.”
 
Hope this helps. See you !!
André Ontalba – www.dbadutra.com

Data Guard Physical Setup using Data Broker in Oracle Database 19c
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0

 

 Data Guard Physical Setup using Data Broker in Oracle Database 19c

 
 
 

Environments

  • You have two servers (VMs or physical) with an operating system and Oracle installed on them. My environment I’ve used Oracle Linux 7.6 and Oracle Database 19c.
  • The primary server (duts-dg1) has a running instance.
  • The standby server (duts-dg2) has a software only installation.
  • There is nothing blocking communication between the machines over the listener ports.

 

Primary Server Setup

Logging

Check that the primary database is in archivelog mode.

SELECT log_mode FROM v$database;

LOG_MODE

————

NOARCHIVELOG

SQL>

If it is noarchivelog mode, switch is to archivelog mode.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

Enabled forced logging by issuing the following command.

ALTER DATABASE FORCE LOGGING;

— Make sure at least one logfile is present.

ALTER SYSTEM SWITCH LOGFILE;

Create standby redo logs on the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following standby redo logs must be created on both servers.

— If Oracle Managed Files (OMF) is not used.

ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo01.log’) SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo02.log’) SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo03.log’) SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/data/duts/std_redo04.log’) SIZE 100M;

— If Oracle Managed Files (OMF) is used.

ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE SIZE 100M;

If you want to user flashback database, enable it on the primary now, so it will be enabled on the standby also. I always use it in my environments.

ALTER DATABASE FLASHBACK ON;

 

Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to “duts” on the primary database.

SQL> show parameter db_name

NAME                                TYPE       VALUE

———————————— ———– ——————————

db_name                             string     duts

SQL> show parameter db_unique_name

NAME                                TYPE       VALUE

———————————— ———– ——————————

db_unique_name                      string     duts

SQL>

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. For this example, the standby database will have the value “duts_stby”.

Make sure the STANDBY_FILE_MANAGEMENT parameter is set.

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

 

Service Setup

Entries for the primary and standby databases are needed in the “$ORACLE_HOME/network/admin/tnsnames.ora” files on both servers.

You can create these using the Network Configuration Utility (netca) or manually.

The following entries were used during this setup. Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the databases when they are down, so the services will not be present.

duts =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = duts)

    )

  )

duts_stby =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg2)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = duts)

    )

  )

The “$ORACLE_HOME/network/admin/listener.ora” file on the primary server contains the following configuration.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg1)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = duts_DGMGRL)

      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)

      (SID_NAME = duts)

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

The “$ORACLE_HOME/network/admin/listener.ora” file on the standby server contains the following configuration.

Since the broker will need to connect to the database when it’s down, we can’t rely on auto-registration with the listener, hence the explicit entry for the database.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg2)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = duts_stby_DGMGRL)

      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)

      (SID_NAME = duts)

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Once the listener.ora changes are in place, restart the listener on both servers.

lsnrctl stop

lsnrctl start

 

Standby Server Setup

Prepare for Duplicate

Create a parameter file for the standby database called “/tmp/initduts_stby.ora” with the following contents.

*.db_name=’duts’

Create the necessary directories on the standby server.

mkdir -p /u02/data/duts/pdbseed

mkdir -p /u02/data/duts/pdb1

mkdir -p /u02/app/oracle/fast_recovery_area/duts

mkdir -p /u02/app/oracle/admin/duts/adump

Create a password file, with the SYS password matching that of the primary database.

$ orapwd file=/u01/app/oracle/product/19.0.0/db_1/dbs/orapwduts password=oracle entries=10

 

 

Create Standby Using DUPLICATE

Start the auxiliary instance on the standby server by starting it using the temporary “init.ora” file.

$ export ORACLE_SID=duts

$ sqlplus / as sysdba

Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication.

$ rman TARGET sys/oracle@duts AUXILIARY sys/oracle@duts_stby

Now issue the following DUPLICATE command.

DUPLICATE TARGET DATABASE

  FOR STANDBY

  FROM ACTIVE DATABASE

  DORECOVER

  SPFILE

    SET db_unique_name=’duts_stby’ COMMENT ‘Is standby 19c’

  NOFILENAMECHECK;

If you need to convert file locations, or alter any initialization parameters, you can do this during the DUPLICATE using the SET command.

DUPLICATE TARGET DATABASE

  FOR STANDBY

  FROM ACTIVE DATABASE

  DORECOVER

  SPFILE

    SET db_unique_name=’duts_stby’ COMMENT ‘Is standby 19c’

    SET db_file_name_convert=’/u01/data/duts/’,’/u02/data/duts/’

    SET log_file_name_convert=’/u01/data/duts/’,’/u02/data/duts/’

    SET job_queue_processes=’0′

  NOFILENAMECHECK;

A brief explanation of the individual clauses is shown below.

  • FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
  • FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafiles, without an additional backup step.
  • DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
  • SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
  • NOFILENAMECHECK: Destination file locations are not checked.
  •  

Once the command is complete, we can start using the broker.

 

Enable Broker

At this point we have a primary database and a standby database, so now we need to start using the Data Guard Broker to manage them. Connect to both databases (primary and standby) and issue the following command.

ALTER SYSTEM SET dg_broker_start=true;

On the primary server, issue the following command to register the primary server with the broker.

$ dgmgrl sys/oracle@duts

DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 14:39:33 2019

Version 19.2.0.0.0

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

Welcome to DGMGRL, type “help” for information.

Connected as SYSDBA.

DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS duts CONNECT IDENTIFIER IS duts;

Configuration “dg_config” created with primary database “duts”

DGMGRL>

Now add the standby database.

DGMGRL> ADD DATABASE duts_stby AS CONNECT IDENTIFIER IS duts_stby MAINTAINED AS PHYSICAL;

Database “duts_stby” added

DGMGRL>

Now we enable the new configuration.

DGMGRL> ENABLE CONFIGURATION;

Enabled.

DGMGRL>

The following commands show how to check the configuration and status of the databases from the broker.

DGMGRL> SHOW CONFIGURATION;

Configuration – dg_config

Protection Mode: MaxPerformance

  Members:

  duts      – Primary database

    duts_stby – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 26 seconds ago)

DGMGRL> SHOW DATABASE duts;

Database – duts

  Role:               PRIMARY

  Intended State:     TRANSPORT-ON

  Instance(s):

    duts

Database Status:

SUCCESS

DGMGRL> SHOW DATABASE duts_stby;

Database – duts_stby

  Role:               PHYSICAL STANDBY

  Intended State:     APPLY-ON

  Transport Lag:      0 seconds (computed 1 second ago)

  Apply Lag:          0 seconds (computed 1 second ago)

  Average Apply Rate: 15.00 KByte/s

  Real Time Query:    OFF

  Instance(s):

    duts

Database Status:

SUCCESS

DGMGRL>

 

 

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following commands. Connect to the primary database (duts) and switchover to the standby database (duts_stby).

$ dgmgrl sys/oracle@duts

DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 14:55:33 2019

Version 19.2.0.0.0

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

Welcome to DGMGRL, type “help” for information.

Connected as SYSDBA.

DGMGRL> SWITCHOVER TO duts_stby;

Performing switchover NOW, please wait…

Operation requires a connection to instance “duts” on database “duts_stby”

Connecting to instance “duts”…

Connected as SYSDBA.

New primary database “duts_stby” is opening…

Operation requires start up of instance “duts” on database “duts”

Starting instance “duts”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “duts_stby”

DGMGRL>

Let’s switch back to the original primary. Connect to the new primary (duts_stby) and switchover to the new standby database (duts).

$ dgmgrl sys/oracle@duts_stby

DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 14:57:20 2019

Version 19.2.0.0.0

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

Welcome to DGMGRL, type “help” for information.

Connected as SYSDBA.

DGMGRL> SWITCHOVER TO duts;

Performing switchover NOW, please wait…

Operation requires a connection to instance “duts” on database “duts”

Connecting to instance “duts”…

Connected as SYSDBA.

New primary database “duts” is opening…

Operation requires start up of instance “duts” on database “duts_stby”

Starting instance “duts”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “duts”

DGMGRL>

 

Database Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements. Connect to the standby database (duts_stby) and failover.

$ dgmgrl sys/oracle@duts_stby

DGMGRL for Linux: Release 19.0.0.0.0 – Production on Tue May 11 15:00:20 2019

Version 19.2.0.0.0

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

Welcome to DGMGRL, type “help” for information.

Connected as SYSDBA.

DGMGRL> FAILOVER TO duts_stby;

Performing failover NOW, please wait…

Failover succeeded, new primary is “duts_stby”

DGMGRL>

Since the standby database is now the primary database it should be backed up immediately.

The original primary database can now be configured as a standby. If flashback database was enabled on the primary database, then this can be done relatively easily with the following command.

DGMGRL> REINSTATE DATABASE duts;

Reinstating database “duts”, please wait…

Operation requires shut down of instance “duts” on database “duts”

Shutting down instance “duts”…

ORACLE instance shut down.

Operation requires start up of instance “duts” on database “duts”

Starting instance “duts”…

ORACLE instance started.

Database mounted.

Continuing to reinstate database “duts” …

Reinstatement of database “duts” succeeded

DGMGRL>

If flashback database is not enabled, you would have to manually recreate duts as a standby. The basic process is the reverse of what you did previously.

# 1) Cleanup the old instance.

sqlplus / as sysdba <<EOF

SHUTDOWN IMMEDIATE;

EXIT;

EOF

rm -Rf /u01/data/duts/*

rm -Rf /u01/app/oracle/fast_recovery_area/duts

rm -Rf /u01/app/oracle/fast_recovery_area/duts_stby

rm -Rf /u01/app/oracle/admin/duts

mkdir -p /u01/app/oracle/fast_recovery_area/duts

mkdir -p /u01/app/oracle/admin/duts/adump

mkdir -p /u01/data/duts/pdbseed

mkdir -p /u01/data/duts/pdb1

rm $ORACLE_HOME/dbs/spfileduts.ora

export ORACLE_SID=duts

sqlplus / as sysdba <<EOF

STARTUP NOMOUNT PFILE=’/tmp/initduts_stby.ora’;

EXIT;

EOF

# 2) Connect to RMAN.

$ rman TARGET sys/oracle@duts_stby AUXILIARY sys/oracle@duts

# 3) Duplicate the database.

DUPLICATE TARGET DATABASE

  FOR STANDBY

  FROM ACTIVE DATABASE

  DORECOVER

  SPFILE

    SET db_unique_name=’duts’ COMMENT ‘Is standby 19c’

    SET db_file_name_convert=’/u02/data/duts/’,’/u01/data/duts/’

    SET log_file_name_convert=’/u02/data/duts/’,’/u01/data/duts/’

    SET job_queue_processes=’0′

  NOFILENAMECHECK;

# 4) Connect to DGMDRL on the current primary.

$ dgmgrl sys/oracle@duts_stby

# 5) Enable the new standby.

DGMGRL> ENABLE DATABASE duts;

 

Flashback Database

It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database  once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.

An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database, as shown above.

 

 

Creation of application services

To facilitate the administration of client connections, and to make SWITCHOVER operations more transparent for clients, it is recommended to create database SERVICES.

Example, service definition « DUTSS » :

begin DBMS_SERVICE.CREATE_SERVICE ( service_name => ‘DUTSS’,

                                      network_name => ‘ DUTSS ‘,

                                      failover_method => ‘BASIC’,

                                      failover_type => ‘SELECT’,

                                      failover_retries => 180,

                                      failover_delay => 1);

end;

/

In this case, there are 180 retries and a delay of 1 second (so basically 3 minutes before switching).  This should be adapted depending on your needs and requirements.

These are the services that should be used by client application connections.

 

Creating the Startup trigger

To manage the automatic start of the services, in particular in the event of a role transition, the following TRIGGER must be created (example for the DUTSS service). The trigger must be created under SYS:

Connect SYS as SYSDBA

CREATE OR REPLACE TRIGGER manage_app_services

   AFTER STARTUP

   ON DATABASE

DECLARE

   role   VARCHAR (30);

BEGIN

   SELECT   DATABASE_ROLE INTO role FROM V$DATABASE;

   IF role = ‘PRIMARY’

   THEN

      DBMS_SERVICE.START_SERVICE (‘DUTSS’);

   END IF;

END;

/

Then we restart the PRIMARY database to check that the service is started:

sqlplus / as sysdba

shutdown immediate ;

startup

 

Client connections

To make the role transitions (as a result of a SWITCHOVER or FAILOVER)   transparent to users the client database connection string needs to be configured with a failover connection string.

This can be configured at the TNSNAMES.ORA file level by configuring two addresses or two descriptions for the same alias.

Example of an alias defined for DUTS:

DUTS =

  (DESCRIPTION =

    (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg1)(PORT = 1521))

     (ADDRESS = (PROTOCOL = TCP)(HOST = duts-dg2)(PORT = 1521))

        )

    (CONNECT_DATA = (SERVICE_NAME = DUTSS))

  )    

With this method, clients will attempt to first connect to the first address (corresponding to the primary server and database). If it is operational (which should be the case in a nominal way), the connection is established with this base.

If this first address does not respond (primary server unavailable or services stopped), then the client tries to connect to the second address (pointing to the standby server). If it is operational (which will be the case only after a SWITCHOVER or a FAILOVER), then the client will connect to the emergency base transparently and automatically.

Hope this helps. See you !!!
André  Ontalba  – http://www.dbadutra.com


New Feature 19C – AutoUpgrade
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 5 years ago Comments: 0
New Feature 19C – AutoUpgrade

 

Oracle Database 19c on premises is available and one of the new features is the AutoUpgrade utility.
 
What is the AutoUpgrade?
 
The Oracle Database AutoUpgrade utility is a new tool which allows you to upgrade your databases in an unattended way.
The idea of the tool is to run the prechecks against multiple databases, fix of the potential issues, set a restore point in case something goes wrong and then upgrade your databases.
Of course, do the postupgrade, recompilation and time zone adjustment. The only thing you need to provide is a config file in text format.
 
Which database releases are supported?
According to the MOS Note: 2485457.1 – AutoUpgrade Tool only the versions below are supported:
Oracle Database 19.3.0 and newer
Oracle Database 18.5.0 and newer
Oracle Database 12.2.0.1 with Jan 2019 RU and newer

Where do you get the AutoUpgrade?
You get it when you install Oracle Database 19c (19.3) or you download the most recent version from MOS Note: 2485457.1 – AutoUpgrade Tool:

Where do you find the AutoUpgrade documentation?
It is all here included in the Oracle Database 19c Upgrade Guide:
Using AutoUpgrade for Oracle Database Upgrades
Hope this helps !!!
Soon we will have more articles on the Oracle 19c.
Andre Ontalba – www.dbadutra.com

1 6 7 8