Monthly Archives: October 2019

pagination and python 3.8 assignment expressions in AWS boto3

Posted on by
Reply

By Brian Fitzgerald

Introduction

AWS query operations return their results all at once, or in smaller, manageable pages. boto3 provides the programmer with to ways to manage the retrieval of paginated results.

Python version 3.8 introduces assignment expressions, a way to assign to variables within an expression using the notation := expr. PEP-572 explains how assignment expressions are useful in control statements, such as if and while, and in comprehensions. Assignment expressions can be used to improve coding style in pagination code.

describe

To query an Amazon AWS service, one calls an API using a procedure with a name beginning with “describe”, or sometimes “list” or “get”. The result is a dictionary with at least two elements, namely the payload, and ResponseMetadata. If more results are pending, NextToken will appear. The payload is your query result and consists of an array of objects. Examples of payload data include EC2 instance Reservations, EC2 EBS Volumes, and RDS DBInstances.

Using python API boto3, you can write:

import boto3
ec2 = boto3.client('ec2')
resp = { 'NextToken' : '' }
while 'NextToken' in resp:
    resp = ec2.describe_instances(
        NextToken = resp['NextToken']
    )
    for resv in resp['Reservations']:
        for inst in resv['Instances']:
            print ( inst['InstanceId'])

resp is a dict, and appears four times in the above code:

  • initialization with a dummy NextToken
  • returned from describe_instances
  • used to get describe_instances argument NextToken
  • used to get Reservations

The dictionary keys are Reservations, ResponseMetadata, and when needed, NextToken.

python 3.8 assignment expression

In python 3.8, we can assign to a variable, and then use the assignment as an expression. Specifically, we can assign resp, and access element Reservations.

import boto3
ec2 = boto3.client('ec2')
resp = { 'NextToken' : '' }
while 'NextToken' in resp:
    for resv in (resp := ec2.describe_instances(
        NextToken = resp['NextToken']
    ))['Reservations']:
        for inst in resv['Instances']:
            print ( inst['InstanceId'])

There is one fewer line. Some may find the revised code appealing.

paginator

AWS boto3 provides paginators, python iterators, that handle NextToken for you.

import boto3
ec2 = boto3.client('ec2')
pagr = ec2.get_paginator('describe_instances')
for page in pagr.paginate():
    for resv in page['Reservations']:
        for inst in resv['Instances']:
            print( inst['InstanceId'])

There are two fewer lines. NextToken does not appear in the code.

Paginate returns the same results as the prior “describe” type methods. The returned value is a dict, again with keys Reservations, ResponseMetadata, and when needed, NextToken. However, the returned NextToken has no role in the new code.

paginator relationship to API call

A debug trace of describe_instances() shows an underlying call to client.py, line 357, function _api_call().

(Pdb) where
  /usr/lib64/python3.7/bdb.py(585)run()
-> exec(cmd, globals, locals)
  (1)()
  /home/ec2-user/git/aws/insts.desc.3.7.py(8)()
-> NextToken = resp['NextToken']
  /usr/local/lib/python3.7/site-packages/botocore/client.py(357)_api_call()
-> return self._make_api_call(operation_name, kwargs)

Debug trace of paginate approach shows a call to the same point via paginate.py.

(Pdb) where
  /usr/lib64/python3.7/bdb.py(585)run()
-> exec(cmd, globals, locals)
  (1)()
  /home/ec2-user/git/aws/insts.page.3.7.py(6)()
-> for page in pagr.paginate():
  /usr/local/lib/python3.7/site-packages/botocore/paginate.py(255)__iter__()
-> response = self._make_request(current_kwargs)
  /usr/local/lib/python3.7/site-packages/botocore/paginate.py(332)_make_request()
-> return self._method(**current_kwargs)
  /usr/local/lib/python3.7/site-packages/botocore/client.py(357)_api_call()

You can see where paginate is handling NextToken for you. For example, this code:

NextToken = resp['NextToken']

is handled behind the scenes as:

> /usr/local/lib/python3.7/site-packages/botocore/paginate.py(303)__iter__()
-> previous_next_token = next_token
(Pdb) p next_token
{'NextToken': 'eyJ2IjoiMiIsImMiOiJ5eHV0K011N2crQlBBaFhoSWU2SUpad0c3V3VaUFBvKzBPbDRIWFAvaXJSb3poeDFDNks3TkxGMkU5R1UxRjk4UlVnNFViRzNjSUlWWXhMbHk3ejU1Qjd1ZGhERHNBVktCR1g0cW5RZk9FdStZckViM0NjOXljV1p0SWplckhkV2ZISkNvc0NXdjhnMXA4RVBMWDFiVzNkS3k1NW5CdlZmUlhWUEpzeUZNbnhMS3VzdEo4eHFIWHRYNytpcEdWbHJKMFRqTlNLQ3A0Rk9VaEZGckdBMTVOYU44WGhvYkYyZVBBYjRrMVVaYXNFTCIsInMiOiIxIn0='}

The describe and paginate approaches are functionally equivalent. Neither one has a performance advantage. Using paginate leads to cleaner code.

limiting the results (cloud side)

You can limit the number of items returned thus:

...
for page in pagr.paginate(PaginationConfig={'MaxItems': 250}):
...

In that case, the total number of items returned will be at most 250. The limiting is done on the cloud side. The items could be returned across more than one page.

limiting the results (client side)

Suppose you want all the ec2 instance types that have memory less than or equal to 8 GB. Two problems:

  1. AWS pricing has no filter for memory.
  2. The AWS pricing filter has no “less than” operator.

In that case, you need to retrieve your products, and apply additional filtering in client side code.

#!/home/ec2-user/sw/python/3.8/bin/python3.8

from boto3 import client
from pfilt import Pfilt
from json import dumps, loads


class ResultsLimitExceededError(Exception):
    def __init__(self, limit):
        fmt = format('results limit %s exceeded')
        msg = (fmt % limit)
        super(ResultsLimitExceededError, self).__init__(msg)


class Prices:

    @classmethod
    def instancetypes(cls):
        cli = client('pricing')
        pag = cli.get_paginator('get_products')

        max_products_in = 1000
        max_products_out = 100
        num_products_in = 0
        num_products_out = 0
        for page in pag.paginate(
                ServiceCode='AmazonEC2',
                Filters=Pfilt.filters,
                PaginationConfig={'MaxItems': max_products_in}
        ):
            print('page size %s' % len(page['PriceList']))
            for skitem in [loads(itm) for itm in page['PriceList']]:
                num_products_in += 1
                if (filtered_skitem := cls.client_filter(cls.enrich(skitem))) is not None:
                    if (num_products_out := num_products_out + 1) > max_products_out:
                        raise ResultsLimitExceededError(max_products_out)
                    cls.processitm(filtered_skitem)

        print('number of products in %s' % num_products_in)
        print('number of products out %s' % num_products_out)

    @classmethod
    def enrich(cls, skitem):
        eskitem = skitem
        product = skitem['product']
        memGB = float(product['attributes']['memory'].split()[0].replace(',', ''))
        eskitem['memGB'] = memGB
        return eskitem

    @classmethod
    def client_filter(cls, eskitem):
        maxmem = 8
        return eskitem if eskitem['memGB'] <= maxmem else None

    @classmethod
    def processitm(cls, skitem):
        pass


if __name__ == '__main__':
    Prices.instancetypes()

In this case, we set a cloud-side limit of 1000 records, and a client-side limit of 100 records.

Example output:

$ ./prices.pag.py
page size 100
page size 100
page size 39
number of products in 239
number of products out 33

You could manage your client-side memory by reducing the page size:

PaginationConfig={'MaxItems': max_products_in, 'PageSize': 50}

Setting PageSize does not affect the results. The output, then, is:

page size 50
page size 50
page size 50
page size 50
page size 39
number of products in 239
number of products out 33

To demonstrate the exception, we can change:

max_products_out = 20

in that case, we get:

$ ./prices.pag.py
Traceback (most recent call last):
  File "./prices.pag.py", line 62, in 
    Prices.instancetypes()
  File "./prices.pag.py", line 36, in instancetypes
    raise ResultsLimitExceededError(max_products_out)
__main__.ResultsLimitExceededError: results limit 20 exceeded

We used the new Python 3.8 assignment expression in two places:

if (fskitem := cls.client_filter(cls.enrich(skitem))) is not None:
    if (num_products_out := num_products_out + 1) > max_products_out:

We assign fskitem and then test it. Likewise, we assign num_products_out and test it.

pagination availability

Not all AWS operations can be paginated — it depends on the service. If an operation cannot be paginated, it will not return NextToken. If an operation cannot be paginated, you may have issues managing the results. You can use this script to find out if an operation can be paginated.

#!/usr/bin/python

from boto3 import client
from argparse import ArgumentParser


class CanPag:
    args = None

    @classmethod
    def prs(cls):
        ap = ArgumentParser(
            description='Check whether an operation can paginate'
        )
        ap.add_argument(
            '--service', '-s', required=True,
            help='AWS service name (ec2, s3, pricing, etc.)'
        )
        ap.add_argument(
            '--operation', '-o', required=True,
            help='AWS service operation (describe_instances, etc.)'
        )

        cls.args = ap.parse_args()

    @classmethod
    def canpage(cls):
        cli = client(cls.args.service)
        cp = cli.can_paginate(cls.args.operation)
        print(
            '%s %s %s paginate.' % (
                cls.args.service,
                cls.args.operation,
                'can' if cp else 'cannot')
        )


if __name__ == '__main__':
    CanPag.prs()
    CanPag.canpage()

Examples:

$ ./can.paginate.py -s ec2 -o describe_instances
ec2 describe_instances can paginate.
$ ./can.paginate.py -s ec2 -o describe_volumes
ec2 describe_volumes can paginate.
$ ./can.paginate.py -s ec2 -o describe_images
ec2 describe_images cannot paginate.
$ ./can.paginate.py -s pricing -o get_products
pricing get_products can paginate.

Conclusion

I wrote this article for two reasons: To identify a good paginator coding practice, and to try out the new Python 3.8 assignment expression.

AWS queries are often returned in chunks, or pages. You could write your own code to manage the retrieval, but you are better off using the provided paginator. You can configure the paginator as to and MaxItems and PageSize. Use can_paginate() to find out which operations can be paginated.

Python version 3.8 assignment expression was demonstrated. Three cases were presented. In each case, the pattern was “assign and test”. The assigned variables in the examples were resp, filtered_skitem, and num_products_out. The assigned value was needed elsewhere in the routine.

Data Guard broker 19c configuration export

Posted on by
Reply

By Brian Fitzgerald

Introduction

Oracle Database 19c Data Guard broker has a new export/import feature. You can use export to backup your Data Guard configuration and you can use import to recreate your configuration.

Export

Issue a command such as:

DGMGRL> export configuration to 'dg.xml';
Succeeded.

Find your configuration file in diagnostic_dest

/u01/app/oracle/diag/rdbms/prim/PRIM/trace/dg.xml

You cannot specify a directory or a relative path.

DGMGRL> export configuration to '/home/oracle/dg.xml';
ORA-16540: invalid argument
ORA-06512: at "SYS.DBMS_DRS", line 1947
ORA-06512: at line 1

DGMGRL> export configuration to '../trace/dg.xml';
ORA-16540: invalid argument
ORA-06512: at "SYS.DBMS_DRS", line 1947
ORA-06512: at line 1

dgmgrl will not overwrite an existing file

DGMGRL> export configuration to 'dg.xml';
ORA-16571: Oracle Data Guard configuration file creation failure

You may export at the standby. The export file appears in the standby trace directory.

/u01/app/oracle/diag/rdbms/stby/STBY/trace/dg.xml

Contents

Refer, for example, to this link dg.xml. The export file contains:

  • Identity and role of members
  • Default, desired, and current state, including error number and timestamp
  • System and member configuration parameters

Some parameters that are at the default value and have never been modified are not exported.

Only the error number is exported. The error text depends on localization.

[oracle@ip-172-31-88-93 ~]$ export NLS_LANG=French
[oracle@ip-172-31-88-93 ~]$ dgmgrl sys/Zystm.22@PRIM
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Ven. Oct. 18 18:49:58 2019
Version 19.3.0.0.0

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

Bienvenue dans DGMGRL, tapez "help" pour obtenir des informations.
Connecté à "PRIM"
Connecté en tant que SYSDBA.
DGMGRL> show configuration

Configuration - ORCL_CONFIG

  Mode de protection : MaxPerformance
  Membres :
  PRIM - Base de données principale
    Erreur : ORA-16778: erreur du Redo Transport Service pour des membres

    STBY - Base de données de secours physique
      Erreur : ORA-12543: TNS : hôte de destination impossible à atteindre

Fonction Fast-Start Failover :  Disabled

Statut de configuration :
ERROR   (statut mis à jour il y a 34 secondes)

Import

Here is how to use import.

If there is a configuration, disable and remove it. All members must be accessible.

DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration
Removed configuration

Import and enable the configuration

DGMGRL> import configuration from 'dg.xml';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.
DGMGRL> enable configuration
Enabled.

Data Guard broker will not import over an existing configuration

DGMGRL> import configuration from 'dg.xml';
ORA-16504: The Oracle Data Guard broker configuration already exists.

Result:

DGMGRL> show configuration

Configuration - ORCL_CONFIG

  Protection Mode: MaxPerformance
  Members:
  PRIM - Primary database
    STBY - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 4 seconds ago)

You can change the export file before reimporting.

Before:

DGMGRL> show database verbose PRIM NetTimeout
  NetTimeout = '30'

File:

tag

Result:

DGMGRL> remove configuration
Removed configuration
DGMGRL> import configuration from 'dg.xml';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.
DGMGRL> show database verbose PRIM NetTimeout
  NetTimeout = '10'

You cannot split the Data Guard xml file into pieces and import them separately.

DGMGRL> import configuration from 'prim.xml';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.
DGMGRL> import configuration from 'stby.xml';
ORA-16504: The Oracle Data Guard broker configuration already exists.
DGMGRL>

You must import to the primary. If you attempt to import to the standby, this message will appear:

DGMGRL> import configuration from 'dg.xml';
ORA-16501: The Oracle Data Guard broker operation failed.

The trace file offers no clue about what could be the problem.

2019-10-18T11:19:37.866+00:00
IMPORT METADATA FROM /u01/app/oracle/diag/rdbms/stby/STBY/trace/dg.xml
ORA-16501: The Data Guard broker failed to import metadata from /u01/app/oracle/diag/rdbms/stby/STBY/trace/dg.xml.
IMPORT METADATA FROM /u01/app/oracle/diag/rdbms/stby/STBY/trace/dg.xml completed with error ORA-16501

Management of an unreachable member

If you remove a Data Guard configuration while a member is down, you will have an issue later if you try to import. The following administrative sequence that could lead up to the trouble:

DGMGRL> export configuration to 'dg.xml';
Succeeded.

Now, suppose the standby is down

