Friday, August 5, 2016

11g - Data Guard - Primary/Standby Windows Server reboot


  1. Procedure for Primary Oracle server reboot

  1. Create safe backups for Primary and Standby databases


C:\Users\oracle>set oracle_sid=<SID_NAME>
C:\Users\oracle> rman target /
RMAN> backup database current controlfile;
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE CURRENT CONTROLFILE PLUS ARCHIVELOG;

Check Oracle RMAN status:

SQL> select sid, start_time,(sofar/totalwork) * 100 done,
to_char(sysdate + time_remaining/3600/24,'dd-mm-yyyy hh24:mi')
from v$session_longops
where opname not like '%aggregate%' and opname like 'RMAN%' and totalwork > sofar;

  1. Stop Oracle Enterprise Manager Console


C:\Users\oracle>set oracle_sid=<SID_NAME>
C:\Users\oracle>emctl status dbconsole
Oracle Enterprise Manager 11g is running.
C:\Users\oracle>emctl stop dbconsole
The OracleDBConsole<DB_NAME> service was stopped successfully.

  1. Disable Primary and Standby scheduled backup tasks that might interfere with switchover and reboot activity


Set backup jobs for all databases to Disabled.

  1. Switchover databases to Standby


Check gaps for online redo
  • on primary :
SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

  • on standby :
SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES'
AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;

Check the difference (should not be bigger the 1 or 2 according to Oracle documentation)

Check no jobs are running (automatic jobs starting at 22:00 should be finished) :
SQL> SELECT * FROM DBA_JOBS_RUNNING;

Tail alert log files (optional)
  • With Power Shell:
PS C:\Users\oracle> Get-Content E:\app\oracle\diag\rdbms\<db_name>\<SID>\trace\alert_<sid>.log -wait

Perform switchover
  • Connect to the primary instance and perform the switchover
DGMGRL> CONNECT SYS/password@primary
DGMGRL> SHOW CONFIGURATION
DGMGRL> SWITCHOVER TO <standby database name>;
  • Check the new configuration
DGMGRL> SHOW CONFIGURATION

  • on the new primary  check on each instance if the services are running:

SQL> SELECT NAME FROM V$SERVICES; 
  • on the new standby check on each instance if the services are NOT running:

SQL> SELECT NAME FROM V$SERVICES;


  1. Prepare server for reboot


  • Connect to observer server

C:\Users\oracle>dgmgrl /@<primary_db_name>
DGMGRL> show configuration;
Fast-Start Failover: ENABLED
DGMGRL> disable fast_start failover;
Disabled.
DGMGRL> show configuration;
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

  • Checking New Primary/New standby:

C:\Users\oracle> sqlplus /nolog
SQL> conn / as sysdba
SQL> select db_unique_name, database_role, open_mode from v$database;

  • New primary:

SQL> show parameter log_archive_dest_state_2;
SQL> alter system set log_archive_dest_state_2=defer scope=both;
SQL> alter system switch logfile;

  • New standby:
SQL> shutdown immediate

  • Stop Oracle services on New standby

  1. REBOOT New standby SERVER


  1. Startup Oracle services and instances on New standby server reboot


  • Start Oracle services on New standby
  • Startup database in MOUNT state

C:\Users\oracle> set oracle_sid=<SID_NAME>
C:\Users\oracle> sqlplus /nolog
SQL> conn / as sysdba
SQL> startup mount
SQL> select db_unique_name, database_role, open_mode from v$database;

  • Check database and alert.log to see that log_archive_dest_state_2 is set automatically to ENABLE.

SQL> show parameter log_archive_dest_state_2;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE

  • Enable fast start failover

C:\Users\oracle> dgmgrl /@<db_name>
DGMGRL> show database verbose <primary database>;
DGMGRL> enable fast_start failover;
DGMGRL> show configuration verbose;

  1. Switchover databases to Primary


Check gaps for online redo
  • on New primary :
SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

  • on New standby :
SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
WHERE APPLIED = 'YES'
AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;

Check the difference (should not be bigger the 1 or 2 according to Oracle documentation)

Check no jobs are running (automatic jobs starting at 22:00 should be finished) :
SQL> SELECT * FROM DBA_JOBS_RUNNING;

Tail alert log files (optional)
  • With Power Shell:
PS C:\Users\oracle> Get-Content E:\app\oracle\diag\rdbms\<db_name>\<SID>\trace\alert_<sid>.log -wait

Perform switchover
  • Connect to the new primary instance and perform the switchover
DGMGRL> CONNECT SYS/password@New primary
DGMGRL> SHOW CONFIGURATION
DGMGRL> SWITCHOVER TO <Primary database name>;
  • Check the new configuration
DGMGRL> SHOW CONFIGURATION

  • on the primary check on each instance if the services are running:

SQL> SELECT NAME FROM V$SERVICES; 
  • on the new standby check on each instance if the services are NOT running:

SQL> SELECT NAME FROM V$SERVICES;

  1. Enable scheduled tasks


Enable Scheduled tasks




  1. Procedure for STANDBY Oracle server reboot


  1. Create safe backups for Primary and Standby databases


C:\Users\oracle>set oracle_sid=<SID_NAME>
C:\Users\oracle> rman target /
RMAN> backup database current controlfile;
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE CURRENT CONTROLFILE PLUS ARCHIVELOG;

Check Oracle RMAN status:

SQL> select sid, start_time,(sofar/totalwork) * 100 done,
to_char(sysdate + time_remaining/3600/24,'dd-mm-yyyy hh24:mi')
from v$session_longops
where opname not like '%aggregate%' and opname like 'RMAN%' and totalwork > sofar;

  1. Disable DCO scheduled backup tasks that might interfere with reboot activity


