Database
How to Upgrade Oracle Restart from 12.1 to 12.2 – (Bug 26554987)
Category: Database Author: Leonardo Santos Lopes Date: 6 years ago Comments: 0

How to Upgrade Oracle Restart from 12.1 to 12.2 – (Bug 26554987)

Hi, everyone.
Today in this article I will show you, how to upgrade Oracle Grid Infrastructure for Standalone without ASM instance (Oracle Restart).
Please find below the required steps to accomplish this task:
Prerequisites:
  1.  Download Oracle Grid Infrastructure 12.2.
  2.  Download latest or desired RU (Release Update).
  3.  Download latest OPatch version compatible with your current release.
Bug:
I came across a bug during the “gridSetup.sh” related to the upgrade option:
“Oracle Restart: Grid Infrastructure Upgrade from 12.1 to 12.2 Failed with ORA-01078 ORA-29701 CLSRSC-164 message while ASM was not in use (Doc ID 2369909.1)”
 
It happens if you are upgrading the Grid Infrastructure (Oracle Restart only) from 11.2 or 12.1 to 12.2 or beyond.
 
There is a bug fix for it: “Bug 26554987 fixed in 18.1. Apply patch 26554987 on 12.2 home and re-execute rootupgrade.sh”
 
In my case, I did not had the chance to find the bug fix to the platform I am working for: “Oracle Solaris Sparc x64.”
To solve it, we are going to apply the Release Update “12.2.0.1.190416” before run the gridSetup.sh.

Oracle Restart 12.2 installations steps:
 
  1.  unzip the Oracle Grid Infrastructure to the new ORACLE_HOME:
  • export ORACLE_HOME=/oracle/app/grid_122
  • cd /oracle/iso/
  • unzip Oracle_Grid_12.2.0.1_V840013-01.zip -d $ORACLE_HOME
  1.  unzip the new OPatch to the new ORACLE_HOME:
  • export ORACLE_HOME=/oracle/app/grid_122
  • cd $ORACLE_HOME
  • mv OPatch OPatch_old
  • unzip /oracle/iso/OPatch_p6880880_122010_SOLARIS64.zip -d $ORACLE_HOME
  1.  unzip the Release Update Patch:
  • cd /oracle/iso/
  • unzip GI_APR_2019_Release_Update_12.2.0.1.190416_p29301687_122010_SOLARIS64.zip
  1.  Apply the Release Update before complete the Grid Infrastructure Upgrade: (As grid user)
  • export ORACLE_HOME=/oracle/app/grid_122
$ORACLE_HOME/gridSetup.sh -applyPSU /oracle/iso/29301687
PS: DO NOT EXECUTE root.sh or rootupgrade.sh before follow up this procedure, only after apply the steps below.
e.g.:
-bash-4.4$ $ORACLE_HOME/gridSetup.sh -applyPSU /oracle/iso/29301687
Preparing the home to patch…
Applying the patch /oracle/iso/29301687…
Successfully applied the patch.
The log can be found at: /oracle/app/oraInventory/logs/GridSetupActions2019-05-28_03-07-45PM/installerPatchActions_2019-05-28_03-07-45PM.log
Launching Oracle Grid Infrastructure Setup Wizard…
 
  1. When the patching process is done, it automatically calls the installation wizard.
Please find below the installation process through installation wizard:
 
 
  1. Done, installation successfully.
 
 
 
 Leonardo Santos Lopes –  https://leonardosantoslopes.wordpress.com/

Monitoring Data Guard operation
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 6 years ago Comments: 0

Monitoring Data Guard operation

As we saw in the last article on installing DataGuard in Oracle 19C, we will now see how to monitor Data Guard.
 
 
— This script is to be run on the Standby of a Data Guard Physical Standby Site

 

set echo off
set feedback off
column timecol new_value tstamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol from sys.dual;
column output new_value dbname
select value || '_' output from v$parameter where name = 'db_name';
— Output the results to this file
spool dg_Standby_diag_&&dbname&&tstamp
set lines 132
set pagesize 500
set numformat 999999999999999
set trim on
set trims on
— Get the current Date
set feedback on
select systimestamp from dual;

-- Standby Site Details
set heading off
set feedback off
select 'Standby Site Details' from dual;
select '********************' from dual;
set heading on
set feedback on

col db_unique_name format a15
col flashb_on format a10

