Creating a Physical Standby database

So there are heaps of articles out there on how to do this, so this one is for my benefit because I've run through it a few times and I know it works. If it doesn't work using this, it means that there is an issue with the environment or infrastructure.

This is pretty much copy and paste.

Primary

copy+paste this into a sqlplus session as sysdba

ALTER DATABASE FORCE LOGGING;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STDBY)';
alter system set log_archive_dest_2='SERVICE=ORCL_STDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STDBY';
alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER;
alter system set fal_server=ORCL_STDBY;
alter system set fal_client=ORCL;
alter system set DB_FILE_NAME_CONVERT='ORCL_STDBY','ORCL' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='ORCL_STDBY','ORCL' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;

Find the size of the redo logs and create the same number and size for standby

SELECT GROUP#,BYTES FROM V$LOG;

   GROUP#      BYTES
---------- ----------
         1   52428800
         3   52428800
         2   52428800

Check to see if standby logs have been created

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

If so, skip the next step.

If not, add them:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

create pfile='/home/oracle/initORCL_STDBY.ora' from spfile;

cat the created file and copy and paste it into the standby, then edit it.

Standby
edit the init.ora

*.control_files='+DATA','+RECO','+ARCH'
*.log_archive_dest_2='SERVICE=ORCL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL';
*.db_unique_name='ORCL_STDBY'
*.log_file_name_convert='ORCL','ORCL_STDBY'
*.db_file_name_convert='ORCL','ORCL_STDBY'
*.fal_client='ORCL_STDBY'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(ORCL,ORCL_STDBY)'

standby listener (could be in GRID HOME, could be in ORACLE_HOME, could be defined by TNS_ADMIN)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_STDBY)
      (SID_NAME = ORCL)
      (ORACLE_HOME = /oracle/ORCL/121)
    )
  )

lsnrctl reload

Needs to be 'UNKNOWN', not 'BLOCKED'

tnsnames.ora on both hosts (could be in GRID HOME, could be in ORACLE_HOME, could be defined by TNS_ADMIN, may need .WORLD, may not)

ORCL.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = oraorcl01)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA =
       (SID = ORCL)
       (GLOBAL_NAME = ORCL.WORLD)
       (UR = A)
    )
  )

ORCL_STDBY.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = oraorcl02)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA =
       (SID = ORCL)
       (GLOBAL_NAME = ORCL.WORLD)
       (UR = A)
    )
  )


Check you can tnsping - if not, they probably haven't opened the ports.
TNS-12535: TNS:operation timed out = ports blocked

startup nomount pfile='initORCL_STDBY.ora'

rman script

DIR=/home/oracle/scripts
LOG=$DIR/duplicate.log
rman target sys@ORCL/xxxxx auxiliary sys@ORCL_STDBY/xxxxx << EOF > $LOG
RUN
{
        allocate channel dev1 type disk;
        allocate channel dev2 type disk;
        allocate channel dev3 type disk;
        allocate channel dev4 type disk;
        allocate channel dev5 type disk;
        ALLOCATE auxiliary CHANNEL CH01 TYPE DISK;
        ALLOCATE auxiliary CHANNEL CH02 TYPE DISK;
        ALLOCATE auxiliary CHANNEL CH03 TYPE DISK;
        ALLOCATE auxiliary CHANNEL CH04 TYPE DISK;
        ALLOCATE auxiliary CHANNEL CH05 TYPE DISK;
        DUPLICATE TARGET DATABASE
        FOR STANDBY
                FROM ACTIVE DATABASE;
}
EOF

check standby db_unique_name = ORCL_STDBY

shutdown immediate

sqlplus / as sysdba
create spfile='+DATA/ORCL_STDBY/spfileORCL.ora' from pfile;
exit

mv initORCL.ora initORCL.ora_old

vi initORCL.ora
spfile=('+DATA/ORCL_STDBY/spfileORCL.ora')

sqlplus / as sysdba
startup mount

