My Certification

Oracle Database 10g OCP
Oracle Database 11g OCP
Oracle Database 12c OCP
Oracle GoldenGate 10 OCS
Oracle 11gR2 RAC OCE
MySQL 5.6 Database OCP
Novell SUSE Linux 10 CLA
IBM Power System AIX 7.1



MySQL 8.0.15 InnoDB Cluster Setup

Prepare hosts
Depending on version of OS their might be a need to tweak port openings, a full guide on what port you need to open is available here.

myport

Continue reading MySQL 8.0.15 InnoDB Cluster Setup

Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (文档 ID 2118136.2)

psu1

 

psu2

 

psu3

Oracle GoldenGate 12C Release 3(12.3.0.1.0) Microservices Architecture

Microservices Architecture is a method or approach to developing applications where an application is deployed as a suite of independently deployed small modular services.
Each module supports a specific business goal and uses a simple, light weight  and well-defined interface to communicate with other sets of services.
Oracle GoldenGate Microservices Architecture (MA)  is a similar architecture based on REST APIs which enable us to configure, monitor, and manage Oracle GoldenGate services using a web-based user interface.
In Oracle GoldenGate 12.3, we have two architectures available for deploying GoldenGate – the (original) Classic Architecture and the new Microservices Architecture.

 

Microservices Architecture in Oracle GoldenGate is comprised of five main components:
Service Manager
Administration Server
Distribution Server
Receiver Server
Performance Metrics Server

MA2

Continue reading Oracle GoldenGate 12C Release 3(12.3.0.1.0) Microservices Architecture

WARNING: Granules of pga_aggregate_target 102 cannot be more than memory_target (104) – sga_target (0) or min_sga (5)

DB实例的alert日志显示以下告警

WARNING: Granules of pga_aggregate_target 102 cannot be more than memory_target (104) – sga_target (0) or min_sga (5)

 

MOS给的解决方案如下

To implement a solution for unpublished Bug:8813366, please execute any of the below alternative solutions:

  • Upgrade to 12.1.

OR

  • Apply patchset 11.2.0.2 in which unpublished Bug:8813366 is fixed.

OR

  • Apply patchset update release (PSU) 11.2.0.1.1 in which unpublished Bug:8813366 is fixed.

OR

OR

  • Use one of following workarounds:

    • Reduce the number of shared pool subpools by setting the init.ora parameter
      "_kghdsidx_count"=2

      OR
       

    • Reduce granule size with init.ora parameter:
      "_ksmg_granule_size"=16777216

      NOTE: These require an outage of the database.

 

 

This warning message is raised because the MEMORY_TARGET/MEMORY_MAX_TARGET and SGA_MAX_SIZE/SGA_TARGET parameters are set or default to minimum values, and that the difference between the parameter values is less than the required PGA_AGGREGATE_TARGET value.  In Oracle 11g the MEMORY_TARGET parameter defines the total amount of memory that Oracle can use, which incorporates both SGA and PGA areas.  Therefore if these are set the same as the SGA_MAX_SIZE/SGA_TARGET parameters or the difference between them is too small, then this leaves no memory available for the PGA, and hence this warning message is raised.

Ensure that the MEMORY_TARGET/MEMORY_MAX_TARGET parameter settings are set to at least the sum of SGA_MAX_SIZE/SGA_TARGET plus the PGA_AGGREGATE_TARGET parameter.

Oracle 11.2.0.4 RAC to Single Instance DataGuard Switchover

Checked all the necessary parameters on both sides.
On Primary
SQL> select name, value
  2    from v$parameter
  3   where name in ('log_archive_dest_1',
  4                  'log_archive_dest_2',
  5                  'log_archive_dest_state_1',
  6                  'log_archive_dest_state_2',
  7                  'fal_client',
  8                  'fal_server',
  9                  'local_listener',
 10                  'remote_listener',
 11                  'standby_archive_dest',
 12                  'standby_file_management',
 13                  'service_names',
 14                  'db_unique_name',
 15                  'instance_name',
 16                  'db_name',
 17                  'db_file_name_convert',
 18                  'log_file_name_convert');
 
NAME                           VALUE
—————————— ——————————————————————————————
db_file_name_convert
log_file_name_convert
log_archive_dest_1             location=+FRADG valid_for=(all_logfiles,all_roles) db_unique_name=zwc
log_archive_dest_2             service=zwcdg lgwr async  valid_for=(online_logfiles,primary_role) db_unique_name=zwcdg
log_archive_dest_state_1       enable
log_archive_dest_state_2       ENABLE
standby_archive_dest           ?/dbs/arch
fal_client                     zwc
fal_server                     zwcdg
standby_file_management        AUTO
instance_name                  zwc1
service_names                  zwc
local_listener                  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.12)(PORT=1521))
remote_listener                zwc-cluster-scan:1521
db_name                        zwc
db_unique_name                 zwc
 
SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/zwcdg/','+DATADG/zwc/','/u01/app/oracle/oradata/zwcdg/','+FRADG/zwc/' scope=spfile sid='*';
 
System altered.
 
SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/zwcdg/','+DATADG/zwc/' scope=spfile sid='*';
 
 
 

Continue reading Oracle 11.2.0.4 RAC to Single Instance DataGuard Switchover

MySQL 8.0.13 MEB Fails to Backup Encrypted (TDE) Tables: “ERROR: Keyring migration error, exited with status: 1″

Using MySQL Enterprise Backup 8.0 or later to backup encrypted tables in MySQL Server 8.0 or later, the following errors occurs.
181207 11:26:07 RDR1    INFO: Active keyring "keyring_file" detected.
181207 11:26:07 RDR1    INFO: Invoking the keyring migration tool.
181207 11:26:07 RDR1    INFO: Keyring migration tool start.
181207 11:26:04 RDR1    INFO: Keyring migration tool execution complete.
181207 11:26:04 RDR1 ERROR: Error response from tool Enter password: 2018-12-07T01:26:04.161407Z 0 [System] [MY-010116] [Server] /opt/mysql-server/bin/mysqld (mysqld 8.0.13-commercial) starting as process 23964
2018-12-07T01:26:04.227796Z 0 [ERROR] [MY-013106] [Server] Can not perform keyring migration : Failed to disable keyring_operations variable..
2018-12-07T01:26:04.227796Z 0 [ERROR] [MY-013106] [Server] Can not perform keyring migration : Failed to enable keyring_operations variable..
2018-12-07T01:26:04.227796Z 0 [ERROR] [MY-011084] [Server] Keyring migration failed.
2018-12-07T01:26:04.227812Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-12-07T01:26:04.228659Z 0 [System] [MY-010910] [Server] /opt/mysql-server/bin/mysqld: Shutdown complete (mysqld 8.0.13-commercial)  MySQL Enterprise Server – Commercial.

181207 11:26:04 RDR1 ERROR: Keyring migration error, exited with status: 1
181207 11:26:04 RDR1    INFO: Requesting completion of redo log copy. Rapid: 1

mysqlbackup failed with errors!

 


This is caused by the user used by MySQL Enterprise Backup to connect to MySQL Server not having the appropriate privileges. Particularly, it is required for the user to have the ENCRYPTION_KEY_ADMIN global privilege which is required when MySQL Enterprise Backup uses the keyring migration feature to export the keyring to the backup. This is used when MySQL 8.0 or later. The ENCRYPTION_KEY_ADMIN privilege is used to disable keyring operations while the keyring is being backed up.
Ensure the user used by MySQL Enterprise Backup to connect to MySQL Server has the ENCRYPTION_KEY_ADMIN global privilege.

mysql> GRANT ENCRYPTION_KEY_ADMIN ON *.* TO meb@localhost;
Query OK, 0 rows affected (0.09 sec)

Oracle Configure RMAN to purge archivelogs after applied on standby

Prior to 11g, if not using mandatory archivelog destinations, the database (primary and standby) must be restarted with the following parameter.
alter system set "_log_deletion_policy"=ALL scope=spfile sid='*';

 

Warning: In 10g, when set to APPLIED ON STANDBY, then FRA files will be purged even if they aren't backed up.
NOTE: attempt to set this configuration in RMAN without this parameter setting may result in error.

RMAN> configure archivelog deletion policy to applied on standby; 
new RMAN configuration parameters: 
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY; 
new RMAN configuration parameters are successfully stored 
starting full resync of recovery catalog 
full resync complete 
RMAN-08591: WARNING: invalid archivelog deletion policy


Starting from 11g, we have enhanced the configure archivelog deletion policy to include TO APPLIED ON [ALL] STANDBY [BACKED UP n TIMES TO DEVICE TYPE ]. This will ensure that the archivelogs is applied as well as backed up on primary before it is being purged.
The archivelog must have been applied to the standby. Run the following query to list all archivelogs applied to the standby:

select a.thread#, a.sequence#, a.applied
from v$archived_log a, v$database d
where a.activation# = d.activation#
and a.applied='YES'
/

Oracle 11.2.0.4 RAC Active DataGuard to Single Instance FileSystem

Primary database
db_name:zwc
sid:zwc1/zwc2
db_unique_name:zwc
service_names:zwc
 
Standby database
db_name:zwc
sid:zwc
db_unique_name:zwcdg
service_names:zwcdg
 
 
 