select DB_UNIQUE_NAME,DATABASE_ROLE DB_ROLE,FORCE_LOGGING F_LOG,FLASHBACK_ON FLASHB_ON,LOG_MODE,OPEN_MODE,
GUARD_STATUS GUARD,PROTECTION_MODE PROT_MODE
from v$database;
— Current SCN – this value on the primary and standby sites where real time apply is in place should be nearly the same

 

select DB_UNIQUE_NAME,SWITCHOVER_STATUS,CURRENT_SCN from v$database;

-- Incarnation Information
--

set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on

select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;

set heading off
set feedback off
select 'Archive Destination Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on
-- Current Archive Locations
--

column host_name format a30 tru
column version format a10 tru
select INSTANCE_NAME,HOST_NAME,VERSION,ARCHIVER from v$instance;

column destination format a35 wrap
column process format a7
column archiver format a8
column dest_id format 99999999

select DEST_ID,DESTINATION,STATUS,TARGET,ARCHIVER,PROCESS,REGISTER,TRANSMIT_MODE
from v$archive_dest
where DESTINATION IS NOT NULL;

column name format a22
column value format a100
select NAME,VALUE from v$parameter where NAME like 'log_archive_dest%' and upper(VALUE) like 'SERVICE%';

set heading off
set feedback off
select 'Archive Destination Errors' from dual;
select '**************************' from dual;
set heading on
set feedback on

column error format a55 tru
select DEST_ID,STATUS,ERROR from v$archive_dest
where DESTINATION IS NOT NULL;

column message format a80
select MESSAGE, TIMESTAMP
from v$dataguard_status
where SEVERITY in ('Error','Fatal')
order by TIMESTAMP;

-- Redo Log configuration
-- The size of the standby redo logs must match exactly the size on the online redo logs

set heading off
set feedback off
select 'Data Guard Redo Log Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on

select GROUP# STANDBY_GROUP#,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS from v$standby_log order by GROUP#,THREAD#;

select GROUP# ONLINE_GROUP#,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log order by GROUP#,THREAD#;
— Data Guard Parameters

set heading off
set feedback off
select 'Data Guard Related Parameters' from dual;
select '*****************************' from dual;
set heading on
set feedback on

column name format a30
column value format a100
select NAME,VALUE from v$parameter where NAME IN ('db_unique_name','cluster_database','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','fal_client','fal_server','log_archive_config','log_archive_trace','log_archive_max_processes','archive_lag_target','remote_login_password_file','redo_transport_user') order by name;
— Managed Recovery State

 

set heading off
set feedback off
select 'Data Guard Apply Status' from dual;
select '***********************' from dual;
set heading on
set feedback on

select systimestamp from dual;

column client_pid format a10
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

host sleep 10

select systimestamp from dual;

select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

host sleep 10

select systimestamp from dual;

select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

set heading off
set feedback off
select 'Data Guard Apply Lag' from dual;
select '********************' from dual;
set heading on
set feedback on

column name format a12
column lag_time format a20
column datum_time format a20
column time_computed format a20
SELECT NAME, VALUE LAG_TIME, DATUM_TIME, TIME_COMPUTED
from V$DATAGUARD_STATS where name like 'apply lag';
— If there is a lag remove the comment for the select below

 

-- SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' AND COUNT > 0;

set heading off
set feedback off
select 'Data Guard Gap Problems' from dual;
select '***********************' from dual;
set heading on
set feedback on

select * from v$archive_gap;

set heading off
set feedback off
select 'Data Guard Errors in the Last Hour' from dual;
select '**********************************' from dual;
set heading on
set feedback on

select TIMESTAMP,SEVERITY,ERROR_CODE,MESSAGE from v$dataguard_status where timestamp > systimestamp-1/24;
spool off

 



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

 Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

 


Query the last patch applied on database 11g, 12cR1, 12cR2 and 18c
Category: Database Author: Rodrigo Mufalani (Board Member) Date: 6 years ago Comments: 0

Query the last patch applied on database 11g, 12cR1, 12cR2 and 18c

 
 
Part of DBA routine is patch databases by quarterly, what is that means? 
 