DGMGRL> validate database STBY
Error: ORA-1034: ORACLE not available
Error: ORA-16625: cannot reach member "STBY"
DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration 
Warning: ORA-16620: one or more members could not be reached for a remove operation Removed configuration
DGMGRL> import configuration from 'dg.xml';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.

Now, if you enable even while the standby is up:

DGMGRL> enable configuration
Enabled.

Error appears

DGMGRL> show configuration

Configuration - ORCL_CONFIG

  Protection Mode: MaxPerformance
  Members:
  PRIM - Primary database
    STBY - Physical standby database (disabled)
      ORA-16904: Member may be a primary database or is receiving redo data from another primary database.

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 28 seconds ago)

Solution: On the standby disable the broker, remove the Data Guard Broker configuration files, and reenable the broker.

SQL> alter system set dg_broker_start=false;

System altered.

[grid@ip-172-31-91-148 ~]$ asmcmd rm +DATA01/STBY/dr1orcl.dat
[grid@ip-172-31-91-148 ~]$ asmcmd rm +RECO01/STBY/dr2orcl.dat

SQL> alter system set dg_broker_start=true;

System altered.

Then, on the primary, disable, remove, import, and enable the configuration.

DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration
Warning: ORA-16620: one or more members could not be reached for a remove operation

Removed configuration
DGMGRL> import configuration from 'dg.xml';
Succeeded. Run ENABLE CONFIGURATION to enable the imported configuration.
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - ORCL_CONFIG

  Protection Mode: MaxPerformance
  Members:
  PRIM - Primary database
    STBY - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 34 seconds ago)

The problem scenario described here is not unique to import. The issue can occur any time a configuration is deleted while members are down.

Conclusion

dgmgrl export and import are useful for backing up and restoring the Data Guard configuration.

listener status UNKNOWN or BLOCKED

Posted on by
Reply

By Brian Fitzgerald

Introduction

By trial and error, I identify what Oracle Database instance states correspond to listener status READY, UNKNOWN, and BLOCKED.

Details

I have recorded the listener status for various database states. In some cases, the listener was reloaded or restarted.

Database open READ WRITE:

Service "EQTRD" has 2 instance(s).
  Instance "EQTRD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "EQTRD", status READY, has 1 handler(s) for this service...

Shutdown the database

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status UNKNOWN, has 1 handler(s) for this service...

Delete the static listener and reload the listener.

[none]

Startup nomount

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status BLOCKED, has 1 handler(s) for this service...

Reload

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status BLOCKED, has 1 handler(s) for this service...

Stop and start the listener.

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status BLOCKED, has 1 handler(s) for this service...

Note that reloading or restarting the listener does not clear a BLOCKED state.

Mount

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status READY, has 1 handler(s) for this service...

Startup force nomount

Service "EQTRD" has 1 instance(s).
  Instance "EQTRD", status BLOCKED, has 1 handler(s) for this service...

Re-enable the static listener

Service "EQTRD" has 2 instance(s).
  Instance "EQTRD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "EQTRD", status BLOCKED, has 1 handler(s) for this service...

Mount

Service "EQTRD" has 2 instance(s).
  Instance "EQTRD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "EQTRD", status READY, has 1 handler(s) for this service...

Restricted session

Service "XIENT" has 1 instance(s).
  Instance "XIENT", status RESTRICTED, has 1 handler(s) for this service...

Summary

Here is a summary of listener status and interpretation.

status interpretation
BLOCKED nomount
UNKNOWN static listener
READY mounted or open. Dymamically registered
RESTRICTED restricted session

 

Data Guard broker 19c edit database set parameter feature is unstable

Posted on by
Reply

By Brian Fitzgerald

Introduction

New in Oracle Database 19c, you can set a database initialization parameter directly from dgmgrl, the Data Guard broker command-line interface. The documentation is not clear or the feature is unstable.

Edit database set parameter

The basic functionality works as documented.

DGMGRL> edit database PRIM set parameter pga_aggregate_target='752m';
Parameter "pga_aggregate_target" updated

However:

DGMGRL> edit database PRIM set parameter pga_aggregate_target='753m' sid='PRIM';
edit database PRIM set parameter pga_aggregate_target='753m' sid='PRIM';
                                                                ^
Syntax error before or at "="

DGMGRL> edit database PRIM set parameter sga_target=2256m scope = both;
edit database PRIM set parameter sga_target=2256m scope = both;
                                                        ^
Syntax error before or at "="

Options SID and SCOPE are documented but are not working for me.

Edit database reset parameter

You can use edit database to reset a database initialization parameter. Suppose spfile initially contains:

*.use_large_pages='ONLY'
PRIM.use_large_pages='ONLY'

Now reset.

DGMGRL> edit database PRIM reset parameter use_large_pages;
ORA-16675: database instance restart required for property value modification to take effect

Parameter "use_large_pages" reset
DGMGRL>

This appears in the alert log

2019-10-17T19:43:49.034727+00:00
ALTER SYSTEM RESET use_large_pages SCOPE=SPFILE;

Here is what is remains:

PRIM.use_large_pages='ONLY'

The “sid=’*'” entry is gone. The dgmgrl reset behavior, then, is the same as the alter system syntax with an assumed “sid=’*'”.

The dgmgrl edit database reset parameter command has no sid option.

DGMGRL> edit database PRIM reset parameter use_large_pages sid = 'PRIM';
edit database PRIM reset parameter use_large_pages sid = 'PRIM';
                                                   ^
Syntax error before or at "sid"

Conclusion

The dgmgrl edit database set and reset parameter commands are incomplete or have unexpected behavior today, or I misunderstand them. I will not use them for now.

AutoUpgrade 12c->19c with Transient Logical Standby

Posted on by
Reply

By Brian Fitzgerald

Introduction

Oracle Database 12.1 was upgraded to version 19.3 using the AutoUpgrade utility and the Transient Logical Standby. Actual downtime was less than 10s.

News about AutoUpgrade

For news about AutoUpgrade, please refer Oracle product manager Mike Dietrich’s blog.

System Description

The system is Linux 7.7 on Amazon AWS EC2. Details appear in this table:

Attribute Value
cloud AWS
location US East (N. Virginia)
instanceType m5.large
tenancy shared
instanceFamily General purpose
physicalProcessor Intel Xeon Platinum 8175
clockSpeed 2.5 GHz
Number of CPUs 2
memGB 7.8 GiB
Storage EBS gp2
operatingSystem Linux
AMI RHEL-7.7_HVM_GA-20190723-x86_64-1-Hourly2-GP2
OS Vendor Red Hat, Inc.
Red Hat version 7.7
price per hour $0.096

System Configuration

Initially, the system is a Data Guard network with a physical standby.

Description Value
Original version 12.1.0.2
Target version 19.3.0
Primary EQTRD
Physical Standby XIENT
Instance type Restart
Schema trades

Non-default Database system configurations

Selected non-default instance parameters are:

name value
archive_lag_target 900
audit_file_dest /u01/app/oracle/admin/EQTRD/adump
audit_trail DB
compatible 12.1.0.2.0
control_files +DATA01/EQTRD/CONTROLFILE/current.259.1021685185, +RECO01/EQTRD/CONTROLFILE/current.313.1021685185
db_block_size 8192
db_create_file_dest +DATA01
db_create_online_log_dest_1 +DATA01
db_create_online_log_dest_2 +RECO01
db_name EQTRD
db_recovery_file_dest +RECO01
db_recovery_file_dest_size 30g
dg_broker_config_file1 +DATA01/EQTRD/dr1orcl.dat
dg_broker_config_file2 +RECO01/EQTRD/dr2orcl.dat
dg_broker_start TRUE
diagnostic_dest /u01/app/oracle
local_listener 127.0.0.1:1521
log_archive_config dg_config=(EQTRD,XIENT)
log_archive_dest_1 location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES, ALL_ROLES)
log_archive_dest_2 service=”XIENT”, ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name=”XIENT” net_timeout=30, valid_for=(online_logfile,all_roles)
log_archive_dest_state_2 ENABLE
sga_max_size 1500m
sga_target 1500m
standby_file_management AUTO
use_large_pages ONLY

