Oracle - Rotate/backup listener.log file

Stop log generation into listener logfile

[host11:oracle:/home/oracle]lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 22-NOV-2016 15:45:39

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to OFF
The command completed successfully

In different window, change listener log filename

[host111:oracle:/home/oracle]mv listener.log listener_1.log

Return to previous window and start log generation

LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL>

Archive old listener logfile

[host111:oracle:/home/oracle]tar -cvf - listener_1.log | gzip > listener_1.tar.gz

Oracle - Rotate/backup listener.log file

Stop log generation into listener logfile

[host11:oracle:/home/oracle]lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 22-NOV-2016 15:45:39

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to OFF
The command completed successfully

In different window, change listener log filename

[host111:oracle:/home/oracle]mv listener.log listener_1.log

Return to previous window and start log generation

LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL>

Archive old listener logfile

[host111:oracle:/home/oracle]tar -cvf - listener_1.log | gzip > listener_1.tar.gz

SQL Server - Execute dynamic SQL with parameters

Example of procedure running an dynamic SQL based on procedure's parameter:

CREATE PROCEDURE [dbo].[SP_DYNAMIC_SQL_TEST] 
(@V_PARAMETER INT) 
AS
BEGIN

SET NOCOUNT ON 

DECLARE @delete_sql nvarchar(1000)
DECLARE @ParmDefinitionDel NVARCHAR(500)

SET @delete_sql = 'DELETE TB_DYNAMIC_SQL'

IF (@V_PARAMETER IS NOT NULL) 
SET @delete_sql = @delete_sql + ' WHERE COLUMN_NAME = @V_PARAMETER_DYN'

       SET @ParmDefinitionDel = N'@V_PARAMETER_DYN INT'

       PRINT @delete_sql

       EXEC sp_executesql @delete_sql, @ParmDefinitionDel, @V_PARAMETER_DYN = @V_PARAMETER

END

Oracle - Sessions details using UNDO

SELECT r.NAME "Undo Segment Name", dba_seg.size_mb,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || 
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, 
v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
v$session.USERNAME, v$session.STATUS, action 
FROM v$lock l, v$process p, v$rollname r, v$session, 
(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg 
WHERE l.SID = p.pid(+) AND 
v$session.SID = l.SID AND 
TRUNC (l.id1(+)/65536)=r.usn AND 
l.TYPE(+) = 'TX' AND 
l.lmode(+) = 6 
AND r.NAME = dba_seg.segment_name
--AND v$session.username = 'SYSTEM'
--AND status = 'INACTIVE'
ORDER BY size_mb DESC;

Oracle - List all my tablespaces free space (autoextend on)

WITH tmp AS
    (
        SELECT file_id, tablespace_name, file_name,
               DECODE (autoextensible,
                       'YES', GREATEST (BYTES, maxbytes),
                       BYTES
                      ) mysize,
              DECODE (autoextensible,
                      'YES', CASE
                         WHEN (maxbytes > BYTES)
                            THEN (maxbytes - BYTES)
                         ELSE 0
                      END,
                      0
                     ) growth
         FROM dba_data_files)
SELECT   tmp.tablespace_name,
         ROUND (SUM (tmp.mysize) / (1024 * 1024)) total_size,
         ROUND (SUM (growth) / (1024 * 1024)) growth,
         ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) datafile_free_space,
         ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
               ) total_free_space,
         ROUND (  (SUM (NVL (freebytes, 0)) + SUM (growth))
                 / SUM (tmp.mysize)
                 * 100
               ) percent_free
    FROM tmp, (SELECT   file_id, SUM (BYTES) freebytes
                      FROM dba_free_space
                  GROUP BY file_id) dfs
   WHERE tmp.file_id = dfs.file_id(+)
GROUP BY tmp.tablespace_name
ORDER BY 6 DESC

SQL Server - Free space management

Check free space on server partititons

EXEC MASTER..xp_fixeddrives
GO


SELECT DISTINCT dovs.logical_volume_name AS LogicalDriveName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceMB ASC

GO

Check free space in database files

SELECT DB_NAME() AS DbName, 
name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files with (NOLOCK);


Shrink database files in small chunks

DECLARE @StartSize INT 
DECLARE @TargetSize INT

SET   @StartSize  = -- SET START SIZE OF THE DATABASE FILE (MB)
Set   @TargetSize = -- SET END SIZE OF THE DATABASE FILE (MB)

WHILE @StartSize > @TargetSize
BEGIN
SET @StartSize = @StartSize - 512
    DBCC SHRINKFILE (N'file name' , @StartSize)
END

GO

View shrink operation details


SELECT 
    d.name,
    percent_complete, 
    session_id,
    start_time, 
    status, 
    command, 
    estimated_completion_time, 
    cpu_time, 
    total_elapsed_time
FROM 
    sys.dm_exec_requests E left join
    sys.databases D on e.database_id = d.database_id
WHERE
    command in ('DbccFilesCompact','DbccSpaceReclaim')



select DST.text, DMR.Status, DMR.Command, DatabaseName = db_name(R.database_id), DMR.cpu_time, R.total_elapsed_time, DMR.percent_complete
from sys.dm_exec_requests DMR

cross apply sys.dm_exec_sql_text(DMR.sql_handle) DST

SQL Server - Get filegroup and files space details