On Primary
Enable force logging and archivelog mode.
 
SQL> alter database force logging;
 
Database altered.
 
SQL> select inst_id,dbid,name,db_unique_name,database_role,current_scn,force_logging from gv$database;
 
   INST_ID DBID NAME DB_UNIQUE_NAME        DATABASE_ROLE CURRENT_SCN FOR
———- ———- ——— —————————— —————- ———– —
1  750809337 ZWC zwc        PRIMARY     1121796 YES
2  750809337 ZWC zwc        PRIMARY     1121796 YES
 
SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        +FRADG
Oldest online log sequence     13
Next log sequence to archive   14
Current log sequence        14
 
 
 
Create Standby Read Log Groups.
[grid@zwc1 ~]$ asmcmd 
ASMCMD> ls -l     
State    Type    Rebal  Name
MOUNTED  EXTERN  N      DATADG/
MOUNTED  EXTERN  N      FRADG/
MOUNTED  NORMAL  N      SYSDG/
ASMCMD> mkdir +datadg/zwc/STANDBYLOG
ASMCMD> mkdir +fradg/zwc/STANDBYLOG
ASMCMD> ls -l +datadg/zwc
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 N    STANDBYLOG/
                                                 Y    TEMPFILE/
                                                 N    spfilezwc.ora => +DATADG/ZWC/PARAMETERFILE/spfile.267.994829985
ASMCMD> ls -l +fradg/zwc
Type  Redund  Striped  Time             Sys  Name
                                        Y    ARCHIVELOG/
                                        Y    CONTROLFILE/
                                        Y    ONLINELOG/
                                        N    STANDBYLOG/
 
 
SQL> select max(bytes/1024/1024) "size_MB",count(*) from v$log;
 
   size_MB   COUNT(*)
———- ———-
50     4
 
SQL> select group#,thread# from v$log order by 1,2;
 
    GROUP#    THREAD#
———- ———-
1     1
2     1
3     2
4     2
 
SQL> alter system set standby_file_management=manual scope=both sid='*';
 
System altered.
 
SQL> alter database add standby logfile thread 1 group 5 '+datadg/zwc/STANDBYLOG/standby_group_05.log' size 50M;
 
Database altered.
 
SQL> alter database add standby logfile thread 1 group 6 '+fradg/zwc/STANDBYLOG/standby_group_06.log' size 50M;
 
Database altered.
 
SQL> alter database add standby logfile thread 1 group 7 '+fradg/zwc/STANDBYLOG/standby_group_07.log' size 50M;
 
Database altered.
 
SQL> alter database add standby logfile thread 2 group 8 '+datadg/zwc/STANDBYLOG/standby_group_08.log' size 50M;
 
Database altered.
 
SQL> alter database add standby logfile thread 2 group 9 '+fradg/zwc/STANDBYLOG/standby_group_09.log' size 50M;
 
Database altered.
 
SQL> alter database add standby logfile thread 2 group 10 '+fradg/zwc/STANDBYLOG/standby_group_10.log' size 50M;
 
Database altered.
 
SQL> alter system set standby_file_management=auto scope=both sid='*';
 
System altered.
 
 
 
Backup full database and Create Standby ControlFile.
[oracle@zwc1 ~]$ rman target /
 
Recovery Manager: Release 11.2.0.4.0 – Production on Mon Dec 17 04:19:52 2018
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ZWC (DBID=750809337)
 
RMAN> run {
2> allocate channel c1 type disk;
3> backup database format '/u01/app/bak/FULL_%U.bk';
4> backup archivelog all format '/u01/app/bak/ARCH_%U.arch';
5> release channel c1;
6> }
 
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=67 instance=zwc1 device type=DISK
 
Starting backup at 17-DEC-18
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATADG/zwc/datafile/system.256.994829547
input datafile file number=00002 name=+DATADG/zwc/datafile/sysaux.257.994829547
input datafile file number=00003 name=+DATADG/zwc/datafile/undotbs1.258.994829547
input datafile file number=00005 name=+DATADG/zwc/datafile/undotbs2.264.994829779
input datafile file number=00004 name=+DATADG/zwc/datafile/users.259.994829547
channel c1: starting piece 1 at 17-DEC-18
channel c1: finished piece 1 at 17-DEC-18
piece handle=/u01/app/bak/FULL_06tkvi43_1_1.bk tag=TAG20181217T042019 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:55
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 17-DEC-18
channel c1: finished piece 1 at 17-DEC-18
piece handle=/u01/app/bak/FULL_07tkvi5r_1_1.bk tag=TAG20181217T042019 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-DEC-18
 
