Oracle DB - 實作 Resize ORL and SRL on Primary and Physical Standby in Dataguard Environment

Primary Database:

sqlplus sys/admin as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 18 02:33:02 2013

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


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

SQL> select status,instance_name,DB_UNIQUE_NAME,database_role from v$database,v$Instance;

STATUS
------------------------------------
INSTANCE_NAME
------------------------------------------------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
DATABASE_ROLE
------------------------------------------------
OPEN
DGTEST
DGTEST_P
PRIMARY

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;

    GROUP# Size in MB
---------- ----------
 1   50
 2   50
 3   50

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

    GROUP# Size in MB
---------- ----------
 6   50
 4   50
 5   50
 7   50

Standby Database:

$sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 18 03:08:15 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status,instance_name,DB_UNIQUE_NAME,database_role from v$database,v$Instance;

STATUS
------------------------------------
INSTANCE_NAME
------------------------------------------------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
DATABASE_ROLE
------------------------------------------------
OPEN
DGTEST
DGTEST_S
PHYSICAL STANDBY


SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$log group by group#;

    GROUP# Size in MB
---------- ----------
 1   50
 2   50
 3   50

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

    GROUP# Size in MB
---------- ----------
 6   50
 4   50
 5   50
 7   50

SQL> show parameter standby_file_management

NAME     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
standby_file_management      string
auto
SQL> alter system set standby_file_management=manual;

System altered.

SQL> show parameter standby_file_management

NAME     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
standby_file_management      string
MANUAL


On the primary database:
Check the status of the Online Redo Logs and resize them by dropping the INACTIVE redo logs and re-creating them with the new size.
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 INACTIVE
 2 CURRENT
 3 INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 ('/u01/oracle/oradata/DGTEST/redo01.log') size 25M;
alter database add logfile group 1 ('/u01/oracle/oradata/DGTEST/redo01.log') size 25M
*
ERROR at line 1:
ORA-00301: error in adding log file '/u01/oracle/oradata/DGTEST/redo01.log' -
file cannot be created
ORA-27038: created file already exists
Additional information: 1


SQL> !  rm /u01/oracle/oradata/DGTEST/redo01.log

SQL> alter database add logfile group 1 ('/u01/oracle/oradata/DGTEST/redo01.log') size 25M;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 UNUSED
 2 ACTIVE
 3 CURRENT

SQL> alter system checkpoint;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 UNUSED
 2 INACTIVE
 3 CURRENT

SQL> alter database drop logfile group 2;

Database altered.

SQL> !rm /u01/oracle/oradata/DGTEST/redo02.log

SQL> alter database add logfile group 2 ('/u01/oracle/oradata/DGTEST/redo02.log') size 25M;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 UNUSED
 2 UNUSED
 3 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CURRENT
 2 UNUSED
 3 ACTIVE

SQL> alter system checkpoint;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CURRENT
 2 UNUSED
 3 INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL>  !rm /u01/oracle/oradata/DGTEST/redo03.log

SQL> alter database add logfile group 3 ('/u01/oracle/oradata/DGTEST/redo03.log') size 25M;

Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
 1   25
 2   25
 3   25



Moving on to the Standby Redo Logs on the Primary Database:
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
 6   50
 4   50
 5   50
 7   50

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ------------------------------
 4 UNASSIGNED
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

SQL> alter database add standby logfile group 4 ('/u01/oracle/oradata/DGTEST/srl0.log') size 25M;
alter database add standby logfile group 5 ('/u01/oracle/oradata/DGTEST/srl1.log') size 25M;
Database altered.

SQL>

Database altered.

SQL> alter database add standby logfile group 6 ('/u01/oracle/oradata/DGTEST/srl2.log') size 25M;

Database altered.

SQL> alter database add standby logfile group 7 ('/u01/oracle/oradata/DGTEST/srl3.log') size 25M;

Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6         25
         4         25
         5         25
         7         25

Moving on to the standby database:

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CLEARING_CURRENT
 2 CLEARING
 3 CLEARING

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CLEARING_CURRENT
 2 CLEARING
 3 CLEARING

SQL> alter database clear logfile group 2;

Database altered.