SELECT
b.groupname AS 'File Group',
Name,
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))
[Currently Allocated Space (MB)],
CONVERT (Decimal(15,2),
ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))
AS [Space Used (MB)],
CONVERT (Decimal(15,2),
ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))
AS [Available Space (MB)]
FROM dbo.sysfiles a (NOLOCK)
JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
ORDER BY b.groupname

SQL Server - Get rows and space details for all tables in database

with tempspace as (
SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows)

select SchemaName, TableName, 
sum(RowCounts) as RowsCount, 
sum(TotalSpaceKB)/1024/1024 as TotalGB, 
sum(UsedSpaceKB)/1024/1024 as UsedGB, 
sum(UnusedSpaceKB)/1024/1024 as UnusedGB
from tempspace 
group by SchemaName, TableName
order by TotalGB desc, SchemaName, TableName

SQL Server 2012 - Truncate table partitions

1. Create staging table [staging_TABLE_NAME]. The best way to create it is from SSMS 

ServerName -> Databases -> DatabaseName -> Tables -> TABLE_NAME (Right click) -> Storage -> Manage partition -> Create a staging table for partition switching

In the generated script comment ALTER TABLE lines with constraint on partition limits.

Execute script to create staging table.

Update in truncate partition script staging_TABLE_NAME with generated staging table name.

2. Get details about table partititons

DECLARE @TableName NVARCHAR(200) = N'TABLE_NAME'

SELECT SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(i.object_id) AS [object]
     , p.partition_number AS [p#]
     , fg.name AS [filegroup]
     , p.rows
     , au.total_pages AS pages
     , CASE boundary_value_on_right
       WHEN 1 THEN 'less than'
       ELSE 'less than or equal to' END as comparison
     , rv.value
     , CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
       SUBSTRING (au.first_page, 5, 1))) + ':' + CONVERT (VARCHAR(20),
       CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
       SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +
       SUBSTRING (au.first_page, 1, 1))) AS first_page
FROM sys.partitions p
INNER JOIN sys.indexes i
     ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.objects o
     ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au
     ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
     ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f
     ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
     ON dds.partition_scheme_id = ps.data_space_id
     AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
     ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values rv
     ON f.function_id = rv.function_id
     AND p.partition_number = rv.boundary_id
WHERE i.index_id < 2
     AND o.object_id = OBJECT_ID(@TableName)
order by 2;

3. Execute truncate script for selected partitions. To execute also the commands un-comment EXEC lines. Test it before execution in ptoduction like environment !!!!

DECLARE @partno nvarchar(5)
DECLARE @cmd1 nvarchar(200)
DECLARE @cmd2 nvarchar(200)
SELECT @cmd2 = 'TRUNCATE TABLE [staging_TABLE_NAME]'

DECLARE table_names CURSOR FOR
SELECT distinct partition_number -- partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TABLE_NAME'
and partition_number between 20 and 100
order by partition_number

OPEN table_names
FETCH NEXT FROM table_names INTO @partno
WHILE @@fetch_status = 0
BEGIN

IF EXISTS (SELECT partition_number -- partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TABLE_NAME'
and partition_number = @partno)

BEGIN

SELECT @cmd1 = 'ALTER TABLE [DBNAME].[dbo].[TABLE_NAME] SWITCH PARTITION ' + @partno + ' TO [DBNAME].[dbo].[staging_TABLE_NAME]'
   
PRINT @cmd1
PRINT @cmd2
-- EXEC sp_executeSQL @cmd1;
-- EXEC sp_executeSQL @cmd2;
END

FETCH NEXT FROM table_names INTO @partno
END

CLOSE table_names
DEALLOCATE table_names

SQL Server - Get table partitions details

SELECT    
     p.partition_id                             as PartitionID
    ,t.name                                     as [Table]
    ,ps.name                                    as PartitionScheme
    ,pf.name                                    as PartitionFunction
    ,p.partition_number                         as [Partition]
    ,p.rows                                     as [Rows]
    ,prv.value                                  as Boundary
    --,pf.type_desc                               as BoundaryType
    ,case when pf.boundary_value_on_right = 1
        then 'Right'
        else 'Left'
        end                                     as BoundarySide

FROM
    sys.tables t
    inner join sys.partitions p
        on t.object_id = p.object_id
        and p.index_id = 1
    inner join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
    inner join sys.data_spaces ds on i.data_space_id = ds.data_space_id
    inner join sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
    inner join sys.partition_functions pf on ps.function_id = pf.function_id
    left outer join sys.partition_range_values prv
        on pf.function_id = prv.function_id
        and p.partition_number = prv.boundary_id
WHERE
    is_ms_shipped = 0 and
    t.name = 'TABLE_NAME'  
    and exists (select 1 from sys.partitions p2
                where t.object_id = p2.object_id
                and partition_number > 1)
order by
     t.name
    ,p.partition_number desc

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;

2. 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.

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

Set backup jobs for all databases to Disabled.

4. 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;

5. 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

6. REBOOT New standby SERVER

7. 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;

8. 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;

9. Enable scheduled tasks

Enable Scheduled tasks

2. 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;

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

Set backup jobs for all databases to Disabled.

3. 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

4. REBOOT Standby SERVER

5. 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;

6. Enable scheduled tasks on Standby server

Enable Scheduled tasks

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

 

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>