Existing Data Guard 12c physical standby

We start this article with a working Data Guard physical standby with these issues already handled on the primary and the standby:

  • network route
  • static listeners
  • archivelog
  • flashback on
  • forced logging
  • 3×500 MB online redo log groups
  • 4×500 MB standby redo log groups
  • audit directory
  • data guard broker
  • tested switchover
  • spfile moved to ASM
  • tnsnames.ora in 12c oracle home
  • Restart is setup
  • oratab is configured and pointing to the 12c home
  • srvctl tested

The standby spfile has been moved to ASM so that it will be unnecessary to move it after the upgrade, or a possible downgrade.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA01/XIENT/PARAMETERFILE/spfile.275.1021229119

Existing Data

Example table ordermessage has one row.

SQL> @ insert1.sql

1 row created.

SQL> commit;

Commit complete.
SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK

Preparations

Careful preparation should be completed days ahead of the planned upgrade. AutoUpgrade Analyze and Fixups can be run and the results reviewed. The 19c oracle homes, including tnsnames.ora and orapw should be prepared for the upgrade.

Download AutoUpgrade

Download the latest autoupgrade.jar

Install to the 19c oracle home on primary and standby

[oracle@ip-172-31-91-148 dbhome_1]$ alias oh
alias oh='cd $ORACLE_HOME'
[oracle@ip-172-31-91-148 dbhome_1]$ oh
[oracle@ip-172-31-91-148 dbhome_1]$ find . -name autoupgrade.jar -ls
76988898 3616 -rw-r--r-- 1 oracle oinstall 3702360 Oct 7 20:35 ./rdbms/admin/autoupgrade.jar

Set this alias for convenience

[oracle@ip-172-31-91-148 dbhome_1]$ AH=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ip-172-31-91-148 dbhome_1]$ alias au='rlwrap $AH/jdk/bin/java -jar $AH/rdbms/admin/autoupgrade.jar'

Use rlwrap for CLI readline editing and history recall.

Check the AutoUpgrade version:

[oracle@ip-172-31-91-148 dbhome_1]$ au -version
build.hash 67fee5b
build.version 20190823
build.date 2019/08/23 18:08:47

Source AutoUpgrade configuration file

The source configuration file is eqtrd.12c.19c.conf. Notice that target_home need not appear in the source configuration file.

global.autoupg_log_dir=/u01/app/oracle/autoupgrade

upg1.dbname=EQTRD
upg1.start_time=now
upg1.source_home=/u01/app/oracle/product/12.1.0/dbhome_1
upg1.sid=EQTRD
upg1.log_dir=/u01/app/oracle/autoupgrade
upg1.upgrade_node=ip-172-31-88-93.ec2.internal
upg1.target_version=19.3

Analyze

Use AutoUpgrade to analyze for issues and needed fixups.

If you scroll down, you will find a horizontal scrollbar. You may scroll right to view the right-most columns.

[oracle@ip-172-31-88-93 ~]$ au -config eqtrd.12c.19c.conf -mode analyze
AutoUpgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
| 110|  EQTRD|PRECHECKS|PREPARING|RUNNING|19/10/11 14:38|     N/A|14:38:09|Loading DB info|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|       MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
| 110|  EQTRD|PRECHECKS|PREPARING|RUNNING|19/10/11 14:38|     N/A|14:38:16|Remaining 6/72|
+----+-------+---------+---------+-------+--------------+--------+--------+--------------+
Total jobs 1
upg> Job 110 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 110 FOR EQTRD

Fixups

Use AutoUpgrade to run Fixups:

[oracle@ip-172-31-88-93 ~]$ au -config eqtrd.12c.19c.conf -mode fixups
AutoUpgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|        MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
| 111|  EQTRD|PRECHECKS|PREPARING|RUNNING|19/10/11 14:43|     N/A|14:43:28|Loading DB info|
+----+-------+---------+---------+-------+--------------+--------+--------+---------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|      MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-------------+
| 111|  EQTRD|PREFIXUPS|EXECUTING|RUNNING|19/10/11 14:43|     N/A|14:43:42|Remaining 3/3|
+----+-------+---------+---------+-------+--------------+--------+--------+-------------+
Total jobs 1
upg> Job 111 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 111 FOR EQTRD

Target AutoUpgrade configuration file

Prepare the target AutoUpgrade configuration file. Notice that dbname and sid are changed. upgrade_node must match the target hostname. The configuration file is xient.12c.19c.conf.

global.autoupg_log_dir=/u01/app/oracle/autoupgrade

upg1.dbname=XIENT
upg1.start_time=now
upg1.source_home=/u01/app/oracle/product/12.1.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=XIENT
upg1.log_dir=/u01/app/oracle/autoupgrade
upg1.upgrade_node=ip-172-31-91-148.ec2.internal
upg1.target_version=19.3

Prepare 19c oracle homes

Prepare the 19c oracle homes with the password file and tnsnames.ora. Primary:

[oracle@ip-172-31-88-93 ~]$ alias oh
alias oh='cd $ORACLE_HOME'
[oracle@ip-172-31-88-93 ~]$ oh
[oracle@ip-172-31-88-93 dbhome_1]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@ip-172-31-88-93 dbhome_1]$ cp -p dbs/orapwEQTRD /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@ip-172-31-88-93 dbhome_1]$ cp -p network/admin/tnsnames.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/

standby:

[oracle@ip-172-31-91-148 ~]$ oh
[oracle@ip-172-31-91-148 dbhome_1]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@ip-172-31-91-148 dbhome_1]$ cp -p dbs/orapwXIENT /u01/app/oracle/product/19.3.0/dbhome_1/dbs/ 
[oracle@ip-172-31-91-148 dbhome_1]$ cp -p network/admin/tnsnames.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/

Create the Transient Logical Standby

For this exercise, I find it helpful to have four terminal windows open, two on the primary and two on the standby. One window per host is for tailing the alert log, and the other is for commands.

Data Guard configuration

Note the Data Guard configuration. At the standby:

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME  PARENT_DBUN     DEST_ROLE         CURRENT_SCN     CON_ID
--------------- --------------- ----------------- ----------- ----------
EQTRD           NONE            PRIMARY DATABASE      1868165          0
XIENT           EQTRD           PHYSICAL STANDBY      1868075          0

Disable the Data Guard broker configuration

We are not going to stop the Data Guard broker. Disabling the configuration freezes the current Data Guard system state.

DGMGRL> disable configuration
Disabled.

Standby is mounted

Note that the standby database is mounted:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

Primary restore point

The primary restore point is required for the upgrade procedure later. Create it now:

SQL> create restore point upgrd1 guarantee flashback database;

Restore point created.

Cancel managed recovery

On the standby:

SQL> recover managed standby database cancel
Media recovery complete.

Create standby restore point

On the standby:

SQL> create restore point upgrd2 guarantee flashback database;

Restore point created.

Some DBAs create additional restore points at later steps.

Create the logical standby

For this step, it is helpful to watch two windows.

On the standby:

SQL> alter database recover to logical standby keep identity;

The session hangs …

On the primary:

SQL> exec dbms_logstdby.build;

