> dbaascli gg status DBAAS CLI version 1.0.0 Executing command gg status Golden Gate status: disabled > quit
$ dbaascli gg setup DBAAS CLI version 1.0.0 Executing command gg setup Enter Golden Gate admin username: ggadmin Enter Golden Gate admin password: Re-enter Golden Gate admin password: Setting up Golden Gate Updating the registry Successfully setup GG
ORCL11g.sql -- ----------------------------------------------- -- Enable Supplemental Logging and Archive Logging -- ----------------------------------------------- SHUTDOWN ABORT; STARTUP PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/s_ORCL11 g.ora MOUNT EXCLUSIVE; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE ARCHIVELOG; ALTER DATABASE FORCE LOGGING; ALTER DATABASE OPEN; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE scope=both; -- ------------------------------------------------------------ -- Set STREAMS_POOL_SIZE to 10% of SGA_MAX_SIZE -- If SGA_MAX_SIZE is zero, set it to a default of 16MB -- GoldenGate installation will contain a readme file -- for the customer to know how to modify this from GoldenGate. -- ------------------------------------------------------------ DECLARE sga_max_size_strvalue VARCHAR2(4000); sga_max_size_value NUMBER; str_pool_size NUMBER; str_pool_size_command VARCHAR2(200); BEGIN -- Check the current value of SGA_MAX_SIZE SELECT VALUE INTO sga_max_size_strvalue FROM v$parameter WHERE N AME = 'sga_max_size'; sga_max_size_value := TO_NUMBER(sga_max_size_strvalue); -- Make STREAMS_POOL_SIZE 10% of SGA_MAX_SIZE str_pool_size := sga_max_size_value * 0.1; -- If str_pool_size is zero, set it to a default of 16M IF str_pool_size = 0 THEN str_pool_size := 16 * 1024 * 1024; END IF; -- Execute ALTER SYSTEM EXECUTE IMMEDIATE 'ALTER SYSTEM SET STREAMS_POOL_SIZE=' || str_p ool_size || ' scope=both'; END; / -- ----------------------------- -- Create Tablespace for GGADMIN -- ----------------------------- CREATE TABLESPACE gg_tbls_targetdb DATAFILE 'gg_tbls_targetdb.dbf' SIZE 100M REUSE AUTOEXTEND ON extent management local uniform siz e 256k; SET VERIFY OFF -- --------------------------- -- Create Default GGADMIN user -- --------------------------- create user &1 identified by "&2" default tablespace gg_tbls_targe tdb quota unlimited on gg_tbls_targetdb; grant dba, resource, unlimited tablespace, create session, alter s ession, connect to &1; exec dbms_goldengate_auth.grant_admin_privilege(grantee => '&1');
- Using the script, Oracle database will be shutdown abort for the GoldenGate setup.
- Database level supplemental logging is enabled. You can optimize this if you see performance issue and log only specific tables/schemas.
- If the SGA_MAX_SIZE is zero, the STREAM_POOL_SIZE is set to 16MB. Otherwise, STREAM_POOL_SIZE is set to be 10% of the SGA_MAX_SIZE.
- GGADMIN user is created for Oracle GoldenGate and granted DBA user privileges. You can restricted its privilege if needed.
ORCL12c.sql: -- ----------------------------------------------- -- Enable Supplemental Logging and Archive Logging -- ----------------------------------------------- SHUTDOWN NORMAL; STARTUP PFILE=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/s_ORCL12c.ora MOUNT EX CLUSIVE; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE ARCHIVELOG; ALTER DATABASE FORCE LOGGING; ALTER DATABASE OPEN; ALTER SYSTEM SWITCH LOGFILE; ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE scope=both; -- ------------------------------------------------------------ -- Set STREAMS_POOL_SIZE to 10% of SGA_MAX_SIZE -- If SGA_MAX_SIZE is zero, set it to a default of 16MB -- GoldenGate installation will contain a readme file -- for the customer to know how to modify this from GoldenGate. -- ------------------------------------------------------------ DECLARE sga_max_size_strvalue VARCHAR2(4000); sga_max_size_value NUMBER; str_pool_size NUMBER; str_pool_size_command VARCHAR2(200); BEGIN -- Check the current value of SGA_MAX_SIZE SELECT VALUE INTO sga_max_size_strvalue FROM v$parameter WHERE NAME = 'sga_max _size'; sga_max_size_value := TO_NUMBER(sga_max_size_strvalue); -- Make STREAMS_POOL_SIZE 10% of SGA_MAX_SIZE str_pool_size := ceil(sga_max_size_value * 0.1); -- If str_pool_size is zero, set it to a default of 16M IF str_pool_size = 0 THEN str_pool_size := 16 * 1024 * 1024; END IF; -- Execute ALTER SYSTEM EXECUTE IMMEDIATE 'ALTER SYSTEM SET STREAMS_POOL_SIZE=' || str_pool_size || ' scope=both'; END; / SHOW CON_NAME; SET VERIFY OFF -- --------------------------- -- Create Default GGADMIN user -- --------------------------- create user &1 identified by "&2" container = ALL; grant connect, resource, unlimited tablespace, alter session, select_catalog_rol e to &1; grant dba to &1; grant create session to &1 container = ALL; grant set container to &1 container = ALL; grant select any dictionary to &1 container = ALL; exec dbms_goldengate_auth.grant_admin_privilege(grantee => '&1', container => 'A LL');
Details: After running the Oracle setup script with an DBaaS already enabled GoldenGate, you can cause the redo log issue and receive the following error:
sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 7 10:51:17 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 3206836224 bytes Fixed Size 2257520 bytes Variable Size 1811942800 bytes Database Buffers 1375731712 bytes Redo Buffers 16904192 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 5777 Session ID: 125 Serial number: 5Answer:To fix this, run the following command:
SQL> connect / as sysdba Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 3206836224 bytes Fixed Size 2257520 bytes Variable Size 1811942800 bytes Database Buffers 1375731712 bytes Redo Buffers 16904192 bytes Database mounted. SQL> alter database clear unarchived logfile group 1; Database altered. SQL> alter database clear unarchived logfile group 2; Database altered. SQL> alter database clear unarchived logfile group 3; Database altered. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 3206836224 bytes Fixed Size 2257520 bytes Variable Size 1811942800 bytes Database Buffers 1375731712 bytes Redo Buffers 16904192 bytes Database mounted. Database opened.