Starting backup at 17-DEC-18
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=2 STAMP=994830053
input archived log thread=2 sequence=1 RECID=1 STAMP=994829995
input archived log thread=2 sequence=2 RECID=6 STAMP=994830301
input archived log thread=1 sequence=6 RECID=3 STAMP=994830056
input archived log thread=1 sequence=7 RECID=4 STAMP=994830299
input archived log thread=1 sequence=8 RECID=5 STAMP=994830300
input archived log thread=2 sequence=3 RECID=7 STAMP=995015047
input archived log thread=2 sequence=4 RECID=8 STAMP=995015047
input archived log thread=1 sequence=9 RECID=11 STAMP=995015058
input archived log thread=2 sequence=5 RECID=9 STAMP=995015053
input archived log thread=2 sequence=6 RECID=10 STAMP=995015053
input archived log thread=1 sequence=10 RECID=12 STAMP=995015715
input archived log thread=2 sequence=7 RECID=13 STAMP=995033883
input archived log thread=1 sequence=11 RECID=15 STAMP=995067139
input archived log thread=2 sequence=8 RECID=14 STAMP=995033884
input archived log thread=1 sequence=12 RECID=16 STAMP=995067139
input archived log thread=2 sequence=9 RECID=19 STAMP=995081364
input archived log thread=1 sequence=13 RECID=17 STAMP=995067142
input archived log thread=1 sequence=14 RECID=18 STAMP=995081364
input archived log thread=1 sequence=15 RECID=21 STAMP=995084482
input archived log thread=2 sequence=10 RECID=20 STAMP=995084482
channel c1: starting piece 1 at 17-DEC-18
channel c1: finished piece 1 at 17-DEC-18
piece handle=/u01/app/bak/ARCH_08tkvi65_1_1.arch tag=TAG20181217T042124 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
Finished backup at 17-DEC-18
 
released channel: c1
 
RMAN> backup device type disk format '/u01/app/bak/standby_ctl_%U.ctl' current controlfile for standby;
 
Starting backup at 17-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 instance=zwc1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 17-DEC-18
channel ORA_DISK_1: finished piece 1 at 17-DEC-18
piece handle=/u01/app/bak/standby_ctl_09tkvihc_1_1.ctl tag=TAG20181217T042724 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-DEC-18
 
Recovery Manager complete.
[oracle@zwc1 ~]$ ls -l /u01/app/bak/
total 1233896
-rw-r—– 1 oracle asmadmin  134535680 Dec 17 04:21 ARCH_08tkvi65_1_1.arch
-rw-r—– 1 oracle asmadmin 1095852032 Dec 17 04:21 FULL_06tkvi43_1_1.bk
-rw-r—– 1 oracle asmadmin   18579456 Dec 17 04:21 FULL_07tkvi5r_1_1.bk
-rw-r—– 1 oracle asmadmin   18546688 Dec 17 04:27 standby_ctl_09tkvihc_1_1.ctl
 
 
 
Create Standby pfile.
SQL> create pfile='/u01/app/bak/init_zwc.ora' from spfile;
 
File created.
 
 
 
Transmission to the Standby.
[oracle@zwc1 bak]$ scp -rp /u01/app/bak 192.168.0.207:/u01/app/
[oracle@zwc1 bak]$ cd $ORACLE_HOME/dbs
[oracle@zwc1 dbs]$ scp orapwzwc1 192.168.0.207:/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwzwc
 
 
 
Configuration Primary parameters.
SQL> alter system set fal_client='zwc' sid='*';
 
System altered.
 
SQL> alter system set fal_server='zwcdg' sid='*';
 
System altered.
 
SQL> alter system set log_archive_config='dg_config=(zwc,zwcdg)' sid='*';
 
System altered.
 
SQL> alter system set log_archive_dest_1='location=+FRADG valid_for=(all_logfiles,all_roles) db_unique_name=zwc' sid='*';
 
System altered.
 
SQL> alter system set log_archive_dest_2='service=zwcdg lgwr async  valid_for=(online_logfiles,primary_role) db_unique_name=zwcdg' sid='*';
 
System altered.
 
SQL> alter system set log_archive_max_processes=10 sid='*';
 
System altered.
 
select name, value from v$parameter
 where name in 
 ('db_name',
'db_unique_name',
'log_archive_config',
'log_archive_dest_1',
'log_archive_dest_2',
'log_archive_dest_state_1',
'log_archive_dest_state_2',
'remote_login_passwordfile',
'log_archive_format',
'log_archive_max_processes',
'fal_server',
'db_file_name_convert',
'log_file_name_convert',
 16   'standby_file_management')
 17  /
 