PL/SQL procedure successfully completed.

SQL>

dbms_logstdby.build runs in approximately three seconds. Three more seconds later, the alter database finishes and the sqlplus prompt appears.

Database altered.

SQL>

Run standby apply

On the standby:

SQL> alter database open;

Database altered.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
INITIALIZING

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
INITIALIZING

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
LOADING DICTIONARY

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
LOADING DICTIONARY

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
LOADING DICTIONARY

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
APPLYING

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
IDLE

SQL>

Standby log miner dictionary build may hang at INITIALIZING for more than one minute. It could take up to 5 minutes to reach IDLE.

Foreign archivelogs

Archivelogs identified as “foreign” may appear at the standby

Wed Oct 16 02:21:23 2019
LOGMINER: End mining logfile for session 1 thread 1 sequence 33, +RECO01/XIENT/foreign_archivelog/EQTRD/2019_10_16/thread_1_seq_33.316.1021774867

Stop standby apply

When logical standby state reaches IDLE, stop standby apply.

On the primary:

SQL> alter system set log_archive_dest_state_2=defer scope=memory;

System altered.

From this point until the catchup, the primary continues to operate normally with users connected. Archived logs at the primary contain transactions that will be replayed at the standby later.

On the standby:

SQL> alter database stop logical standby apply;

Database altered.

Shutdown 12c

On the standby:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Upgrade

Upgrade the standby to 19c.

Setup the shell environment

Set the 19c environment.

On the standby:

[oracle@ip-172-31-91-148 ~]$ unset ORACLE_SID
[oracle@ip-172-31-91-148 ~]$ . oraenv
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.3.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ip-172-31-91-148 ~]$ export ORACLE_SID=XIENT

Upgrade the Restart

[oracle@ip-172-31-91-148 ~]$ srvctl upgrade database -database XIENT -oraclehome /u01/app/oracle/product/19.3.0/dbhome_1

startup upgrade

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1577054672 bytes
Fixed Size                  8896976 bytes
Variable Size             385875968 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

Check the status. The following output is expected:

SQL> select logins, status from v$instance;

LOGINS     STATUS
---------- ------------
RESTRICTED OPEN MIGRATE

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

oratab

Note that the Oracle Agent has updated the Oracle home in oratab.

[oracle@ip-172-31-91-148 ~]$ grep XIENT /etc/oratab
XIENT:/u01/app/oracle/product/19.3.0/dbhome_1:N         # line added by Agent

Start screen

If you lose your session during the upgrade, you are going to lose direct control over the upgrade. To recover, you would need to search your global.autoupg_log_dir for the state and try to figure out how to recover, if necessary.

Protect your session with screen.

[oracle@ip-172-31-91-148 ~]$ screen

If you get disconnected, reconnect to screen:

[oracle@ip-172-31-91-148 ~]$ screen -r

You can detach from screen with ctrl-a-d.

Run AutoUpgrade

Use AutoUpgrade to upgrade the standby database.

On the standby, in screen:

[oracle@ip-172-31-91-148 ~]$ AH=/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@ip-172-31-91-148 ~]$ alias au='rlwrap $AH/jdk/bin/java -jar $AH/rdbms/admin/autoupgrade.jar'
[oracle@ip-172-31-91-148 ~]$ au -config xient.12c.19c.conf -mode upgrade
AutoUpgrade tool launched with default options
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|    MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|02:48:05|0%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|02:51:08|12%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|03:00:14|49%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|03:09:21|92%Upgraded |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|    MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|03:12:52|0%Compiled |
+----+-------+---------+---------+-------+--------------+--------+--------+-----------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|     MESSAGE|
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
| 105|  XIENT|DBUPGRADE|EXECUTING|RUNNING|19/10/12 02:47|     N/A|03:15:56|80%Compiled |
+----+-------+---------+---------+-------+--------------+--------+--------+------------+
Total jobs 1
upg> lsj
+----+-------+---------+---------+--------+--------------+--------+--------+-------------------+
|Job#|DB_NAME|    STAGE|OPERATION|  STATUS|    START_TIME|END_TIME| UPDATED|            MESSAGE|
+----+-------+---------+---------+--------+--------------+--------+--------+-------------------+
| 105|  XIENT|DBUPGRADE|EXECUTING|FINISHED|19/10/12 02:47|     N/A|03:18:21|Restarting Database|
+----+-------+---------+---------+--------+--------------+--------+--------+-------------------+
Total jobs 1
upg> lsj
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|    START_TIME|END_TIME| UPDATED|      MESSAGE|
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
| 105|  XIENT|POSTFIXUPS|EXECUTING|RUNNING|19/10/12 02:47|     N/A|03:22:21|Remaining 1/3|
+----+-------+----------+---------+-------+--------------+--------+--------+-------------+
Total jobs 1
upg> Job 105 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished successfully     [1]
Jobs failed                    [0]
Jobs pending                   [0]
------------- JOBS FINISHED SUCCESSFULLY -------------
Job 105 FOR XIENT

Exit screen

You may exit screen now. To exit screen, exit your shell.

Update the static listener

In the grid account, edit listener.ora. You can do this while you are waiting for the upgrade to finish.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = XIENT)
    )
  )

Reload the listener

[grid@ip-172-31-91-148 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-OCT-2019 02:26:43

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-91-148.ec2.internal)(PORT=1521)))
The command completed successfully

Check the listener status

[grid@ip-172-31-91-148 admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 12-OCT-2019 02:26:48

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-91-148.ec2.internal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                11-OCT-2019 13:17:23
Uptime                    0 days 13 hr. 9 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.3.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ip-172-31-91-148/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-172-31-91-148.ec2.internal)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA01" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO01" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "XIENT" has 1 instance(s).
  Instance "XIENT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Continued operations on the 12c primary

Meanwhile, on primary, which is still on 12c, users may continue to modify data:

SQL> select banner from v$version where banner like 'Oracle Database%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> @ insert2.sql

1 row created.

SQL> commit;

Commit complete.

SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK
DMYD9FS3FD3CYO77CU4R

Catch up the upgraded standby

Initial state

The standby is open and upgraded, but still lacks application data updates that were made to the primary during the upgrade.

SQL> select banner from v$version where banner like 'Oracle Database%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK

With only one row, table ordermessage is out of date.

Resume transport

Resume transport from the primary to the standby:

Primary:

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

Standby:

SQL> alter database start logical standby apply immediate;

Database altered.

Check for catchup on the standby:

Primary:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1964941

Standby:

SQL> select applied_scn, latest_scn, mining_scn from v$logstdby_progress;

APPLIED_SCN LATEST_SCN MINING_SCN
----------- ---------- ----------
1964938        1964946    1964944

Check switchover status.

On the primary:

SQL> select database_role, switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY

Until now, the application was operating normally on the original primary. Primary application data is now caught up on the original standby.

Check the application data on the standby:

SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK
DMYD9FS3FD3CYO77CU4R

The standby data is up to date!

Now it is time to quiesce the application. Otherwise, changes could be lost.

Switchover

We’re going to switchover the application to the Transient Logical Standby. This is the actual outage. Get your stopwatch ready!

Original primary:

SQL> alter database commit to switchover to logical standby;

Database altered.

Start your stopwatch!

alter database commit to switchover to logical standby could run for 4s.

Original standby:

SQL> select database_role, switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
LOGICAL STANDBY  TO PRIMARY
SQL> alter database commit to switchover to logical primary;

Database altered.

alter database commit to switchover to logical primary could run for 15s or more.

Stop!

The first outage is over. Users may connect to the new logical primary.

Flashback the original primary