On primary

alter system set log_archive_dest_state_2=enable;

On standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

tail the standby alert log

tail -f /oracle/ORCL/saptrace/diag/rdbms/ORCL_stdby/ORCL/trace/alert_ORCL.log

On the Primary

alter system switch logfile;
/
/

Logs should be applied to the standby.

Check the gap:

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd = lh.thrd;

Numbers should match

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1              7197             7197

There is a SAP process called BRARCHIVE which backs up and deletes logs, so you may have missing logs that have gone from the primary

If there is a gap in logs, restore them like this:

rman target = /
SET DECRYPTION IDENTIFIED BY 'xxxxx';
run {
allocate channel sbt_1 device type 'SBT_TAPE'
parms 'SBT_LIBRARY=/oracle/ORCL/121/lib/libosbws.so, ENV=(OSB_WS_PFILE=/oracle/ORCL/121/dbs/osbwsORCL.ora)';
restore archivelog from logseq=7187 until logseq=7194 ;
}


Setting up the broker

on both primary and standby - assuming it's a new configuration. If not, there is an Oracle note - Step By Step How to Recreate Dataguard Broker Configuration (Doc ID 808783.1)

alter system set dg_broker_start=true;
alter system set log_archive_dest_2='' scope=both;

show parameter broker

remove / move any broker config files

dg_broker_config_file1               string      /oracle/ORCL/121/dbs/dr1ORCL.dat
dg_broker_config_file2               string      /oracle/ORCL/121/dbs/dr2ORCL.dat

exit

dgmgrl /
create configuration 'ORCL_DG' as primary database is 'ORCL' connect identifier is 'ORCL';
ADD DATABASE 'ORCL_STDBY' AS CONNECT IDENTIFIER IS ORCL_STDBY MAINTAINED AS PHYSICAL;
enable configuration;

show configuration;

Should show "SUCCESS" at the bottom

Tail the standby alert log and switch some primary log files, check the gap.


Set up failover connections

exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'ORCL_AUTO',network_name => 'ORCL_AUTO',failover_method => 'BASIC',failover_type => 'SELECT',failover_retries => 180,failover_delay => 1);

PL/SQL procedure successfully completed.

CREATE OR REPLACE TRIGGER manage_dgservice
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('ORCL_AUTO');
END IF;
END;
/



client connection - give to the application
ORCL_AUTO=
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraorcl01)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = oraorcl02)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL_AUTO)
   )
)

Using AWS CLI

Amazon provide an AWS Command Line Interface which gives the ability to gather information from the command prompt, and potentially create text files and load these into a database for better reporting.

I used this guide : https://docs.aws.amazon.com/cli/latest/userguide/awscli-install-windows.html

Download the installer and run it

Download and install Python V3.6 from https://www.python.org/downloads/release

Make sure you select ‘Add to path’

Once installed, run

pip install awscli

from the command prompt

Run this to upgrade to the latest version

pip install --user --upgrade awscli




You may get another prompt if you are not using the latest version of something – run the command that will be displayed

Close the command window and re-open

Log into the AWS console and select ‘My Security Credentials’

Click on ‘Access keys…’

Click on ‘Create New Access Key’

The key will be created – make a note and / or download the file

Go to the RDS instance in the console, and note the Availability Zone

Go to the command prompt, run

aws configure

And enter the information – note the default region name drops off the last character. If in doubt, go to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.RegionsAndAvailabilityZones.html

If all the information is correct, you will now be able to run commands against your instances (if you are using multi-factor authentication, see below)

aws rds describe-db-instances

You can basically do anything

aws rds stop-db-instance –db-instance-identifier MGAMON

If you are using Multi Factor Authentication (MFA), then this won’t work. You need to run a command to generate temporary credentials by entering the code from the Google Authenticator app.

You need to know your ARN – go to IAM from the AWS console and navigate to your username

You then run the command, and put the token code in