NAME    VALUE
————————————————– —————————————————————————————————-
db_file_name_convert
log_file_name_convert
log_archive_dest_1    location=+FRADG valid_for=(all_logfiles,all_roles) db_unique_name=zwc
log_archive_dest_2    service=zwcdg lgwr async  valid_for=(online_logfiles,primary_role) db_unique_name=zwcdg
log_archive_dest_state_1    enable
log_archive_dest_state_2    enable
fal_server    zwcdg
log_archive_config    dg_config=(zwc,zwcdg)
log_archive_format    %t_%s_%r.dbf
log_archive_max_processes    10
standby_file_management    AUTO
remote_login_passwordfile    EXCLUSIVE
db_name    zwc
db_unique_name    zwc
 
 
 
 
On Standby
Configuration Standby parameters and Create Dir.
 
*.audit_file_dest='/u01/app/oracle/admin/zwcdg/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/zwcdg/controlfile/ctl_zwcdg.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_file_name_convert='+DATADG/zwc/','/u01/app/oracle/oradata/zwcdg/'
*.log_file_name_convert='+DATADG/zwc/','/u01/app/oracle/oradata/zwcdg/','+FRADG/zwc/','/u01/app/oracle/oradata/zwcdg/'
*.db_domain=''
*.db_name='zwc'
*.db_unique_name='zwcdg'
*.fal_client='zwcdg'
*.fal_server='zwc'
*.log_archive_config='dg_config=(zwc,zwcdg)'
*.log_archive_dest_1='location=/u01/app/arch valid_for=(all_logfiles,all_roles) db_unique_name=zwcdg'
*.log_archive_dest_2='service=zwc1 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=zwc'
*.log_archive_dest_3='service=zwc2 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=zwc'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_dest_state_3='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zwcXDB)'
*.memory_target=1112385024
*.open_cursors=300
*.processes=1000
*.remote_login_passwordfile='exclusive'
*.sessions=1105
*.service_names='zwcdg'
*.standby_file_management='AUTO'
*.thread=1
*.undo_tablespace='UNDOTBS1'
 
[oracle@dgdb ~]$ mkdir -p $ORACLE_BASE/admin/zwcdg/{adump,dpdump,hdump,pfile}
[oracle@dgdb ~]$ mkdir -p $ORACLE_BASE/diag/rdbms/zwcdg
[oracle@dgdb ~]$ mkdir -p $ORACLE_BASE/oradata/{zwcdg/{controlfile,datafile,tempfile,onlinelog,standbylog},ZWCDG/onlinelog}
 
 
 
Configuration Listener and TNS (the same primary conf)
[oracle@dgdb admin]$ cat tnsnames.ora 
ZWC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zwc)
    )
  )
  
ZWC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zwc)
    )
  )
  
ZWC2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.14)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zwc)
    )
  )
  
  
ZWCDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.207)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zwcdg)
    )
  )
 
[oracle@dgdb admin]$ cat listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = zwcdg)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME = zwc)
    )
  )
 
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.207)(PORT = 1521))
  )
 
ADR_BASE_LISTENER = /u01/app/oracle
 
[oracle@dgdb admin]$ lsnrctl start
 
 
  
Nomount Standby Database.
SQL> create spfile from pfile='/u01/app/bak/init_zwc.ora';
 
File created.
 
SQL> startup nomount
ORACLE instance started.
 
Total System Global Area  835104768 bytes
Fixed Size     2257840 bytes
Variable Size   708840528 bytes
Database Buffers   121634816 bytes
Redo Buffers     2371584 bytes
SQL> show parameter spfile;
 
NAME      TYPE VALUE
———————————— ———– ——————————
spfile      string /u01/app/oracle/product/11.2.0
.4/db_1/dbs/spfilezwc.ora
 
 
 
RMAN Restore Standby ControlFile and Mount Restore Database.
[oracle@dgdb ~]$ rman target /
 
Recovery Manager: Release 11.2.0.4.0 – Production on Mon Dec 17 05:33:52 2018
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ZWC (not mounted)
 
RMAN> restore standby controlfile from '/u01/app/bak/standby_ctl_09tkvihc_1_1.ctl';
 
Starting restore at 17-DEC-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
 
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/zwcdg/controlfile/ctl_zwcdg.ctl
Finished restore at 17-DEC-18
 
RMAN> alter database mount;
 
database mounted
released channel: ORA_DISK_1
 