The original primary is going to become the physical standby.

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> flashback database to restore point upgrd1;

Flashback complete.

Convert the original primary to physical standby

SQL> alter database convert to physical standby;

Database altered.

Shut down original primary

SQL> shutdown abort
ORACLE instance shut down.

Environment

Set the 19c environment:

[oracle@ip-172-31-88-93 ~]$ unset ORACLE_SID
[oracle@ip-172-31-88-93 ~]$ . oraenv
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.3.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ip-172-31-88-93 ~]$ ORACLE_SID=EQTRD

Upgrade original primary Restart

 [oracle@ip-172-31-88-93 ~]$ srvctl upgrade database -database EQTRD -oraclehome /u01/app/oracle/product/19.3.0/dbhome_1

startup mount

Mount the original primary.

[oracle@ip-172-31-88-93 ~]$ srvctl start database -database EQTRD -startoption MOUNT

Check oratab

Notice that the Oracle Agent updated the Oracle Home in oratab

[oracle@ip-172-31-88-93 ~]$ grep EQTRD /etc/oratab
EQTRD:/u01/app/oracle/product/19.3.0/dbhome_1:N         # line added by Agent

Static listener

Edit the static listener. In the grid account on the original primary:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME = EQTRD)
    )
  )

Reload the listener

[grid@ip-172-31-91-148 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-OCT-2019 00:32:52

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-91-148.ec2.internal)(PORT=1521)))
The command completed successfully

Check the listener status

[grid@ip-172-31-88-93 admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-OCT-2019 03:56:08

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-172-31-88-93.ec2.internal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-OCT-2019 00:25:28
Uptime                    0 days 3 hr. 30 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/19.3.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ip-172-31-88-93/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ip-172-31-88-93.ec2.internal)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA01" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO01" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "EQTRD" has 2 instance(s).
  Instance "EQTRD", status UNKNOWN, has 1 handler(s) for this service...
  Instance "EQTRD", status READY, has 1 handler(s) for this service...
The command completed successfully

Enable the Data Guard configuration

Connect to the Transient Logical Standby:

[oracle@ip-172-31-91-148 ~]$ dgmgrl sys/Zystm.22@XIENT
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Oct 12 05:17:06 2019
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "XIENT"
Connected as SYSDBA.

DGMGRL> enable configuration
Enabled.

Data Guard broker is going to detect that XIENT is the new primary. Data Guard broker will start log transport to EQTRD, and will start managed standby on EQTRD. Wait 5 to 10 minutes until recovery at the original primary is caught up.

A message such as the following should appear in the original primary alert log:

2019-10-17T02:24:01.118981+00:00
PR00 (PID:9485): Media Recovery Waiting for T-1.S-31 (in transit)

Check the Data Guard configuration

DGMGRL> show configuration

Configuration - ORCL_CONFIG

  Protection Mode: MaxPerformance
  Members:
  XIENT - Primary database
    EQTRD - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 20 seconds ago)

Errors

The following errors should not appear if no steps were missed.

ORA-1033: ORACLE initialization or shutdown in progress

DGMGRL> add database 'EQTRD' as connect identifier is 'EQTRD' maintained as physical;
Error: ORA-1033: ORACLE initialization or shutdown in progress

Failed.

There could be a password file issue. Copy the 12c orapw to the 19c home.

On the original primary:

[oracle@ip-172-31-88-93 ~]$ cp -p /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwEQTRD /u01/app/oracle/product/19.3.0/dbhome_1/dbs/orapwEQTRD

Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

DGMGRL> add database 'EQTRD' as connect identifier is 'EQTRD' maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Solution: clear log_archive_dest_2.

On the original primary:

SQL> alter system set log_archive_dest_2='';

System altered.

Switchover to the original primary

The next step is going to result in another application outage.

DGMGRL> switchover to EQTRD
Performing switchover NOW, please wait...
Operation requires a connection to database "EQTRD"
Connecting ...
Connected to "EQTRD"
Connected as SYSDBA.
New primary database "EQTRD" is opening...
Oracle Clusterware is restarting database "XIENT" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to "XIENT"
Connected to "XIENT"
Switchover succeeded, new primary is "eqtrd"

The switchover completed in less than 40s.

Flashback to restore point

This section contains backout procedures that you can use on the primary or the standby.

Remove or disable Data Guard

You can try to flashback the primary and the standby and resume Data Guard. If you do, you may notice an error such as this one:

In the broker:

ERROR ORA-16700: the standby database has diverged from the primary database

In the standby alert log:

Thu Oct 17 03:53:40 2019
Errors in file /u01/app/oracle/diag/rdbms/xient/XIENT/trace/XIENT_mrp0_3527.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DATA01/XIENT/DATAFILE/system.271.1021840189'

For the sake of demonstration, we will remove Data Guard and then flashback and open either the primary or the standby, but not both.

Remove the Data Guard broker configuration.

DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration
Removed configuration
DGMGRL>

Stop the brokers and disable transport.

On the primary:

SQL> alter system set log_archive_dest_2='';

System altered.

SQL> alter system set dg_broker_start=false;

System altered.

On the standby:

SQL> alter system set dg_broker_start=false;

System altered.

Flashback the primary

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> flashback database to restore point upgrd1;

Flashback complete.

Clear parameters that are invalid in 12.1. Shut down abort.

SQL> alter system reset "__unified_pga_pool_size" sid = 'EQTRD' scope=spfile;

System altered

SQL> shutdown abort
ORACLE instance shut down.

Downgrade Restart.

[oracle@ip-172-31-88-93 ~]$ srvctl downgrade database -database EQTRD -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 -targetversion 12.1.0.2

Set the 12c environment.

[oracle@ip-172-31-88-93 ~]$ unset ORACLE_SID
[oracle@ip-172-31-88-93 ~]$ . oraenv
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ip-172-31-88-93 ~]$ ORACLE_SID=EQTRD

Startup mount and open resetlogs.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

Notice that the Agent has updated oratab to 12c.

[oracle@ip-172-31-88-93 ~]$ grep EQTRD /etc/oratab
EQTRD:/u01/app/oracle/product/12.1.0/dbhome_1:N         # line added by Agent

Check the application data. Changes that were made during the upgrade have been lost.

SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK

Rollback of primary to 12c is done.

Flashback the standby

As an alternative, flashback the standby.

SQL> recover managed standby database cancel
Media recovery complete.
SQL> flashback database to restore point upgrd2;

Flashback complete.

SQL> alter database activate physical standby database;

Database altered.

Reset parameters that are invalid in 12.1. Shut down abort.

SQL> alter system reset "__unified_pga_pool_size" sid = 'XIENT' scope=spfile;

System altered.

SQL> shutdown abort
ORACLE instance shut down.

Downgrade Restart.

[oracle@ip-172-31-91-148 ~]$ srvctl downgrade database -database XIENT -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 -targetversion 12.1.0.2

Set the 12c environment. Startup.

[oracle@ip-172-31-91-148 ~]$ unset ORACLE_SID
[oracle@ip-172-31-91-148 ~]$ . oraenv
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ip-172-31-91-148 ~]$ export ORACLE_SID=XIENT

SQL> startup
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

Notice that the Agent has updated oratab.

[oracle@ip-172-31-91-148 ~]$ grep XIENT /etc/oratab
XIENT:/u01/app/oracle/product/12.1.0/dbhome_1:N         # line added by Agent

Check the application data.

SQL> select ClOrdID from ordermessage order by TransactTime;

CLORDID
------------------------------
XL46W2NCVM22N02RQ2YK

Conclusion to flashbacks