Set backup jobs for all databases to Disabled.

  1. Prepare server for reboot (stop databases and Oracle services)


  • Connect to observer server

C:\Users\oracle>dgmgrl /@<primary_db_name>
DGMGRL> show configuration;
Fast-Start Failover: ENABLED
DGMGRL> disable fast_start failover;
Disabled.
DGMGRL> show configuration;
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

  • Checking Primary/standby:

C:\Users\oracle> sqlplus /nolog
SQL> conn / as sysdba
SQL> select db_unique_name, database_role, open_mode from v$database;

  • primary:

SQL> show parameter log_archive_dest_state_2;
SQL> alter system set log_archive_dest_state_2=defer scope=both;
SQL> alter system switch logfile;

  • standby:
SQL> shutdown immediate

  • Stop Oracle services on standb

  1. REBOOT Standby SERVER

  1. Startup Oracle services and instances on Standby server after server reboot

  • Start Oracle services on standby
  • Startup databases in MOUNT state

C:\Users\oracle> set oracle_sid=<SID_NAME>
C:\Users\oracle> sqlplus /nolog
SQL> conn / as sysdba
SQL> startup mount
SQL> select db_unique_name, database_role, open_mode from v$database;

  • Check database and alert.log to see that log_archive_dest_state_2 is set automatically to ENABLE.

SQL> show parameter log_archive_dest_state_2;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE

  • Enable fast start failover

C:\Users\oracle> dgmgrl /@<db_name>
DGMGRL> show database verbose <primary database>;
DGMGRL> enable fast_start failover;
DGMGRL> show configuration verbose;

  • Select the time of action

SQL> set linesize 200
SQL> select (select (to_char(sysdate,'dd - mm - yyyy hh24:mi:ss')) from dual) as TimeofExec, db_unique_name,
database_role, open_mode from v$database;


  1. Enable scheduled tasks on Standby server

Enable Scheduled tasks
Posted by at No comments:
Labels:

11g - Reconfigure EM console


C:\Users>set oracle_unqname=orcl
C:\Users>emca -config dbcontrol db -repos recreate -reconfig ports -dbcontrol_http_port 5501

Optional, reconfigure EM console port

C:\Users>emca -reconfig ports -DBCONTROL_HTTP_PORT 5501

Check EM console status

C:\Users>emctl status dbconsole
 
Posted by at No comments:
Labels:

11g - DataGuard change SYS password


PRIMARY WIN-PRYORA01
STANDBY WIN-SECORA01
OBSERVER WIN-OBSORA01


On WIN-OBSORA01 (Check Oracle Wallet)

C:\Users\oracle>mkstore -wrl "E:\app\oracle\product\11.2.0\dbhome_1\BIN\owm\wallets\oracle" -listCredential
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

List credential (index: connect_string username)
2: testaic2 sys
1: testaic sys

C:\Users\oracle>

On WIN-OBSORA01 (Check observer status)

C:\Users\oracle>dgmgrl /@testaic
DGMGRL for 64-bit Windows: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration verbose

Configuration - testaic

Protection Mode: MaxAvailability
Databases:
testaic - Primary database
testaic2 - (*) Physical standby database

(*) Fast-Start Failover target

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: ENABLED

Threshold: 30 seconds
Target: testaic2
Observer: WIN-OBSORA01
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE

Configuration Status:
SUCCESS

C:\Users\oracle>dgmgrl /@testaic
DGMGRL for 64-bit Windows: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> disable fast_start failover;
Disabled.
DGMGRL> exit

On WIN-PRYORA01 (Disable log transport)

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter user sys identified by "NewPassword";

User altered.

Copy PWDtestaic.ORA to WIN-SECORA01, and rename it to PWDtestaic2.ORA

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;

System altered.

SQL>



On WIN-OBSORA01 (Enable fast_start failover)

C:\Users\oracle>dgmgrl sys/NewPassword@testaic
DGMGRL for 64-bit Windows: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> enable fast_start failover;
Enabled.
DGMGRL> exit

!!!!! The observer still connects with old SYS password (no password set for password file).

C:\Users\oracle>dgmgrl /@testaic2
DGMGRL for 64-bit Windows: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL>

On WIN-OBSORA01 (Reconfigure users in wallet)

C:\Users\oracle>mkstore -wrl "E:\app\oracle\product\11.2.0\dbhome_1\BIN\owm\wallets\oracle" -deleteCredential testaic
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: 

Delete credential
Delete 1

C:\Users\oracle>mkstore -wrl "E:\app\oracle\product\11.2.0\dbhome_1\BIN\owm\wallets\oracle" -createCredential testaic sys NewPassword
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: 

Create credential oracle.security.client.connect_string1

Same steps for testaic2

Changing password for SYSTEM user

On WIN-PRYORA01 (Disable log transport)

SQL> alter user system identified by "NewPassword";

User altered.

SQL> conn system/1qazxsw2@testaic
Connected.
SQL> conn system/1qazxsw2@testaic2
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


Warning: You are no longer connected to ORACLE.

Testing new password on secondary

On WIN-OBSORA01 (only for test)

C:\Users\oracle>dgmgrl /@testaic
DGMGRL for 64-bit Windows: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> switchover to testaic2
Performing switchover NOW, please wait...
Operation requires a connection to instance "testaic2" on database "testaic2"
Connecting to instance "testaic2"...
Connected.
New primary database "testaic2" is opening...
Operation requires startup of instance "testaic" on database "testaic"
Starting instance "testaic"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "testaic2"
DGMGRL>

C:\Users\oracle>sqlplus system/NewPassword@testaic2

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 5 11:29:51 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

C:\Users\oracle>sqlplus system/NewPassword@testaic

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 5 11:29:58 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


Enter user-name:
C:\Users\oracle>
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)