RMAN> list backup;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
5       Full    1.02G      DISK        00:00:49     17-DEC-18      
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20181217T042019
        Piece Name: /u01/app/bak/FULL_06tkvi43_1_1.bk
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  —- — —- ———- ——— —-
  1       Full 1126399    17-DEC-18 /u01/app/oracle/oradata/zwcdg/datafile/system.256.994829547
  2       Full 1126399    17-DEC-18 /u01/app/oracle/oradata/zwcdg/datafile/sysaux.257.994829547
  3       Full 1126399    17-DEC-18 /u01/app/oracle/oradata/zwcdg/datafile/undotbs1.258.994829547
  4       Full 1126399    17-DEC-18 /u01/app/oracle/oradata/zwcdg/datafile/users.259.994829547
  5       Full 1126399    17-DEC-18 /u01/app/oracle/oradata/zwcdg/datafile/undotbs2.264.994829779
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
6       Full    17.70M     DISK        00:00:01     17-DEC-18      
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20181217T042019
        Piece Name: /u01/app/bak/FULL_07tkvi5r_1_1.bk
  SPFILE Included: Modification time: 17-DEC-18
  SPFILE db_unique_name: ZWC
  Control File Included: Ckp SCN: 1126473      Ckp time: 17-DEC-18
 
BS Key  Size       Device Type Elapsed Time Completion Time
——- ———- ———– ———— —————
7       128.30M    DISK        00:00:06     17-DEC-18      
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20181217T042124
        Piece Name: /u01/app/bak/ARCH_08tkvi65_1_1.arch
 
  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  —- ——- ———- ——— ———- ———
  1    5       962982     14-DEC-18 968730     14-DEC-18
  1    6       968730     14-DEC-18 968732     14-DEC-18
  1    7       968732     14-DEC-18 992094     14-DEC-18
  1    8       992094     14-DEC-18 992097     14-DEC-18
  1    9       1012186    16-DEC-18 1012294    16-DEC-18
  1    10      1012294    16-DEC-18 1020879    16-DEC-18
  1    11      1020879    16-DEC-18 1093981    16-DEC-18
  1    12      1093981    16-DEC-18 1093985    16-DEC-18
  1    13      1093985    16-DEC-18 1093987    16-DEC-18
  1    14      1093987    16-DEC-18 1122333    17-DEC-18
  1    15      1122333    17-DEC-18 1126495    17-DEC-18
  2    1       968441     14-DEC-18 968560     14-DEC-18
  2    2       968728     14-DEC-18 992114     14-DEC-18
  2    3       992114     14-DEC-18 1012185    16-DEC-18
  2    4       1012185    16-DEC-18 1012189    16-DEC-18
  2    5       1012189    16-DEC-18 1012191    16-DEC-18
  2    6       1012191    16-DEC-18 1012279    16-DEC-18
  2    7       1014094    16-DEC-18 1073922    16-DEC-18
  2    8       1073922    16-DEC-18 1073926    16-DEC-18
  2    9       1093982    16-DEC-18 1122337    17-DEC-18
  2    10      1122337    17-DEC-18 1126492    17-DEC-18
 
RMAN> crosscheck backup;
 
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/bak/FULL_06tkvi43_1_1.bk RECID=5 STAMP=995084420
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/bak/FULL_07tkvi5r_1_1.bk RECID=6 STAMP=995084476
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/bak/ARCH_08tkvi65_1_1.arch RECID=7 STAMP=995084485
Crosschecked 3 objects
 
 
RMAN> delete expired backup;
 
using channel ORA_DISK_1
specification does not match any backup in the repository
 
RMAN> run {
2> allocate channel c1 type disk;
3> restore database;
4> release channel c1;
5> }
 
released channel: ORA_DISK_1
allocated channel: c1
channel c1: SID=47 device type=DISK
 
Starting restore at 17-DEC-18
 
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/zwcdg/datafile/system.256.994829547
channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/zwcdg/datafile/sysaux.257.994829547
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/zwcdg/datafile/undotbs1.258.994829547
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/zwcdg/datafile/users.259.994829547
channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/zwcdg/datafile/undotbs2.264.994829779
channel c1: reading from backup piece /u01/app/bak/FULL_06tkvi43_1_1.bk
channel c1: piece handle=/u01/app/bak/FULL_06tkvi43_1_1.bk tag=TAG20181217T042019
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:35
Finished restore at 17-DEC-18
 
released channel: c1
 
 
 
Enable Active DataGuard.
SQL> alter database recover managed standby database disconnect from session;
 
Database altered.
 
SQL> shutdown immediate
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
 
Total System Global Area  835104768 bytes
Fixed Size     2257840 bytes
Variable Size   708840528 bytes
Database Buffers   121634816 bytes
Redo Buffers     2371584 bytes
SQL> alter database mount standby database;
 
Database altered.
 
SQL> alter database open read only;  
 
Database altered.
 
SQL> alter database recover managed standby database using current logfile disconnect from session; 
 
Database altered.
 
SQL> select database_role,open_mode,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
 