This section was an outline of two possible, independent rollback procedures. Flashback will result in data loss. Further refinement is required.

Prior to beginning, we had moved spfile to ASM.

This concludes the section on flashback.

Final steps

If there are no problems with the upgrade, continue.

Drop the restore point

Primary:

SQL> drop restore point UPGRD1;

Restore point dropped.

Standby:

SQL> drop restore point UPGRD2;

Restore point dropped.

Update compatible

Primary:

SQL> alter system set compatible = '19.3.0.0.0' scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

Standby:

SQL> alter system set compatible = '19.3.0.0.0' scope=spfile;

System altered.

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size             503320288 bytes
Database Buffers         1056964608 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL

compatible error

You cannot change compatibility if a guaranteed restore point exists.

SQL> alter system set compatible = '19.3.0.0.0' scope=spfile;

System altered.

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1577054672 bytes
Fixed Size                  8896976 bytes
Variable Size             536870912 bytes
Database Buffers         1023410176 bytes
Redo Buffers                7876608 bytes
ORA-38880: Cannot advance compatibility from 12.1.0.2.0 to 19.3.0.0.0 due to
guaranteed restore points

Solutions:

  1. Set compatible to its original value. 
    SQL> alter system set compatible = '12.1.0.2.0' scope=spfile;

System altered.

SQL> startup force mount
ORACLE instance started.

Total System Global Area 1577054672 bytes
Fixed Size                  8896976 bytes
Variable Size             536870912 bytes
Database Buffers         1023410176 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL>

    or:

  2. Drop the restore point.

Delete foreign archive logs

RMAN> list foreign archivelog all;

RMAN> delete noprompt foreign archivelog all;

Conclusion

Oracle Database was upgraded from version 12.1 to version 19.3 using AutoUpgrade and Transient Logical Standby. The actual application outage was 10 seconds.

Notes:

  • The initial state was a working Data Guard system with a physical standby.
  • Oracle Customer offers rolling upgrade script physru.
  • In case of a problem with the upgrade, it is possible to flashback to a point in time before the upgrade.
  • This is a zero data loss upgrade.
  • There are two brief outages.
  • The rollback is not fully developed in this article.
  • As described here, downgrade to 12c results in data loss.

 

Scrollbar wanted

Posted on by
Reply

By Brian Fitzgerald

Introduction

I’m looking for a wordpress.com plugin that has a vertical and horizontal scrollbar surrounding preformatted text, such as code or output. Note that wordpress.com is not the same thing as wordpress.org.

Closest

This one is the closest I get, but the horizontal scrollbar is out of view until you scroll down.

Lorem ipsum dolor sit amet, consectetur adipiscing elit.
Cras ac leo vitae felis sagittis posuere.
if True:
    Nunc sit amet orci et dui dapibus vestibulum at at sapien.
    Aliquam laoreet erat eget vestibulum malesuada.
    Fusce nec quam nec dui iaculis feugiat tristique vel erat.
    Curabitur malesuada erat at ultricies porta.
    Ut blandit risus ut erat pellentesque, at rutrum purus sollicitudin.
else:
    Pellentesque interdum mauris quis diam porttitor, a porttitor dolor lacinia.
    Vivamus ac mauris vitae justo maximus vehicula.
    Duis ultricies velit id eros maximus semper.
    Quisque laoreet ligula vel mauris condimentum vestibulum.
    Vivamus pharetra diam et elit finibus consequat.
    Nullam non lectus vel velit viverra gravida sed ut nisi.
    Nullam eu nisl id mi laoreet elementum.

I use plugin “Custom Scrollbar” by Michael Uno in a div tag.

Conclusion

Please put your suggestions in the reply section.

move spfile to ASM in one step

Posted on by
Reply

By Brian Fitzgerald

Introduction

Here is a simple technique for relocating the Oracle Database spfile from the file system to ASM in a single RMAN step in Oracle Restart. No direct ASM access is required. The technique applies to Oracle Database 12c and up.

tl;dr

RMAN> restore from service 'EQTRD' spfile to '+DATA01';
SQL> startup force

Initial condition

Note the Restart configuration

Note the db_unique_name and the spfile location.

[oracle@ip-172-31-88-93 ~]$ srvctl config database -database EQTRD
Database unique name: EQTRD
Database name: EQTRD
Oracle home: /u01/app/oracle/product/19.3.0/dbhome_1
Oracle user: oracle
Spfile: /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: RECO01,DATA01
Services:
OSDBA group:
OSOPER group:
Database instance: EQTRD

ASM

Check that no spfile exists in ASM for your db_unique_name:

[grid@ip-172-31-88-93 ~]$ asmcmd ls +DATA01/EQTRD/PARAMETERFILE
ASMCMD-8002: entry 'PARAMETERFILE' does not exist in directory '+DATA01/EQTRD/'

No spfile was found in ASM.

Check Restart

The initial condition should be a working Oracle Restart.

[oracle@ip-172-31-88-93 ~]$ srvctl stop database -database EQTRD
[oracle@ip-172-31-88-93 ~]$ srvctl start database -database EQTRD
[oracle@ip-172-31-88-93 ~]$

Restore

Restore the spfile using a single RMAN command. You must connect to oracle via a service. “rman target /” will not work in this case. You only need to specify the target disk group. You do not need to specify the full ASM directory path.

[oracle@ip-172-31-88-93 ~]$ rman target sys/Zystm.22@EQTRD

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Oct 13 18:12:25 2019
Version 19.3.0.0.0

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

connected to target database: EQTRD (DBID=625173207)

RMAN> restore from service 'EQTRD' spfile to '+DATA01';

Starting restore at 13-OCT-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=282 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service EQTRD
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA01
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-OCT-19

RMAN>

Recovery Manager complete.

The RMAN technique uses the “restore from service” syntax, available in Oracle Database 12c and up.

Check Restart

Notice that the Restart configuration got updated:

[oracle@ip-172-31-88-93 ~]$ srvctl config database -database EQTRD
Database unique name: EQTRD
Database name: EQTRD
Oracle home: /u01/app/oracle/product/19.3.0/dbhome_1
Oracle user: oracle
Spfile: +DATA01/EQTRD/PARAMETERFILE/spfile.280.1021572757
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: RECO01,DATA01
Services:
OSDBA group:
OSOPER group:
Database instance: EQTRD

Notice we moved spfile and learned its new location without direct access to ASM. In an organization with segregation of duties, the RDMBS DBA can move spfile to ASM without coordination with the ASM DBA.

Check ASM

For information, we may check ASM.

[grid@ip-172-31-88-93 ~]$ asmcmd ls +DATA01/EQTRD/PARAMETERFILE/spfile.280.1021572757
spfile.280.1021572757

Remove old spfile

Remove the old spfile from the file system (if you dare!).

The Oracle base has been set to /u01/app/oracle
[oracle@ip-172-31-88-93 ~]$ rm /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora
[oracle@ip-172-31-88-93 ~]$

Restart the database

You must restart the database instance now. You may use sqlplus or srvctl.

SQL> startup force
ORACLE instance started.

Total System Global Area 2365586088 bytes
Fixed Size                  8899240 bytes
Variable Size             520093696 bytes
Database Buffers         1828716544 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.
SQL>

Done!

Only one RMAN command was needed to move the spfile to ASM.

Reversal

You can move spfile in the reverse direction, from ASM to the file system.

Check the file system

Note that the spfile does not exist on the file system

 [oracle@ip-172-31-88-93 ~]$ ls /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora
ls: cannot access /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora: No such file or directory

Restore spfile to file system