It means that Oracle is a software and it is hard to hear, but Oracle database has bugs and secutiry vulnerabilities, so is good idea patch regularly your production databases with RU and RUR, you can
find more information about this change on nomenclature that occurred on 12cR2 at this of link on blog of the master https://mikedietrichde.com/2017/11/07/ru-rur-recommendations-facts/
So, usually a patch came with 2 different parts, first part is the binaries (libraries, programs and etc), you must be familiar with opatch utility to apply a patch on the Oracle Home, and the second part is the data dictionary corrections. 
To execute this part, since Oracle version 12cR1 we use datapatch to apply, this tool is amazing, really like to use it. So, the datapatch utility, is part of OPatch and the purpose of usage of this tool is apply scripts against Oracle data dictionary.
In this article I will show to you, how to query the correct views on the database to find informations about 11g, and 12c (family) databases, let’s say like that.
 
So, on version 11g, after apply a PSU patch on database, after run the script catbundle.sql, you can query this view with SYS account, to check information about the past PSUs applied:

— 11g version

set lin 1000
col action form a12
col version form a40
col comments form a70
col action_date form a20

select comments, action, to_char(action_time,’DD/MM/RR HH24:MI:SS’) action_date, version
from registry$history
order by action_date;

COMMENTS ACTION ACTION_DATE VERSION
———————————————————————- ———— ——————– —————————————-
Patch applied from 18.1.0.0.0 to 18.3.0.0.0 RU_APPLY 20/03/19 14:36:26 18.0.0.0.0
OJVM RU post-install APPLY 20/03/19 14:37:42 18.3.0.0.180717OJVMRU
RAN jvmpsu.sql jvmpsu.sql 20/03/19 14:37:42 18.3.0.0.180717OJVMRU
RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627 BOOTSTRAP 18

 

For the version 12R1 and onwards you will need to use another view and depending against wich version are running you can filter by PSU ou RELEASE words on column description:
 

— For 12c and 18c
set lin 1000
col action form a12
col version form a40
col description form a85
col action_date form a20

select description, action, to_char(action_time,’DD/MM/RR HH24:MI:SS’) action_date, ‘ ‘ version
from dba_registry_sqlpatch;

DESCRIPTION ACTION ACTION_DATE VERSION
————————————————————————————- ———— ——————– —————————————-
Database Release Update : 18.3.0.0.180717 (28090523) APPLY 20/03/19 14:37:46
OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415) APPLY 20/03/19 14:37:46

 

Obs.: There is no more version column, I keep that just for have compatibility with some old scripts.
 

To query:
— 12cR1
where upper(description) like ‘DATABASE PSU%’;

— 12cR2 onwards
where upper(descrition) like ‘DATABASE RELEASE%’;

 

I hope this brief note can help you to find more details how to query and check if the version applied on binaries matches the version of the database level of patch.
 

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”

All the best,
Rodrigo Mufalani

Generate dinamically commands to add the services in GI
Category: Database Author: Rodrigo Mufalani (Board Member) Date: 6 years ago Comments: 0

Generate dinamically commands to add the services in GI

 
During one migration activity we must copy all defined services associated with pluggable database from one cluster to another one. This task is not dificult, you can list the service in one side and add the services on your new cluster. But, we are humans and we can forget to add some service or even occurs some types during the creation of services, so, to be more accurate and avoid issues we decided to write a small shell script, I called gen_add_service.sh to add generate dinamically commands to add the services from the source GI to destination GI.

 

 

 
 
 
The script by itself is not rocket science, but some tricks can help you to solve some other  difficulties that I have during the construction of this small piece of code.
 
To reproduce this environment, I will create some services to PDB associated with it.
 
 

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb1_svc1 -pdb pdb1

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb1_svc2 -pdb pdb1

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb1_svc3 -pdb pdb1

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb2_svc1 -pdb pdb2

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb2_svc2 -pdb pdb2

[oracle@ora18c3 ~]$ srvctl add service -d db01 -s pdb2_svc3 -pdb pdb2

As you probably know, when you create the services you need to start them after..
 

[oracle@ora18c3 ~]$ srvctl status service -d db01

Service pdb1_svc1 is not running.

Service pdb1_svc2 is not running.

Service pdb1_svc3 is not running.

Service pdb2_svc1 is not running.

Service pdb2_svc2 is not running.

Service pdb2_svc3 is not running.

 

 Now, let’s start the services, after the creation…
 

 [oracle@ora18c3 ~]$ srvctl start service -s pdb1_svc1 -d db01

[oracle@ora18c3 ~]$ srvctl start service -s pdb1_svc2 -d db01

[oracle@ora18c3 ~]$ srvctl start service -s pdb1_svc3 -d db01