DATABASE_ROLE OPEN_MODE       PROTECTION_MODE    PROTECTION_LEVEL
—————- ——————– ——————– ——————–
PHYSICAL STANDBY READ ONLY WITH APPLY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
 
 
 
Standby Monitor.
SQL> select name,thread#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') "First",to_char(next_time,'yyyy-mm-dd hh24:mi:ss') "Next",applied from v$archived_log,(select max(sequence#) "SEQ#" from v$archived_log where applied='YES') b where sequence# between b.seq#-5 and b.seq#+9 order by sequence#;
 
NAME THREAD#  SEQUENCE# First        Next    APPLIED
———————————— ———- ———- ——————- ——————- ———
/u01/app/arch/1_30_994829627.dbf       1 30 2018-12-17 06:17:30 2018-12-17 06:17:35 YES
/u01/app/arch/1_31_994829627.dbf       1 31 2018-12-17 06:17:35 2018-12-17 06:17:42 YES
/u01/app/arch/1_32_994829627.dbf       1 32 2018-12-17 06:17:42 2018-12-17 06:17:48 YES
/u01/app/arch/1_33_994829627.dbf       1 33 2018-12-17 06:17:48 2018-12-17 06:26:52 YES
/u01/app/arch/1_34_994829627.dbf       1 34 2018-12-17 06:26:52 2018-12-17 06:26:53 YES
/u01/app/arch/1_35_994829627.dbf       1 35 2018-12-17 06:26:53 2018-12-17 06:26:53 YES
/u01/app/arch/1_36_994829627.dbf       1 36 2018-12-17 06:26:53 2018-12-17 06:26:54 IN-MEMORY
 
7 rows selected.
 
SQL>  select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby;
 
PROCESS PID CLIENT_PID   STATUS       DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS
——— ———- ——————– ———— ———- ———— ————-
ARCH 6917 6917   CLOSING 0      0    0
ARCH 6919 6919   CLOSING 0      0    0
ARCH 6921 6921   CONNECTED 0      0    0
ARCH 6923 6923   CLOSING 0      0    0
ARCH 6925 6925   CLOSING 0      0    0
ARCH 6927 6927   CLOSING 0      0    0
ARCH 6929 6929   CLOSING 0      0    0
ARCH 6931 6931   CLOSING 0      0    0
ARCH 6933 6933   CLOSING 0      0    0
ARCH 6935 6935   CLOSING 0      0    0
RFS     6990 3911   IDLE 0      0    0
RFS     6996 3785   IDLE 0      0    0
RFS     6985 3968   IDLE 0      0    0
RFS     6992 2749   IDLE 0      0    0
RFS     6945 4386   IDLE 0      0    0
RFS     6947 3424   IDLE 0      0    0
RFS     6953 4388   IDLE 0      0    0
MRP0 6957 N/A   APPLYING_LOG 0      0    0
 
18 rows selected.
 
SQL> select * from v$archive_gap;
 