aws sts get-session-token --serial-number arn:aws:iam::83XXXXXXXX64:mfa/user.name --token-code 537662

This will return a set of credentials

CREDENTIALS     ASIAXXXXXXXXXXXXXZ7JK5A    2018-09-06T17:22:59Z    i5gVyiw+LXRsbUnv1GD/XXXXXXXXXXXXXU6nLk0BtA        FQoGZXIvYXdzEHcaDJ1kZm7jTMh0OI95gCKwAVbTGneVDrFuTbJedKs/oir7D7nT+JQZasMGkhOWaO8X0XnopSFIB+XXXXXXXXXXXXXXXXXo9XOUa8nCodgjH4IWxR2YWdWPmNi3YD5z0FvI+TH0KYExCg0ScceGVxxxxxxxxxxxxxxxxxDgf9syGrVNwqnI9JP2GdgUVp+XXXXXXXJxsZAVjChAIRgEb82LCpsgtqtJWlXXXXXXXXXXXXXXPzwtwF

These are valid for 12 hours.

Set them as environment variables:

export AWS_ACCESS_KEY = ASIAXXXXXXXXXXXXXZ7JK5A

export AWS_SECRET_ACCESS_KEY= i5gVyiw+LXRsbUnv1GD/XXXXXXXXXXXXXU6nLk0BtA

export AWS_SESSION_TOKEN=FQoGZXIvYXdzEHcaDJ1kZm7jTMh0OI95gCKwAVbTGneVDrFuTbJedKs/oir7D7nT+JQZasMGkhOWaO8X0XnopSFIB+XXXXXXXXXXXXXXXXXo9XOUa8nCodgjH4IWxR2YWdWPmNi3YD5z0FvI+xxxxxxxxxxxxxxxxxGrVNwqnI9JP2GdgUVp+XXXXXXXJxsZAVjChAIRgEb82LCpsgtXXXXXXXXXXXXXXLPzwtwF

Create a profile in the .aws/config file with the target role

[default]

aws_access_key_id=AKIAXXXXXXXXXXXXXXXBJJQ

aws_secret_access_key=S+XXXXXXXXXXXXXXXXXXXXXDZk6deZjXXXWw

output = text

region = ap-southeast-2

[profile etrm]

role_arn=arn:aws:iam::32XXXXXXXXXX4:role/UsersMGA

source_profile=default

output=text

region=ap-southeast-2

mfa_serial=arn:aws:iam::83XXXXXXXXX4:mfa/user.name

Note the mfa_serial has “mfa” and not “user” – it won’t work otherwise

You can then run commands and it won’t prompt for the key

aws rds describe-db-instances --profile etrm

DBINSTANCES     5120    False   ap-southeast-2c 7       rds-ca-2015             True            arn:aws:rds:ap-southeast-2:32XXXXXXXX814:db:allegrordsprod       db.m4.4xlarge   allegrordsprod  available             0       db-HDLEXXXXXXXXXRGIXXXY   sqlserver-ee    12.00.5571.0.v1 arn:aws:logs:ap-southeast-2:3XXXXXX4:log-group:RDSOSMetrics:log-stream:db-HDLXXSP4XXXXXXXXX4Y     False    2017-12-05T22:45:06.679Z                2018-09-09T22:54:01Z    license-included        administrator   1       arn:aws:iam::32XXXXXXXXX4:role/rds-monitoring-role      True    False   22:00-22:30   sat:02:00-sat:08:00             False   ap-southeast-2a False   gp2     E. Australia Standard Time

DBPARAMETERGROUPS       prodetrmstacks-rds-paramgroups-allegroparams-1jbXXXXX5m      in-sync

DBSUBNETGROUP   Internal RDS Subnet     prodetrmstacks-rds-subnetgroups-rdsinternal-1XXXXXXXXX05       Complete        vpc-6XXXX05

SUBNETS subnet-dXXd Active

SUBNETAVAILABILITYZONE  ap-southeast-2a

SUBNETS subnet-0XXXXXX55 Active