[oracle@ora18c3 ~]$ srvctl start service -s pdb2_svc3 -d db01

[oracle@ora18c3 ~]$ srvctl start service -s pdb2_svc2 -d db01

[oracle@ora18c3 ~]$ srvctl start service -s pdb2_svc1 -d db01

 

 

Now, after start, let’s check the service again..

 

[oracle@ora18c3 ~]$ srvctl status service -d db01

Service pdb1_svc1 is running

Service pdb1_svc2 is running

Service pdb1_svc3 is running

Service pdb2_svc1 is running

Service pdb2_svc2 is running

Service pdb2_svc3 is running     

 

When we check the service, I will just pickup one, as an example to analyze it

[oracle@ora18c3 ~]$ srvctl config service -d db01 -s pdb1_svc1

Service name: pdb1_svc1

Cardinality: SINGLETON

Service role: PRIMARY

Management policy: AUTOMATIC

DTP transaction: false

AQ HA notifications: false

Global: false

Commit Outcome: false

Failover type:

Failover method:

Failover retries:

Failover delay:

Failover restore: NONE

Connection Load Balancing Goal: LONG

Runtime Load Balancing Goal: NONE

TAF policy specification: NONE

Edition:

Pluggable database name: pdb1

Hub service:

Maximum lag time: ANY

SQL Translation Profile:

Retention: 86400 seconds

Replay Initiation Time: 300 seconds

Drain timeout:

Stop option:

Session State Consistency: DYNAMIC

GSM Flags: 0

Service is enabled

 

Now, let me explain the issues that I’ve got to wrote this script:
 

for SERVICE in `srvctl status service -d ${DBNAME} | awk -F” ” ‘{print $2}’`

do

   srvctl config service -d ${DBNAME} -s ${SERVICE} | grep “Service name\|Pluggable database name” | column -c 500 >> /tmp/pdbs.txt

done

 

cat /tmp/pdbs.txt | awk -F” ” ‘{print “srvctl add service -d ‘$DBNAME’ -s ” $3 ” -e SELECT -m BASIC -j LONG -P BASIC -pdb ” $7 ” -r ‘$DBNAME’1 -a ‘$DBNAME’2″}’

 

1 ) To list the service names, we used srvctl status serice -d <<DBNAME>> and then used srvctl config service to detailed output for each service.

 

Solution: Used a for to list and then grep all services and pdb names.
 
2) The lines Service Name and Pluggable database name are in separated lines on output.

 

Solution: To solve this, I user grep  “Service name\|Pluggable database name”
 
3) The output using grep with multiple values break the lines and I need to put all outputs on same line.

 

Solution: Used the column command to aggregate the output in just one line
 
4) Even with the aggregation, after some characters the output of my file /tmp/pdbs.txt got wrong results due to break in the line.

 

Solution: Used column -c 500
 
5) The last obstacle was the awk to print linux variables

 

Solution: To solve this and print variables is just use single quotes on variable names and with the command that I need to print at the screen.

 

[oracle@ora18c3 ~]$ sh gen_add_service.sh

Enter dbname to generate add services cmd

db01

srvctl add service -d db01 -s  -e SELECT -m BASIC -j LONG -P BASIC -pdb  -r db011 -a db012

srvctl add service -d db01 -s pdb1_svc1 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb1 -r db011 -a db012

srvctl add service -d db01 -s pdb1_svc2 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb1 -r db011 -a db012

srvctl add service -d db01 -s pdb1_svc3 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb1 -r db011 -a db012

srvctl add service -d db01 -s pdb2_svc1 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb2 -r db011 -a db012

srvctl add service -d db01 -s pdb2_svc2 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb2 -r db011 -a db012

srvctl add service -d db01 -s pdb2_svc3 -e SELECT -m BASIC -j LONG -P BASIC -pdb pdb2 -r db011 -a db012

 

I just put on the script whatever I wanted to formula the desired output to add my services on the new cluster. In my laptop I don’t have the RAC to add the services with multiple instances, but I hope that you got the idea and maybe use the tricks that I used to solve issues on your environment.

 

I hope this script help you on your next clone.
 
Best Regards,
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: 6 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: 6 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: 6 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 … 5 6 7
LUXOUG – LUXEMBOURG ORACLE USERS GROUP
© 2024 LUXOUG – LUXEMBOURG ORACLE USERS GROUP