no rows selected
 
 
 
 
Standby alert_log
Physical standby database opened for read only access.
Completed: alter database open read only
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (zwc)
Mon Dec 17 06:11:05 2018
MRP0 started with pid=37, OS id=6957 
MRP0: Background Managed Standby Recovery process started (zwc)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived…
All non-current ORLs have been archived.
Media Recovery Log /u01/app/arch/2_11_994829627.dbf
Media Recovery Log /u01/app/arch/1_16_994829627.dbf
Media Recovery Log /u01/app/arch/1_17_994829627.dbf
Media Recovery Log /u01/app/arch/2_12_994829627.dbf
Media Recovery Log /u01/app/arch/1_18_994829627.dbf
Media Recovery Log /u01/app/arch/2_13_994829627.dbf
Mon Dec 17 06:11:11 2018
Completed: alter database recover managed standby database using current logfile disconnect from session
Media Recovery Log /u01/app/arch/2_14_994829627.dbf
Media Recovery Log /u01/app/arch/1_19_994829627.dbf
Media Recovery Log /u01/app/arch/1_20_994829627.dbf
Media Recovery Log /u01/app/arch/2_15_994829627.dbf
Media Recovery Log /u01/app/arch/1_21_994829627.dbf
Media Recovery Log /u01/app/arch/1_22_994829627.dbf
Media Recovery Log /u01/app/arch/1_23_994829627.dbf
Media Recovery Log /u01/app/arch/2_16_994829627.dbf
Media Recovery Log /u01/app/arch/1_24_994829627.dbf
Media Recovery Log /u01/app/arch/1_25_994829627.dbf
Media Recovery Log /u01/app/arch/1_26_994829627.dbf
Media Recovery Log /u01/app/arch/2_17_994829627.dbf
Media Recovery Log /u01/app/arch/1_27_994829627.dbf
Mon Dec 17 06:11:16 2018
Media Recovery Log /u01/app/arch/2_18_994829627.dbf
Media Recovery Log /u01/app/arch/1_28_994829627.dbf
Media Recovery Log /u01/app/arch/2_19_994829627.dbf
Media Recovery Waiting for thread 1 sequence 29 (in transit)
Recovery of Online Redo Log: Thread 1 Group 6 Seq 29 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/zwcdg/standbylog/standby_group_06.log
Media Recovery Waiting for thread 2 sequence 20 (in transit)
Recovery of Online Redo Log: Thread 2 Group 8 Seq 20 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/zwcdg/standbylog/standby_group_08.log
Mon Dec 17 06:13:25 2018
Mon Dec 17 06:13:25 2018
Successfully added datafile 6 to media recovery
Datafile #6: '/u01/app/oracle/oradata/ZWCDG/datafile/o1_mf_test1_g1fmj4vq_.dbf'
File 201 not verified due to error ORA-01157
Successfully added datafile 7 to media recovery
Datafile #7: '/u01/app/oracle/oradata/ZWCDG/datafile/o1_mf_test2_g1fmj9pf_.dbf'
Mon Dec 17 06:16:58 2018
Recovery deleting file #6:'/u01/app/oracle/oradata/ZWCDG/datafile/o1_mf_test1_g1fmj4vq_.dbf' from controlfile.
Deleted Oracle managed file /u01/app/oracle/oradata/ZWCDG/datafile/o1_mf_test1_g1fmj4vq_.dbf
Recovery dropped tablespace 'TEST1'
Mon Dec 17 06:17:12 2018
Recovery deleting file #7:'/u01/app/oracle/oradata/ZWCDG/datafile/o1_mf_test2_g1fmj9pf_.dbf' from controlfile.
Deleted Oracle managed file /u01/app/oracle/oradata/ZWCDG/datafile/o1_mf_test2_g1fmj9pf_.dbf
Recovery dropped tablespace 'TEST2'
Mon Dec 17 06:17:30 2018
Archived Log entry 25 added for thread 1 sequence 29 ID 0x2cc015f6 dest 1:
Mon Dec 17 06:17:30 2018
Primary database is in MAXIMUM PERFORMANCE mode
RFS[6]: Assigned to RFS process 6985
RFS[6]: Selected log 5 for thread 1 sequence 30 dbid 750809337 branch 994829627
Mon Dec 17 06:17:31 2018
Media Recovery Waiting for thread 1 sequence 30 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 30 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/zwcdg/standbylog/standby_group_05.log
Mon Dec 17 06:17:36 2018
Archived Log entry 26 added for thread 1 sequence 30 ID 0x2cc015f6 dest 1:
RFS[6]: Selected log 5 for thread 1 sequence 31 dbid 750809337 branch 994829627
Media Recovery Waiting for thread 1 sequence 31 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 31 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/zwcdg/standbylog/standby_group_05.log
Mon Dec 17 06:17:42 2018
Archived Log entry 27 added for thread 1 sequence 31 ID 0x2cc015f6 dest 1:
Mon Dec 17 06:17:42 2018
RFS[6]: Selected log 5 for thread 1 sequence 32 dbid 750809337 branch 994829627
Mon Dec 17 06:17:42 2018
Media Recovery Log /u01/app/arch/1_31_994829627.dbf
Media Recovery Waiting for thread 1 sequence 32 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 32 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/zwcdg/standbylog/standby_group_05.log
Mon Dec 17 06:17:45 2018
Archived Log entry 28 added for thread 2 sequence 20 ID 0x2cc015f6 dest 1:
Mon Dec 17 06:17:45 2018
Primary database is in MAXIMUM PERFORMANCE mode
RFS[7]: Assigned to RFS process 6992
RFS[7]: Selected log 8 for thread 2 sequence 21 dbid 750809337 branch 994829627
Media Recovery Waiting for thread 2 sequence 21 (in transit)
Recovery of Online Redo Log: Thread 2 Group 8 Seq 21 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/zwcdg/standbylog/standby_group_08.log
Mon Dec 17 06:17:48 2018
Expanded controlfile section 11 from 28 to 280 records
Requested to grow by 252 records; added 9 blocks of records
Archived Log entry 29 added for thread 1 sequence 32 ID 0x2cc015f6 dest 1:
RFS[6]: Selected log 5 for thread 1 sequence 33 dbid 750809337 branch 994829627
Media Recovery Log /u01/app/arch/1_32_994829627.dbf
Media Recovery Waiting for thread 1 sequence 33 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 33 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/zwcdg/standbylog/standby_group_05.log