[oracle@ip-172-31-88-93 ~]$ rman target sys/Zystm.22@EQTRD

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Oct 13 18:40:05 2019
Version 19.3.0.0.0

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

connected to target database: EQTRD (DBID=625173207)

RMAN> restore from service 'EQTRD' spfile to '/u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora';

Starting restore at 13-OCT-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=55 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service EQTRD
channel ORA_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 13-OCT-19

RMAN>

Moving the spfile to the file system required no access to ASM and no knowledge of the ASM file name.

Check that spfile exists on the file system

[oracle@ip-172-31-88-93 ~]$ ls -l /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora
-rw-r-----. 1 oracle dba 3584 Oct 13 18:40 /u01/app/oracle/product/19.3.0/dbhome_1/dbs/spfileEQTRD.ora

Check that Restart is updated

Remove old spfile from ASM

[ec2-user@ip-172-31-88-93 ~]$ sudo -i -u grid
The Oracle base has been set to /u01/app/grid
[grid@ip-172-31-88-93 ~]$ asmcmd rm +DATA01/EQTRD/PARAMETERFILE/spfile.280.1021572757
[grid@ip-172-31-88-93 ~]$

Restart the database

Instead of using sqlplus, you may use srvctl.

[oracle@ip-172-31-88-93 ~]$ srvctl stop database -database EQTRD -stopoption ABORT
[oracle@ip-172-31-88-93 ~]$ srvctl start database -database EQTRD
[oracle@ip-172-31-88-93 ~]$

Conclusion

Previously, moving spfile TO ASM required five steps:

  1. Create pfile from spfile
  2. Create spfile from pfile
  3. Connect to ASM and identify the file name.
  4. Update CRS by running srvctl modify database
  5. Restart the instance

By the way, some DBAs pre-create an ASM directory. I don’t know why. Some DBAs specify an ASM alias name for the spfile. I just let ASM generate a random, unique name.

spfile was moved from the file system in a single step. It was unnecessary to create an intermediate pfile, nor was it necessary to know the path to the source spfile. You only need to specify the ASM disk group. The technique did not require the DBA to access ASM to learn the location of the spfile. There is no separate srvctl modify database step. RMAN takes care of that.

In RAC, spfile centralization is an administrative win. Multiple instance settings can be stored in a single file. Initialization parameter settings applicable to all instances, or only one instance can be made even if a RAC node is down. Parameters follow an instance even if an instance is relocated. The same goes for RAC One Node.

Remember that a Restart instance can be relocated, too. With spfile in ASM, there is no need to move spfile during relocation. If you upgrade the Oracle version, you don’t have to be concerned about moving the spfile or reconfiguring Restart.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

Posted on by
Reply

By Brian Fitzgerald

Introduction

After dropping a logical standby database, ORA-08137 appeared. Clearing log_archive_dest_2 resolved the issue.

Problem

During testing, I dropped a logical standby database. I attempted to delete archive logs on the primary. ORA-08137 appeared.

RMAN> delete noprompt archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+RECO01/EQTRD/ARCHIVELOG/2019_10_12/thread_1_seq_24.263.1021422689 thread=1 sequence=24

Solution

Having dropped the standby database, the appropriate solution is to clear log_archive_dest_2.

SQL> alter system set log_archive_dest_2 = '';

System altered.

Now the delete succeeds:

RMAN> delete noprompt archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
List of Archived Log Copies for database with db_unique_name EQTRD
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
33      1    24      A 11-OCT-19
        Name: +RECO01/EQTRD/ARCHIVELOG/2019_10_12/thread_1_seq_24.263.1021422689

deleted archived log
archived log file name=+RECO01/EQTRD/ARCHIVELOG/2019_10_12/thread_1_seq_24.263.1021422689 RECID=33 STAMP=1021422689
Deleted 1 objects

No force is needed.

Conclusion

There are numerous causes for RMAN-08137 relating to GoldenGate and Streams. These situations are described in the blogs and forums. Also, other logical standby situations have a different solution. Our case here is simple. We dropped the logical standby. The solution is also simple: Clear log_archive_dest_2. Thanks to Dean Capps for describing this solution. Refer also to Note 2169282.1.

 

Upgrade to postgres 12

Posted on by
Reply

By Brian Fitzgerald

Introduction

Here is a walkthough of upgrading PostgreSQL from version 10 to 12. PostgreSQL version 12 was released today, Oct 3, 2019. The operating system is Red Hat Linux 7.7.

Install the new binaries

Caution: the installation will overwrite your ~postgres/.bash_profile.

As root:

# yum -y erase pgdg-redhat-repo
# yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum -y install postgresql12-server

Note: the installation will re-point some symbolic links in /usr/bin and /etc/alternatives. For example::

lrwxrwxrwx. 1 root root 28 Oct  3 15:27 /usr/bin/psql -> /etc/alternatives/pgsql-psql
lrwxrwxrwx. 1 root root 22 Oct  3 15:27 /etc/alternatives/pgsql-psql -> /usr/pgsql-12/bin/psql

Shut down the old version

As postgres:

$ echo $PATH /usr/pgsql-10/bin:/var/lib/pgsql/bin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/sbin 
$ echo $PGDATA /var/lib/pgsql/10/data 
$ pg_ctl stop 
waiting for server to shut down.... done 
server stopped

Initialize the new PGDATA

$ PATH=/usr/pgsql-12/bin:/var/lib/pgsql/bin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/sbin
$ PGDATA=/var/lib/pgsql/12/data
$ mkdir -p /var/lib/pgsql/12/data
$ initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/12/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/pgsql/12/data -l logfile start

Upgrade

Supply the old and new bin and data directories on the command line.

$ pg_upgrade -b /usr/pgsql-10/bin -B /usr/pgsql-12/bin -d /var/lib/pgsql/10/data -D /var/lib/pgsql/12/data
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

Start the new version

[postgres@ip-172-31-47-137 data]$ pg_ctl start
waiting for server to start....2019-10-03 15:28:59.551 EDT [2589] LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2019-10-03 15:28:59.551 EDT [2589] LOG:  listening on IPv6 address "::1", port 5432
2019-10-03 15:28:59.551 EDT [2589] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-10-03 15:28:59.554 EDT [2589] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-10-03 15:28:59.559 EDT [2589] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-10-03 15:28:59.569 EDT [2589] LOG:  redirecting log output to logging collector process
2019-10-03 15:28:59.569 EDT [2589] HINT:  Future log output will appear in directory "log".
 done
server started

Analyze the database

I have one user database, “elections”.

$ ./analyze_new_cluster.sh
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy.  When it is done, your system will
have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel
this script and run:
    "/usr/pgsql-12/bin/vacuumdb" --all --analyze-only

vacuumdb: processing database "elections": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "elections": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "elections": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics

Done

Delete the old data files

[postgres@ip-172-31-47-137 data]$ du -s /var/lib/pgsql/10/data /var/lib/pgsql/12/data
2379432 /var/lib/pgsql/10/data
1693660 /var/lib/pgsql/12/data
[postgres@ip-172-31-47-137 data]$ ./delete_old_cluster.sh
[postgres@ip-172-31-47-137 data]$ du -s /var/lib/pgsql/10/data /var/lib/pgsql/12/data
du: cannot access ‘/var/lib/pgsql/10/data’: No such file or directory
1691976 /var/lib/pgsql/12/data

Update ~postgres/.pgsql_profile

PATH=/usr/pgsql-12/bin:$PATH
export PGDATA=/var/lib/pgsql/12/data

Restore your old ~postgres/.bash_profile, if desired.

Conclusion

This was an overview of an upgrade of a standalone server from PostgreSQL 10 to version 12.