Posts

Showing posts from November, 2017

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:

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=

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/ -createC

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