Posts

Featured Post

Log mining a Redo log file / Archive log file on a remote database

Image
At the source site   Enable the database to write to a directory you choose, in this example I use /tmp alter system set utl_file_dir='/tmp' scope=spfile;  Restart of the database is required in order for the parameter utl_file_dir be active
Extract the LogMiner dictionary to a flat file called dictionary.ora
EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', '/tmp/', DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);  Copy redo log file from source to target:Copy redo log file from ASM to the operating system asmcmd cp '+DATA/PDB/ONLINELOG/group_1.259.919359545' /tmp/ Copy the the redo log file to the target host  scp /tmp/dictionary.ora /tmp/group_1.259.919359545 target_host:/tmp Copy the dictionary file from source to target  scp /tmp/dictionary.ora /tmp/group_1.259.919359545 target_host:/tmp  On target db  alter system set utl_file_dir='/tmp' scope=spfile;  Restart of the database is required in order for the parameter utl_file_dir be active
Add the first redo …

Resolve tns alias from the database (DBMS_TNS.RESOLVE_TNSNAME)

Image
When a given tns alias is not working, via SQL*Plus, database link or within data guard, traditionally I troubleshoot connectivity via tnsping utility, at first just to understand the target host, port and service name.

In oracle 12.2 we have a new function called DBMS_TNS.RESOLVE_TNSNAME that enables to query the return string as we receive it from tnsping.
Using oracle utility tnsping from the command line $ tnsping stndby_db

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 13-NOV-2017 14:45:39

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stndby_host)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stndby_db)))
OK (0 msec) Using dbms_tns SQL> select dbms_tns.resolve_tnsname ('stndby_db') as TNS_ALIAS from dual;
TNS_ALIAS
------------------…

Starting the Oracle Data Guard Broker OBSERVER in the BACKGROUND

Image
Prior to Oracle 12.2 we had to find some homemade tricks to run the Observer at the background this way:
$ nohup dgmgrl -silent sys/P@$$w0rd@prmy_db "start observer" & Finally, From Oracle 12.2 the Observer can run in the background using wallet authentication to connect to primary & standby databases and the far sync instance.

For configuring the wallet use the steps described in my post: "Creating a wallet - secure external password store"

Starting the observer at the background using the wallet credentials:
DGMGRL> START OBSERVER myobserver IN BACKGROUND FILE IS observer.dat LOGFILE IS observer.log CONNECT IDENTIFIER IS prmy_db
Submitted command "START OBSERVER" using connect identifier "prmy_db"  You can follow the progress of the observer looking at the logfile:
$ tail observer.log
Connected to "prmy_db"
Connected as SYSDBA.
[W000 11/10 21:14:08.47] FSFO target standby is stndby_db
[W000 11/10 21:14:11.45] Observer …

Setting up an Observer

Image
Setting up an Observer
Observer is an automatic data guard broker, it can be installed with either the Oracle Client Administrator software or the full Oracle Database software.
It is preferable that the observer would run on a different host than the primary and standby databases, using the same network as any end-user client or application.
The host should be located In a third, independent location or at least isolated as much as possible from the standby database.
Make the observer highly available by configuring auto restart on the same server or on an alternate host.
These are the steps for setting up the observer:

Enable flashback on Primary & Standby:
Primary:
sqlplus> alter database flashback on ; Standby:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database flashback on ;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; Update db_lost_write_protect on Primary & Standby:
SQL> alter system set db_lost_write_protect='TYPICAL' s…

Creating a wallet - secure external password store

Image
Creating a wallet - secure external password store
Oracle wallet enables autologin feature without supplying a password. It is no longer a part of Oracle Advanced Security and available in all licensed editions of all supported releases of the Oracle database.
Prepare a secured folder for the wallet

$ mkdir -p $ORACLE_HOME/owm/wallets/oracle
$ chmod -R 700 $ORACLE_HOME/owm/wallets Create the wallet $ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle/ -create
Oracle Secret Store Tool : Version 12.2.0.1.0
Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights
reserved.

Enter password:
Enter password again: Edit sqlnet.ora and add the wallet location created in the previous step $ vi $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION=(
SOURCE=(METHOD=FILE)
(METHOD_DATA=(DIRECTORY=/oracle/product/12.2.0.1/dbhome_1/owm/wallets/oracle/))
) Add credentials $ mkstore -wrl $ORACLE_HOME/owm/wallets/oracle/ -createCredential prmy_db
sys P@$$w0rd
Oracle Se…

List RMAN backups size

Image
SQL script for calculating the total size of RMAN backups

col TYPE for a20 Heading "Backup Type"
col completion_time Heading "Completion Time"
col MB for 99999999999  Heading "Size (MB)"
col BCPTIME for 99999999999 Heading "Backup Time (minutes)"

SELECT TO_CHAR (completion_time, 'YYYY-MON-DD') completion_time
       , TYPE
       , ROUND (SUM (bytes) / 1048576)          MB
       , ROUND (SUM (elapsed_seconds) / 60)     BCPTIME
    FROM (SELECT CASE
                    WHEN s.backup_type = 'L' THEN 'Archive Log'
--                    WHEN s.controlfile_included = 'YES' THEN 'Control File'
                 WHEN s.backup_type = 'D' THEN 'Full (Level ' || NVL (s.incremental_level, 0) || ')'
                    WHEN s.backup_type = 'I' THEN 'Incemental (Level ' || s.incremental_level || ')'
                    ELSE s.backup_type
                 END
            …

Data Guard / Far Sync Best Practice - control protection levels

I have just came across a meta parameter called DB_ULTRA_SAFE that sets the values for all of mentioned parameters in Far Sync Best Practice Recommendations (for Oracle 12.1).

When DB_ULTRA_SAFE=DATA_ONLY
  •DB_BLOCK_CHECKING => MEDIUM.
  •DB_LOST_WRITE_PROTECT => TYPICAL.
  •DB_BLOCK_CHECKSUM => FULL.

When DB_ULTRA_SAFE=DATA_AND_INDEX
  •DB_BLOCK_CHECKING => FULL.
  •DB_LOST_WRITE_PROTECT => TYPICAL.
  •DB_BLOCK_CHECKSUM => FULL.

When DB_ULTRA_SAFE=OFF
  •When any of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT are explicitly set, no changes are made.