SQL> ^[[A    select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CLEARING_CURRENT
 2 UNUSED
 3 CLEARING

SQL>  alter database drop logfile group 2;

Database altered.

SQL> !rm /u01/oracle/oradata/DGTEST/redo02.log

SQL> alter database add logfile group 2 ('/u01/oracle/oradata/DGTEST/redo02.log') size 25M;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CLEARING_CURRENT
 2 UNUSED
 3 CLEARING

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL>  !rm /u01/oracle/oradata/DGTEST/redo03.log

SQL> alter database add logfile group 3 ('/u01/oracle/oradata/DGTEST/redo03.log') size 25M;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CLEARING
 2 CLEARING_CURRENT
 3 UNUSED

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> !rm /u01/oracle/oradata/DGTEST/redo01.log

SQL> alter database add logfile group 1 ('/u01/oracle/oradata/DGTEST/redo01.log') size 25M;

Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
 1   25
 2   25
 3   25

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
 6   50
 4   50
 5   50
 7   50

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ------------------------------
 4 ACTIVE
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

SQL> alter database drop standby logfile group 5;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/oracle/oradata/DGTEST/srl1.log') size 25M;

Database altered.

SQL> alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

SQL>  alter database add standby logfile group 6 ('/u01/oracle/oradata/DGTEST/srl2.log') size 25M;

Database altered.

SQL> alter database add standby logfile group 7 ('/u01/oracle/oradata/DGTEST/srl3.log') size 25M;

Database altered.

SQL> alter system checkpoint;
alter system checkpoint
             *
ERROR at line 1:
ORA-16000: database open for read-only access


SQL> ALTER DATABASE CLEAR LOGFILE GROUP 4;

Database altered.

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ------------------------------
 4 UNASSIGNED
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

SQL>  select group#,status from v$standby_log;

    GROUP# STATUS
---------- ------------------------------
 4 UNASSIGNED
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

SQL> alter database drop standby logfile group 4;

Database altered.

SQL> alter database add standby logfile group 4 ('/u01/oracle/oradata/DGTEST/srl0.log') size 25M;


Database altered.

SQL>  select group#,status from v$standby_log;

    GROUP# STATUS
---------- ------------------------------
 4 UNASSIGNED
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter database recover managed standby database disconnect from session using current logfile;
or ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;

Database altered.

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ------------------------------
         4 ACTIVE
         5 UNASSIGNED
         6 UNASSIGNED
         7 UNASSIGNED

SQL> select process,status,sequence# from v$managed_standby;

PROCESS     STATUS  SEQUENCE#
--------------------------- ------------------------------------ ----------
ARCH    CLOSING      17149
ARCH    CLOSING      17150
ARCH    OPENING      14989
ARCH    CLOSING      17148
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE      17151
RFS    IDLE  0
MRP0    APPLYING_LOG      17151

9 rows selected.

Primary:

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
 17150

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
 17152


Standby:
SQL>  select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
 17149

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
 17152


On the 2nd standby database:
$sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 18 03:08:18 2013

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


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

SQL> select status,instance_name,DB_UNIQUE_NAME,database_role from v$database,v$Instance;

STATUS
------------------------------------
INSTANCE_NAME
------------------------------------------------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
DATABASE_ROLE
------------------------------------------------
OPEN
DGTEST
DGTEST_S_US
PHYSICAL STANDBY


SQL>
SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$log group by group#;

    GROUP# Size in MB
---------- ----------
 1   50
 2   50
 3   50

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

    GROUP# Size in MB
---------- ----------
 6   50
 4   50
 5   50
 7   50

SQL> show parameter standby_file_management

NAME     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
standby_file_management      string
AUTO
SQL> alter system set standby_file_management=manual;

System altered.

SQL> show parameter standby_file_management

NAME     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
standby_file_management      string
MANUAL
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CLEARING_CURRENT
 2 CLEARING
 3 CLEARING

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CLEARING_CURRENT
 2 CLEARING
 3 CLEARING

SQL> alter database clear logfile group 2;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CLEARING_CURRENT
 2 UNUSED
 3 CLEARING

SQL>  alter database drop logfile group 2;

Database altered.

SQL> !rm /u01/oracle/oradata/DGTEST/redo02.log

SQL> alter database add logfile group 2 ('/u01/oracle/oradata/DGTEST/redo02.log') size 25M;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CLEARING_CURRENT
 2 UNUSED
 3 CLEARING

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL>  !rm /u01/oracle/oradata/DGTEST/redo03.log

SQL>
SQL> alter database add logfile group 3 ('/u01/oracle/oradata/DGTEST/redo03.log') size 25M;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
 1 CLEARING
 2 CLEARING_CURRENT
 3 UNUSED

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> !rm /u01/oracle/oradata/DGTEST/redo01.log

SQL> alter database add logfile group 1 ('/u01/oracle/oradata/DGTEST/redo01.log') size 25M;

Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
 1   25
 2   25
 3   25

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
 6   50
 4   50
 5   50
 7   50

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ------------------------------
 4 UNASSIGNED
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

SQL>  alter database drop standby logfile group 4;

Database altered.

SQL> alter database drop standby logfile group 5;

Database altered.

SQL>  alter database drop standby logfile group 6;

Database altered.

SQL> alter database drop standby logfile group 7;

Database altered.

SQL>  alter database add standby logfile group 4 ('/u01/oracle/oradata/DGTEST/srl0.log') size 25M;

Database altered.

SQL> alter database add standby logfile group 5 ('/u01/oracle/oradata/DGTEST/srl1.log') size 25M;

Database altered.

SQL> alter database add standby logfile group 6 ('/u01/oracle/oradata/DGTEST/srl2.log') size 25M;

Database altered.

SQL> alter database add standby logfile group 7 ('/u01/oracle/oradata/DGTEST/srl3.log') size 25M;

Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
 6   25
 4   25
 5   25
 7   25

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ------------------------------
 4 UNASSIGNED
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
 1   25
 2   25
 3   25

SQL>
SQL> alter system set standby_file_management=auto;

System altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS     STATUS  SEQUENCE#
--------------------------- ------------------------------------ ----------
ARCH    CONNECTED  0
ARCH    CONNECTED  0
ARCH    CONNECTED  0
ARCH    CONNECTED  0
RFS    IDLE  0
MRP0    WAIT_FOR_LOG      17151
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0

PROCESS     STATUS  SEQUENCE#
--------------------------- ------------------------------------ ----------
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0

PROCESS     STATUS  SEQUENCE#
--------------------------- ------------------------------------ ----------
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0

PROCESS     STATUS  SEQUENCE#
--------------------------- ------------------------------------ ----------
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0
RFS    IDLE  0

39 rows selected.

SQL>  select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
 17150

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
 17152

Cloud Service - CaaS Exploration

建置Hybrid Cloud的一些考量點:

那些系統適合放在Internet CaaS?

那些系統適合放在MPLS/VPN CaaS?

那些系統適合On-Premises?

--這些系統彼此有連結性，適合放在一起，但是因為目前cloud service的成熟度還不夠，無法提共足夠穩定且靈活服務，暫時還不建議移動到Cloud

--成熟度指的是網路頻寬，網路response time，system maintenance job 的支持度

--目前適合hybrid cloud的運作方式

--IT消費化和租用服務可以將這些費用列為資本支出，而不是購入資產

那些系統需求不定?

適合使用 CaaS- via internet

. 主要是企業外部使用者

. 網路流量小，流量費低 or 對外部網路的流量需求高，較不要求responsed time

. 尚未穩定或生命週期短的系統

. 主要被透過internet access

. 要被每個site access

. 和其他系統的連結性低 

. 硬體效能要求低

. 

適合放在 CaaS - via MPLS/VPN

. 主要是企業內部使用者

. 對其他內部系統的資料交換網路流量高，要求responsed time

. 尚未穩定或生命週期短的系統

. 主要被透過AVPN access

. 要被每個site access, 網路流量高且平均

. 和其他系統的連結性高 

. 硬體效能要求低

暫時適合On-Premises

. 主要是企業內部使用者

. 對其他內部系統的資料交換網路流量高，要求responsed time，適合使用LAN and WAN的高速頻寬

. 穩定且生命週期長的系統，適合安排在private cloud

. 主要被透過LAN access

. 要被每個site access or 需要被單一site access，網路流量高且集中

. 和其他系統的連結性高 

. 硬體效能要求高

對中華電信CaaS服務的問題:

--HiLink VPN 或 CHT IPVPN的相互比較，優缺點? 怎麼評估選用其中一種服務?

--hicloud CaaS VPN加值服務可使用什麼樣的線路? 怎麼申請? 客戶多要買設備嗎? 需要多付出什麼費用?

--hicloud 流量out，是否有分INternet and VPN?

--如果要把VM轉回公司內部private cloud，流量out怎麼收費?

--VPC有提供以下服務，CAAS有提供嗎?

提供虛擬轉虛擬(V2V)介面，可將企業現有虛擬主機映像檔匯入虛擬私雲。 

提供控制台(Console)服務，可提供企業開啟VM 控制台畫面。 

提供虛擬光碟服務，可讓虛擬私雲直接存取企業用戶端光碟機。 

--可否自行安裝特定版本作業系統?

--windows server的license費用，是包含在租金當中?

--VM之間互相傳遞資料，怎麼計算費用?

--是否提供Ticket system for customer?

--是否提供7*24支援專線電話服務? 外國語言服務?

--VPC and CaaS 的機房位置(縣市? 國內外?)

Oracle DB - Data Guard, Create Physical Standby Database

1. DB Migration Procedure v4.doc

2. DailyWorkLog.txt

3. 05_DBA_Project.xlsx

4. C:\Oracle Database 11g Release 2 Documentation\index.htm

5. C:\Oracle Database 10g Release 2 Documentation\index.htm

6. Internet Document
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/ha/dataguard/physstby/physstdby.htm?cid=4024&ssid=106308955970538

http://shivanandarao-oracle.com/2012/03/10/creating-physical-standby-database-on-oracle-11g/

http://learnwithme11g.wordpress.com/2011/12/07/creating-a-physical-standby-database-11gr2-3/


5. Download files from rue1rdb



‧Preparing the Primary Database for Standby Database Creation
--Enable Forced Logging
SQL> ALTER DATABASE FORCE LOGGING;
SQL> SELECT force_logging FROM v$database;

--Create a Password File
Depends...

--Configure a Standby Redo Log
A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases.
The size of the current standby redo log files must exactly match the size of the current primary database online redo log files. For example, if the primary database uses two online redo log groups whose log files are 200K, then the standby redo log groups should also have log file sizes of 200K.
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 01 '/oradata/ERP/srl01.log' SIZE 200M;
or
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

--Set Primary Database Initialization Parameters
Example Primary Database: Primary Role Initialization Parameters
DB_NAME=taipei
DB_UNIQUE_NAME=taipei
LOG_ARCHIVE_CONFIG='DG_CONFIG=(taipei,shuzou)'
CONTROL_FILES='/arch1/taipei/control1.ctl', '/arch2/taipei/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/taipei/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=taipei'
LOG_ARCHIVE_DEST_2=
 'SERVICE=shuzou LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=shuzou'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

Example Primary Database: Standby Role Initialization Parameters
FAL_SERVER=shuzou
FAL_CLIENT=taipei
DB_FILE_NAME_CONVERT='shuzou','taipei'
LOG_FILE_NAME_CONVERT=
 '/arch1/shuzou/','/arch1/taipei/','/arch2/shuzou/','/arch2/taipei/'
STANDBY_FILE_MANAGEMENT=AUTO


--Enable Archiving
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

‧Step-by-Step Instructions for Creating a Physical Standby Database
--Create a Backup Copy of the Primary Database Datafiles
Backup database using RMAN.
Backup database using cold backup.

--Create a Control File for the Standby Database
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'constdby01.ctl';

--Prepare an Initialization Parameter File for the Standby Database
SQL> CREATE PFILE='/tmp/initERP.ora' FROM SPFILE;
SQL> SHUTDOWN IMMIEDATE

修改如下
ERP.__db_cache_size=5838471168
ERP.__java_pool_size=16777216
ERP.__large_pool_size=16777216
ERP.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment
ERP.__pga_aggregate_target=1828716544
ERP.__sga_target=6442450944
ERP.__shared_io_pool_size=0
ERP.__shared_pool_size=520093696
ERP.__streams_pool_size=0
*.archive_lag_target=0
*.audit_file_dest='/u01/oracle/admin/ERP/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/ERP/constdby01.ctl','/oradata/ERP/constdby02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ERP'
*.db_recovery_file_dest='/oradata/arch'
*.db_recovery_file_dest_size=53687091200
*.db_unique_name='ERP_S_US'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ERPXDB)'
*.fal_client='ERP_S_US'
*.fal_server='erp_p'
*.job_queue_processes=1000
*.log_archive_config='dg_config=(ERP_P,ERP_S,ERP_S_US)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2=''
*.log_archive_dest_3=''
*.log_archive_dest_4=''
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_dest_state_4='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
ERP.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
ERP.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=1824522240
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=665
*.sga_max_size=8589934592
*.sga_target=6442450944
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

--Copy Files from the Primary System to the Standby System

1. Datafiles and logifles
select * from v$datafile;
select * from v$logfile;

2. Standby control file
Copy the $ORACLE_HOME/dbs/constdby01.ctl to Standby site /oradata/ERP then duplicate a constdby02.ctl from constdby01.ctl

3. Initialization parameter file

4. Copy the remote login password file from the primary database system to the standby database system
$ORACLE_HOME/dbs/orapwERP file

--Set Up the Environment to Support the Standby Database
1. Set Standby site tnsnames.ora
2. Add ERP_S_US tnsnames.ora information to ERP_P and ERP_S site

% lsnrctl stop
% lsnrctl start

SQL> CREATE SPFILE FROM PFILE='/u01/oracle/product/11.2.0/dbs/initERP.ora';


--Start the Physical Standby Database
SQL>startup nomount;
SQL>alter database mount standby database;
接下來參考Excel file

or <參考原廠手冊>
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
(SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;)


To start Redo Apply in the foreground, issue the following SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
This statement starts a detached server process and immediately returns control to the user.

To start real-time apply, include the USING CURRENT LOGFILE clause on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

--Verify the Physical Standby Database Is Performing Properly
Test archival operations to the physical standby database.
In this example, the transmission of redo data to the remote standby location does not occur until after a log switch. A log switch occurs, by default, when an online redo log file becomes full. To force a log switch so that redo data is transmitted immediately, use the following ALTER SYSTEM statement on the primary database. For example:
SQL> ALTER SYSTEM SWITCH LOGFILE;

On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Primary:
SQL> select max(sequence#) from v$archived_log;

Standby:
SQL> select max(sequence#) from v$archived_log where applied='YES';

To check if the archive logs are successfully applied on standby, use the below query :
SQL> select max(al.sequence#) "Last Seq Received", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;



‧Post-Creation Steps

--Configure DGMGRL
$ dgmgrl
DGMGRL> connect sys
DGMGRL> add database 'ERP_S_US' as connect identifier is ERP_S_US;
DGMGRL> show configuration;
DGMGRL> show database verbose 'ERP_S_US';
DGMGRL> ENABLE DATABASE 'ERP_S_US';

--Config Online and Standby redo log files
Handling ORL and SRL (Resize) on Primary and Physical Standby in Dataguard Environment (Doc ID 1532566.1)

JDE - E1 Web Client ActiveX Controls for Media Object and Grid Export / Import FAIL WITH INTERNET EXPLORER X64

Why 64-bit IE doesn’t work on JDE? Any solution for this?

è 可以使用 64-bit IE，但是因為JDE ActiveX contrls 無法支援64-bit IE，所以用到以下兩個功能時有限制。

n   Media Object

n   Grid Export/Import

 è If you use ActiveX controls, use the 32-bit edition of Internet Explorer instead of the 64-bit edition.

IE10 is another issue we have discussed but no conclusion yet.

è IE10 Support begins with 8.98.4.12

32-bit IE9

64-bit IE9

可以查看以下文件:

A. E1: MOBJ: Working with E1 Web Client ActiveX Controls for Media Object and Grid Export / Import (Doc ID 1056203.1) It does not matter if you are using a 64-bit Windows OS or not. A 32-bit Internet Explorer browser must be used. ActiveX controls are not yet supported with the 64-bit browser. See Bug 12973392 ACTIVEX CONTROL FAIL WITH INTERNET EXPLORER X64 Manual install / File Location on Server As an example, files jdeexpimpU.cab or jdewebctlsU.cab, are stored on the JAS server in the following location: http://<JAS_SERVER_NAME/jde/axctls/ example path on weblogic server C:\jde_home\SCFHA\targets\<instance>\owl_deployment\webclient.ear\app\webclient.war\axctls There are two files in the E1 web install directory:     For 8.95 and below, the files are jdewww/axctls/jdeexpimp.cab (for active x control for import/export) and jdewww/axctls/jdewebctls.cab (for active x control for media objects).     For 8.96 and above, the file names are jdeexpimpU.cab and jdewebctlsU.cab. You can find them on the JAS server too, under /webclient/web/axctls. The ActiveX control install files are OCX files within those cab archives. 在PY_e1oas01上面 To enable the HTML rich text editor set the value of SupportActiveXIE to FALSE in the jas.ini under the [OWWEB] section or Unselect the "Use ActiveX Controls" check box under the server manager web runtime properties. Restart PY_e1oas02 to let the change effective Using 32-bit IE9 connect to PY_e1oas01，可以使用 HTML rich text editor 來新增 Text File Using 64-bit IE9 connect to PY_e1oas01，可以使用 HTML rich text editor 來新增 Text File - Text3 相同的設定在 PY_e1oas03上面做一次，在開啟檔案時出現" Internet Explorer 無法顯示網頁"的錯誤訊息，可能需要重新deploy instance來看看，可否解決這個問題。 There are differences in the activeX (RTF) and ADF HTML editor.  Once you switch a file from ActiveX to HTML, there will be a change in the text file format.  This is due to font limitations.  Once a file is converted to HTML, or created via HTML, it will be read only with ActiveX. 因為 Export to excel randomly fails with SupportActiveXIE set to FALSE When exporting data to Excel using the ALL option, most of the times it does not work, the user receives a blank excel worksheet .This only occurs with Internet Explorer and not on Firefox and is reported with Bug 13391456 GRID EXPORT TO EXCEL FAILS IN NON-ACTIVEX MODE - INTERMITTENT ISSUE and is fixed in Tools Release 8.98.4.7. 所以最好upgrade Tools Release 到8.98.4.12之後，再來試著用 SupportActiveXIE set to FALSE B. E1: JAS: Web Browser Master Checklist for Internet Explorer (Doc ID 1267490.1) Internet Explorer Notes on ActiveX Controls:  Internet Explorer 64-bit browser is not supported.  The ActiveX controls are currently only built for use with Internet Explorer 32-bit version provided with a Windows 32-bit or x64 installation.   C. Bug 12973392 : ACTIVEX CONTROL FAIL WITH INTERNET EXPLORER X64 POTENTIAL WORKAROUNDS   =====================   Use 32-bit Internet Explorer browser

D. JD Edwards EnterpriseOne 9.1.X Web Client Support Statement (Doc ID 1487909.1)

For certain functionality, such as Media Objects or Exporting from the Grid to Microsoft Excel, JD Edwards EnterpriseOne uses 32-bit ActiveX controls.  If you use ActiveX controls, use the 32-bit edition of Internet Explorer instead of the 64-bit edition. For more information about 32-bit and 64-bit Internet Explorer, refer to Microsoft Knowledge Base Article #896457.

Grid Export / Import

Oracle DB - OEM Auto SQL Tuning Task 的建議分類

Advisor Central SQL Tuning Summary SYS.SYS_AUTO_SQL_TUNING_TASK 解析


Advisor Central  >   SQL Tuning Summary:SYS.SYS_AUTO_SQL_TUNING_TASK
的建議可以分為幾類:
1. Statistics
2. SQL Profile
3. Index
4. Restructure SQL
5. Alternative Plan
6. Miscellaneous

Reference:  How to find task exeution "EXEC_XXXX" from the Automatic SQL Tuning Results
https://forums.oracle.com/message/11025652

Does anybody knows how to find a specific run of an Automatic SQL Tuning Advisor in EM 12c?

Very often on the page SQL Tuning Details:SYS.SYS_AUTO_SQL_TUNING_TASK I see the following result:

"This statement was skipped because it has already been tuned recently. See task execution "EXEC_5616" for the most recent tuning results." Fine, but how to find this specific task execution? I'm not able to find any task execution. Does it possible to find it at all?




1. sqlplus
select distinct t.TASK_ID
, t.TASK_NAME
, t.EXECUTION_START
, r.EXECUTION_NAME exec_name
from DBA_ADVISOR_TASKS t
, DBA_ADVISOR_RECOMMENDATIONS r
where t.TASK_ID=r.TASK_ID
and r.EXECUTION_NAME = 'EXEC_6802'
/

2. OEM -> 'Advisor Central' -> SEARCH FOR TASK_NAME from the output of SQL statement above
4. On the 'Results' section, click the task name and you should be able to see the SQL tuning recommendations.

JDE - 升級 upgrade Tools Release to 9.1 with application still 9.0

如果要升級到tools release 9.1 with application 9.0，我目前理解的步驟是:

Steps:

1.      目前主要的HTML Server 都要從 Oracle Application Server改成 WebLogic Server (重新安裝)

2.      升級Enterprise server and DB server 的 OS，從 OEL5U2 à OEL5U5

3.      升級Database 兩版本，從11.2.0.1 à 11.2.0.3

4.      在升級Tools Release 之前，要完成component更新 安裝 5個 ESU (TL913001，JL21519, JL21835, JL21683, JL21509, JL18390)

5.      依序升級新版Tools Release for

l   Server Manager

l   Enterprise Server

l   WLS

l   Deployment Server

6.      Check and the know issues with tools 8.1.3 can be fixed or ignored  “Tools 9.1 Update 3 Known Issues and Workarounds (Doc ID 1543253.1).xlsx”

7.      Full package build and deploy

8.      Training User to be familiar with new version UI

9.      Testing

如果只是單純要支援IE10，簡單升級 tools release 從 8.98.4.6 à 8.98.4.12即可支援。

Steps:

1.      依序升級新版Tools Release for

l   Server Manager

l   Enterprise Server

l   WLS

l   Deployment Server

2.      Full package build and deploy

3.      Testing

Comparison between tools release 8.98.x and 9.1.x: 

Remark 1: Two one off patches are needed to be applied manually
1. Disable Row Exit Menu on right click
2. xxxxxx

Remark 2: Due to the way javascript (.js) files are managed by the browser, it is good practice to clear the browser cache after a tools release upgrade or any time there is a javascript error message and then retry.

Agile - Installing and Upgrading AutoVue for Agile 20.1 -> 20.2.2

Oracle Documentation:
http://www.oracle.com/technetwork/indexes/documentation/index.html


Agile Documentation:
http://www.oracle.com/technetwork/documentation/agile-085940.html


AutoVue Documentation:
http://www.oracle.com/technetwork/documentation/autovue-091442.html
http://docs.oracle.com/cd/E49948_02/otn/pdf/install/html_agabp/output/chapter_1.htm  (純參考用，內容和上方所列的PDF Installation and Configuration Guide 不同)


http://corefonts.sourceforge.net/


Install Microsoft’s TrueType core fonts from http://corefonts.sourceforge.net/. These fonts are required for
viewing files that use Microsoft-specific fonts.

If installing on a Linux OS, make sure you have the correct version of WINE installed. The version of WINE
compatible with AutoVue 20.2.2 is wine-av-20040914-21.i386.rpm. You can download this version of WINE from http://oss.oracle.com/AutoVue.

解決方式是要upgrade Autovue 的版本到20.2.0.

      在Oracle AutoVue 20.2 的Planning Guide裡面的AutoVue System Requirements裡提到Linux的版本是Oracle Enterprise Linux 5.6 (x86).

      而我們的版本是 Oracle Enterprise Linux 5 (x86)

      所以之後我們就沒有upgrade了

    

2013/12/05 安裝 File/Vue Server在 plmt03

http://plmt03:8080/Filemgr/Configuration





oracle_agile9_plmt03$sftp plmh04
Connecting to plmh04...

sftp> cd /u01/app/agile/agile931/AVS/jvuew_c/windows/fonts
sftp> ls
Allegro1.uff         AutoCAD_big1.uff     AutoCAD_big2.uff     AutoCAD_shape1.uff   AutoCAD_shape2.uff   AutoCAD_shape3.uff
AutoCAD_small1.uff   AutoCAD_small2.uff   AutoCAD_small3.uff   AutoCAD_small4.uff   AutoCAD_small5.uff   Cadstar1.uff
GDT.ttf              Mentor1.uff          Mentor10.uff         Mentor11.uff         Mentor12.uff         Mentor2.uff
Mentor3.uff          Mentor4.uff          Mentor5.uff          Mentor6.uff          Mentor7.uff          Mentor8.uff
Mentor9.uff          OrcadLayout1.uff     Pads1.uff            Pcad1.uff            Pcad2.uff            Pcad3.uff
Protel1.uff          Protel2.uff          Protel3.uff          Ug0.uff              VeraMono.ttf         app932.fon
app936.fon           app949.fon           app950.fon           arial.ttf            arialbd.ttf          arialbi.ttf
ariali.ttf           ariblk.ttf           batang.ttc           cat4-codepage1.ucf   cat4-codepage10.ucf  cat4-codepage11.ucf
cat4-codepage12.ucf  cat4-codepage13.ucf  cat4-codepage14.ucf  cat4-codepage15.ucf  cat4-codepage16.ucf  cat4-codepage17.ucf
cat4-codepage18.ucf  cat4-codepage19.ucf  cat4-codepage2.ucf   cat4-codepage20.ucf  cat4-codepage21.ucf  cat4-codepage22.ucf
cat4-codepage23.ucf  cat4-codepage24.ucf  cat4-codepage25.ucf  cat4-codepage26.ucf  cat4-codepage27.ucf  cat4-codepage28.ucf
cat4-codepage29.ucf  cat4-codepage3.ucf   cat4-codepage30.ucf  cat4-codepage31.ucf  cat4-codepage32.ucf  cat4-codepage33.ucf
cat4-codepage34.ucf  cat4-codepage35.ucf  cat4-codepage36.ucf  cat4-codepage37.ucf  cat4-codepage38.ucf  cat4-codepage39.ucf
cat4-codepage4.ucf   cat4-codepage40.ucf  cat4-codepage41.ucf  cat4-codepage42.ucf  cat4-codepage43.ucf  cat4-codepage44.ucf
cat4-codepage45.ucf  cat4-codepage46.ucf  cat4-codepage47.ucf  cat4-codepage48.ucf  cat4-codepage49.ucf  cat4-codepage5.ucf
cat4-codepage50.ucf  cat4-codepage51.ucf  cat4-codepage52.ucf  cat4-codepage53.ucf  cat4-codepage54.ucf  cat4-codepage6.ucf
cat4-codepage7.ucf   cat4-codepage8.ucf   cat4-codepage9.ucf   cat4-font1.uff       cat4-font10.uff      cat4-font11.uff
cat4-font12.uff      cat4-font13.uff      cat4-font14.uff      cat4-font15.uff      cat4-font16.uff      cat4-font17.uff
cat4-font18.uff      cat4-font19.uff      cat4-font2.uff       cat4-font20.uff      cat4-font21.uff      cat4-font3.uff
cat4-font4.uff       cat4-font5.uff       cat4-font6.uff       cat4-font7.uff       cat4-font8.uff       cat4-font9.uff
cat4-fontmap.xml     catia5ANSI.standard  catia5ASME.standard  catia5ISO.standard   catia5JIS.standard   comic.ttf
comicbd.ttf          cour.ttf             courbd.ttf           courbi.ttf           couri.ttf            dgn_font_0.uff
dgn_font_0_ja.uff    dgn_font_1.uff       dgn_font_100.uff     dgn_font_101.uff     dgn_font_102.uff     dgn_font_105.uff
dgn_font_106.uff     dgn_font_107.uff     dgn_font_108.uff     dgn_font_127.uff     dgn_font_15.uff      dgn_font_16.uff
dgn_font_17.uff      dgn_font_2.uff       dgn_font_23.uff      dgn_font_26.uff      dgn_font_3.uff       dgn_font_30.uff
dgn_font_31.uff      dgn_font_32.uff      dgn_font_33.uff      dgn_font_4.uff       dgn_font_41.uff      dgn_font_42.uff
dgn_font_43.uff      dgn_font_50.uff      dgn_font_60.uff      dgn_font_7.uff       docfont.map          estre.ttf
framd.ttf            framdit.ttf          gautami.ttf          georgia.ttf          georgiab.ttf         georgiai.ttf
georgiaz.ttf         gulim.ttc            impact.ttf           kaiu.ttf             l_10646.ttf          latha.ttf
lucon.ttf            mangal.ttf           me10_01.uff          me10_02.uff          me10_03.uff          me10_04.uff
me10_05.uff          me10_06.uff          me10_07.uff          me10_08.uff          me10_09.uff          me10_10.uff
me10_11.uff          me10_12.uff          me10_13.uff          me10_14.uff          me10_15.uff          me10_16.uff
me10_17.uff          me10_18.uff          me10_19.uff          me10_20.uff          me10_21.uff          me10_22.uff
me10_23.uff          me10_24.uff          me10_25.uff          me10_26.uff          me10_27.uff          me10_28.uff
me10_29.uff          me10_30.uff          me10_31.uff          me10_32.uff          me10_33.uff          me10_34.uff
micross.ttf          mingliu.ttc          modern.fon           msgothic.ttc         msmincho.ttc         mvboli.ttf
pala.ttf             palab.ttf            palabi.ttf           palai.ttf            proe1.uff            proe10.uff
proe11.uff           proe12.uff           proe13.uff           proe14.uff           proe15.uff           proe16.uff
proe17.uff           proe18.uff           proe19.uff           proe2.uff            proe20.uff           proe21.uff
proe22.uff           proe23.uff           proe24.uff           proe25.uff           proe26.uff           proe27.uff
proe28.uff           proe29.uff           proe3.uff            proe30.uff           proe31.uff           proe32.uff
proe33.uff           proe34.uff           proe35.uff           proe36.uff           proe4.uff            proe5.uff
proe6.uff            proe7.uff            proe8.uff            proe9.uff            proefont.map         raavi.ttf
roman.fon            script.fon           shruti.ttf           simhei.ttf           simsun.ttc           sylfaen.ttf
symbol.ttf           tahoma.ttf           tahomabd.ttf         times.ttf            timesbd.ttf          timesbi.ttf
timesi.ttf           trebuc.ttf           trebucbd.ttf         trebucbi.ttf         trebucit.ttf         tunga.ttf
verdana.ttf          verdanab.ttf         verdanai.ttf         verdanaz.ttf         webdings.ttf         wingding.ttf
wst_czec.fon         wst_engl.fon         wst_fren.fon         wst_germ.fon         wst_ital.fon         wst_span.fon
wst_swed.fon
sftp> get *

修改plmt03的 /u01/app/agile/agile931/AVS/bin/jvueserver.properties

jvueserver.rmi.port=2099