SUBNETAVAILABILITYZONE  ap-southeast-2c

SUBNETS subnet-dXXXXXX9 Active

SUBNETAVAILABILITYZONE  ap-southeast-2b

ENDPOINT        allegrordsprod.cqmXXXXXX0f.ap-southeast-2.rds.amazonaws.com    Z32TXXXXXXXS0V  1433

OPTIONGROUPMEMBERSHIPS  sqlserverwithbackup     in-sync

VPCSECURITYGROUPS       active  sg-1XXX3

aws rds describe-db-snapshots --max-items 5 --profile etrm

DBSNAPSHOTS     4000    ap-southeast-2c allegrordsprod  arn:aws:rds:ap-southeast-2:32xxxxxxxxx4:snapshot:allegropreprodexport   allegropreprodexport    True    sqlserver-ee    12.00.5546.0.v1 False2017-12-05T22:45:06.679Z         arn:aws:kms:ap-southeast-2:325xxxxxx4:key/9bb411XXXXXXXXXXXXXXX15-95a1-9xxxxxac        license-included        administrator   sqlserverwithbackup     100     1433 2018-03-06T09:31:08.027Z manual  arn:aws:rds:ap-southeast-2:32xxxxxx4:snapshot:rds:allegrordsprod-2018-03-05-22-13    ap-southeast-2  available       gp2     E. Australia Standard Time      vpc-6XXXX

DBSNAPSHOTS     5120    ap-southeast-2c allegrordsprod  arn:aws:rds:ap-southeast-2:32xxxxxx4:snapshot:allegroprodexport      allegroprodexport       False   sqlserver-ee    12.00.5571.0.v1 False2017-12-05T22:45:06.679Z                 license-included        administrator   sqlserverwithbackup     100     1433    2018-09-09T22:33:04.270Z        manual  arn:aws:rds:ap-southeast-2:32xxxxxxx4:snapshot:rds:allegrordsprod-2018-09-08-22-13  ap-southeast-2  available       gp2     E. Australia Standard Time      vpc-6XXX

DBSNAPSHOTS     5120    ap-southeast-2b allegrordspreprod       arn:aws:rds:ap-southeast-2:32xxxxxx4:snapshot:allegrordspreprod-2018-08-13   allegrordspreprod-2018-08-13    False   sqlserver-ee 12.00.5571.0.v1  False   2018-08-07T01:22:50.677Z                        license-included        administrator   sqlserverwithbackup     100     1433    2018-08-13T07:35:26.984Z        manual       available        gp2     E. Australia Standard Time      vpc-6XXXXXXXX5

DBSNAPSHOTS     4500    ap-southeast-2a allegrordspreprod       arn:aws:rds:ap-southeast-2:32xxxxxxx4:snapshot:allegrordspreprod-final-snapshot       allegrordspreprod-final-snapshot        Falsesqlserver-ee     12.00.5546.0.v1 False   2018-03-07T04:39:20.230Z        20000           license-included        administrator   sqlserverwithbackup     100     1433    2018-03-15T05:01:18.538Z     manual                   available       io1     E. Australia Standard Time      vpc-61XXX5

DBSNAPSHOTS     5120    ap-southeast-2c allegrordsprod  arn:aws:rds:ap-southeast-2:32xxxxxxx4:snapshot:allegrordsprod-manual  allegrordsprod-manual   False   sqlserver-ee    12.00.5546.0.v1 False2017-12-05T22:45:06.679Z                 license-included        administrator   sqlserverwithbackup     100     1433    2018-06-13T05:31:03.012Z        manual  arn:aws:rds:ap-southeast-2:32xxxxxxxxx4:snapshot:rds:allegrordsprod-2018-06-12-22-13  ap-southeast-2  available       gp2     E. Australia Standard Time      vpc-6XXXXXX5

NEXTTOKEN       eyJNYXJrxxxxxxxxxxxxxxxxx1bnQiOiA1fQ==