Thinking Out Loud

April 13, 2018

Check 12.1.0.2 Alert Log For HugePages Usage

Filed under: 12c — mdinh @ 11:23 pm

What! Another post on hugepages – seriously?

+ grep 'Dump of system resources acquired for SHARED GLOBAL AREA' -B1 -A22 database alert log
+ tail -25
2018-04-13T09:40:23.908633-07:00
Dump of system resources acquired for SHARED GLOBAL AREA (SGA) 

2018-04-13T09:40:23.916573-07:00
 Per process system memlock (soft) limit = UNLIMITED
2018-04-13T09:40:23.920591-07:00
 Expected per process system memlock (soft) limit to lock
 SHARED GLOBAL AREA (SGA) into memory: 2996M
2018-04-13T09:40:23.928517-07:00
 Available system pagesizes:
  4K, 2048K 
2018-04-13T09:40:23.936717-07:00
 Supported system pagesize(s):
 
2018-04-13T09:40:23.943044-07:00
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2018-04-13T09:40:23.947112-07:00
     2048K             2303            1498            1498        NONE
2018-04-13T09:40:23.951899-07:00
 Reason for not supporting certain system pagesizes: 
2018-04-13T09:40:23.960107-07:00
  4K - Large pagesizes only
2018-04-13T09:40:23.965247-07:00

====================================================================================================

Tue Apr 10 12:29:13 2018
Dump of system resources acquired for SHARED GLOBAL AREA (SGA) 

Tue Apr 10 12:29:13 2018
 Per process system memlock (soft) limit = 128G
Tue Apr 10 12:29:13 2018
 Expected per process system memlock (soft) limit to lock
 SHARED GLOBAL AREA (SGA) into memory: 4002M
Tue Apr 10 12:29:13 2018
 Available system pagesizes:
  4K, 2048K 
Tue Apr 10 12:29:13 2018
 Supported system pagesize(s):
Tue Apr 10 12:29:13 2018
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
Tue Apr 10 12:29:13 2018
        4K       Configured               5         1024005        NONE
Tue Apr 10 12:29:13 2018
     2048K                0            2001               0        NONE
Tue Apr 10 12:29:13 2018
RECOMMENDATION:
Tue Apr 10 12:29:13 2018
 1. For optimal performance, configure system with expected number 
 of pages for every supported system pagesize prior to the next 
Advertisements

March 3, 2018

Upgrade 12.2 Journey – DataGuard

Filed under: 12.2,Dataguard,upgrade,Upgrade 12.2 Journey — mdinh @ 6:07 pm

Oracle Data Guard Broker Changes in Oracle Database 12c Release 2 (12.2.0.1)

How to resolve MRP stuck issues on a physical standby database? (Doc ID 1221163.1)
Starting from 12.2 use V$DATAGUARD_PROCESS view instead of v$managed_standby

February 19, 2018

How Do You Create Data Guard Configuration?

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 11:30 pm

I have taken for granted to create Data Guard Configuration the same way most of the time that I don’t know what goes wrong when done differently.

oracle@racnode-dc1-1:hawk1:/home/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 19 23:41:49 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

23:41:49 SYS @ hawk1:>show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      hawk
db_unique_name                       string      hawk
pdb_file_name_convert                string

23:42:04 SYS @ hawk1:>alter system set dg_broker_start=true sid='*' scope=memory;

System altered.

23:42:40 SYS @ hawk1:>


+++ CREATE CONFIGURATION USING UPPER CASE WITHOUT QUOTES

oracle@racnode-dc1-1:hawk1:/home/oracle
$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.

--- NO QUOTES USE AND ALL UPPERCASE - EASIEST METHOD
--- Broker convert database to match that of db_unique_name

DGMGRL> CREATE CONFIGURATION DG_CONFIG AS PRIMARY DATABASE IS HAWK CONNECT IDENTIFIER IS HAWK;
Configuration "dg_config" created with primary database "hawk"

DGMGRL> show configuration

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk

Database - hawk

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL> remove configuration
Removed configuration

--- CONFIGURATION IS UPPERCASE 
--- Does it look better in uppercase?
DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'hawk' CONNECT IDENTIFIER IS HAWK;
Configuration "DG_CONFIG" created with primary database "hawk"
DGMGRL> show configuration

Configuration - DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  hawk - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk

Database - hawk

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL> remove configuration
Removed configuration

+++ ISSUES OCCUR WHEN USING UPPERCASE WITH QUOTES FOR DATABASE
+++ Not sure if this will work as I have not tested end to end. Why create it this way to begin with?
DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'HAWK' CONNECT IDENTIFIER IS HAWK;
Configuration "DG_CONFIG" created with primary database "HAWK"
DGMGRL> show configuration

Configuration - DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  HAWK - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk
Object "hawk" was not found

DGMGRL> show database HAWK
Object "hawk" was not found

DGMGRL> show database 'HAWK';

Database - HAWK

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL>

REFERENCE:

CREATE CONFIGURATION

CREATE CONFIGURATION configuration_name AS PRIMARY DATABASE IS database-name CONNECT IDENTIFIER IS connect-identifier;

database-name
The name that will be used by the broker to refer to the primary database. 
It must match (case-insensitive) the value of the primary database DB_UNIQUE_NAME initialization parameter.

February 15, 2018

12c DataGuard / Broker Pitfalls

Filed under: 12c,Dataguard — mdinh @ 1:01 pm

In a broker configuration, you use the DGConnectIdentifer property to specify a connect identifier for each database.

The connect identifier for a database must:
Allow all other databases in the configuration to reach it.
Allow all instances of an Oracle RAC database to be reached.
Specify a service that all instances dynamically register with the listeners so that connect-time failover on an Oracle RAC database is possible.

The service should NOT be one that is defined and managed by Oracle Clusterware.

A static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used.

Else, by default, the broker assumes a static service name of db_unique_name_DGMGRL.db_domain and expects the listener has been started with the following content in the listener.ora file:

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = port_num))
  )
)

SID_LIST_LISTENER=
(SID_LIST=
  (SID_DESC=
    (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
    (ORACLE_HOME=oracle_home)
    (SID_NAME=sid_name)
    (ENVS="TNS_ADMIN=oracle_home/network/admin")
  )
)  

As of Oracle Database 12c Release 1 (12.1), for all databases to be added to a broker configuration, any LOG_ARCHIVE_DEST_n parameters that have the SERVICE attribute set, but not the NOREGISTER attribute, must be cleared.

Create Configuration Failing with ORA-16698 (Doc ID 1582179.1)

Oracle Data Guard Installation

 

February 3, 2018

PURGEOLDEXTRACTS Not Purging Trail Files Part2

Filed under: 12c,GoldenGate — mdinh @ 4:55 pm
If you read the post PURGEOLDEXTRACTS Not Purging Trail Files, you will find the solution is to replace syntax from mgr.prm as shown below:
Replace PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1
With    PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2 

I found the solution by luck vs correct analysis; hence, the adage “Better to be lucky than good.”

Recently, the same issue occurred again for another environment and the solution was just the opposite.

PURGEOLDEXTRACTS dirdat/*, USECHECKPOINTS, MINKEEPHOURS 24, FREQUENCYMINUTES 30
-- PURGEOLDEXTRACTS /DBFS/ggs/dirdat/*, USECHECKPOINTS, MINKEEPHOURS 24, FREQUENCYMINUTES 30

Why is that!

No convention and inconsistency.

Here are the details.

--- How is manager configured?
GGSCI> send manager GETPURGEOLDEXTRACTS

Sending GETPURGEOLDEXTRACTS request to MANAGER ...

--- Manager is configured with trail pointing to /DBFS
PurgeOldExtracts Rules
Fileset                              MinHours MaxHours MinFiles MaxFiles UseCP
/DBFS/ggs/dirdat/*                   24       0        1        0        Y
OK	
--- Extract trail showing from $GG_HOME/dirdat
Extract Trails
Filename                        Oldest_Chkpt_Seqno  IsTable  IsVamTwoPhaseCommit
/u01/app/gg/12.2.0/dirdat/aa    16285

--- How was the extract created?
GGSCI> send e* status

Sending STATUS request to EXTRACT E_HAWK ...


EXTRACT E_HAWK (PID 40932)
  Current status: Recovery complete: At EOF

  Current read position:
  Sequence #: 16285
  RBA: 27233729
  Timestamp: 2018-01-25 21:01:35.000450
  Extract Trail: dirdat/aa --- This is how the trail is defined when extract was created

GGSCI> info e*

EXTRACT    E_HAWK    Last Started 2018-01-25 21:22   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Process ID           40932
Log Read Checkpoint  File dirdat/aa000016286
                     2018-02-01 14:42:29.000124  RBA 29233729
GGSCI> exit

--- From $GG_HOME, dirdat is using symbolic link to /DBFS
$ ls -ld dir*
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirchk -> /DBFS/ggs/dirchk
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dircrd -> /DBFS/ggs/dircrd
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirdat -> /DBFS/ggs/dirdat
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirdef -> /DBFS/ggs/dirdef
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirdmp -> /DBFS/ggs/dirdmp
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirout -> /DBFS/ggs/dirout
drwxr-xr-x 2 ggsuser oinstall 4096 Jan 26 13:49 dirpcs
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirprm -> /DBFS/ggs/dirprm
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirrpt -> /DBFS/ggs/dirrpt
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirsql -> /DBFS/ggs/dirsql
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirtmp -> /DBFS/ggs/dirtmp
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirwlt -> /DBFS/ggs/dirwlt
lrwxrwxrwx 1 ggsuser oinstall   23 Mar 18  2017 dirwww -> /DBFS/ggs/dirwww

In conclusion, PURGEOLDEXTRACTS location should be defined the same as the extract.

Isn’t that intuitive?

Oracle should make this a MOS Doc ;=)

January 21, 2018

Be Friend With awk/sed | ASM Mapping

Filed under: 12c,ASM,awk_sed_grep — mdinh @ 5:10 pm

I had request to add disks to ASM Disk Group without any further details for what new disks were added.

Need to figure out which disks are on ASM now, which disks should be used as new ones.

Got lazy and created scripts for this for future use.

[root@racnode-dc1-1 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@racnode-dc1-1 ~]#

[oracle@racnode-dc1-1 ~]$ /etc/init.d/oracleasm listdisks
CRS01
DATA01
FRA01

--- [8,49] is major,minor for device
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01
Disk "DATA01" is a valid ASM disk on device [8,49]

--- Extract major,minor for devide
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01|awk '{print $NF}'
[8,49]

--- Remove [] brackets
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01|awk '{print $NF}'|sed s'/.$//'
[8,49
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'
8,49

--- Alternative option to remove []
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01|awk '{print $NF}'|sed 's/[][]//g'
8,49

--- Create patterns for grep
[oracle@racnode-dc1-1 ~]$ oracleasm querydisk -d DATA01|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'|awk -F, '{print $1 ",.*" $2}'
8,.*49

--- Test grep using pattern
[oracle@racnode-dc1-1 ~]$ ls -l /dev/* | grep -E '8,.*49'
brw-rw---- 1 root    disk      8,  49 Jan 21 16:42 /dev/sdd1
[oracle@racnode-dc1-1 ~]$

--- Test grep with command line syntax
[oracle@racnode-dc1-1 ~]$ ls -l /dev/*|grep -E `oracleasm querydisk -d DATA01|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'|awk -F, '{print $1 ",.*" $2}'`
brw-rw---- 1 root    disk      8,  49 Jan 21 16:42 /dev/sdd1
[oracle@racnode-dc1-1 ~]$

--- Run script
[oracle@racnode-dc1-1 ~]$ /sf_working/scripts/asm_mapping.sh
Disk "CRS01" is a valid ASM disk on device [8,33]
brw-rw---- 1 root    disk      8,  33 Jan 21 21:42 /dev/sdc1

Disk "DATA01" is a valid ASM disk on device [8,49]
brw-rw---- 1 root    disk      8,  49 Jan 21 21:42 /dev/sdd1

Disk "FRA01" is a valid ASM disk on device [8,65]
brw-rw---- 1 root    disk      8,  65 Jan 21 21:42 /dev/sde1

[oracle@racnode-dc1-1 ~]$

--- ASM Lib version
[oracle@racnode-dc1-1 ~]$ rpm -qa|grep asm
oracleasmlib-2.0.4-1.el6.x86_64
oracleasm-support-2.1.8-3.1.el7.x86_64
kmod-oracleasm-2.0.8-19.0.1.el7.x86_64
[oracle@racnode-dc1-1 ~]$

--- Script
[oracle@racnode-dc1-1 ~]$ cat /sf_working/scripts/asm_mapping.sh

#!/bin/sh -e
for disk in `/etc/init.d/oracleasm listdisks`
do
oracleasm querydisk -d $disk
#ls -l /dev/*|grep -E `oracleasm querydisk -d $disk|awk '{print $NF}'|sed s'/.$//'|sed '1s/^.//'|awk -F, '{print $1 ",.*" $2}'`
# Alternate option to remove []
ls -l /dev/*|grep -E `oracleasm querydisk -d $disk|awk '{print $NF}'|sed 's/[][]//g'|awk -F, '{print $1 ",.*" $2}'`
echo
done
[root@racnode-dc1-1 ~]# fdisk -l /dev/sdd1

Disk /dev/sdd1: 8587 MB, 8587837440 bytes, 16773120 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

[root@racnode-dc1-1 ~]#

December 26, 2017

UNIFORM_LOG_TIMESTAMP_FORMAT CANNOT BE SET ON ASM INSTANCE (Doc ID 2308274.1)

Filed under: 12.2 — mdinh @ 3:18 pm

Alert.log: New timestamp format in Oracle 12.2

Whats new with the timestamp format in Oracle 12c Release 2?

UNIFORM_LOG_TIMESTAMP_FORMAT CANNOT BE SET ON ASM INSTANCE (Doc ID 2308274.1)

It's expected behavior.  UNIFORM_LOG_TIMESTAMP_FORMAT parameter can't be set on ASM instance in 12.2.0.1.

It will be implemented in future release 18.1.

December 17, 2017

Not Another adrci Examples

Filed under: 12c,adrci — mdinh @ 4:30 am

I know there are a lot of blog posts on ADRI, etc…

However, none of them solved what I was looking for, “How to show all specific problems?”


--- When there are more than 50 incidents
50 rows fetched (*** more available ***)
show incident -all

--- Show specific problem for ORA errors
show problem -all -p "problem_key='ORA 1578'"

---Generates the package for the problem id 100 in /tmp
ips pack problem 100 in /tmp

---Generates the package for the incident id 6439 in /tmp
ips pack incident 6439 in /tmp

---Generates the package for the problem with the problem_key 'ORA 1578'
ips pack problemkey "ORA 1578"

---Generates the package with the incidents occurred in last 8 seconds.
ips pack seconds 8

---Generates the package with the incidents occurred
---between the times '2007-05-01 10:00:00.00' and '2007-05-01 23:00:00.00'
ips pack time '2007-05-01 10:00:00.00' to '2007-05-01 23:00:00.00'

December 14, 2017

12.2 Datapump Improvements

Filed under: 12.2,DataPump — mdinh @ 1:00 am

Datafile for tablespace USERS was resize to 5242880.

12.2.0.1.0
5242880 size is part of create tablespace.

Why is this important?
Manual intervention is no longer required to resize datafiles.

CREATE TABLESPACE "USERS" DATAFILE
  SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M,
  SIZE 5242880
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

12.1.0.2.0
5242880 size is part of alter tablespace.

 
CREATE TABLESPACE "USERS" DATAFILE
  SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M,
  SIZE 4194304
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

   ALTER DATABASE DATAFILE
  '+DATA/HAWK/DATAFILE/users.269.962674885' RESIZE 5242880;

Test Case:

01:01:05 SYS @ owl:>select bytes,tablespace_name,autoextensible,maxbytes from dba_data_files where tablespace_name='USERS';

     BYTES TABLESPACE_NAME                AUT   MAXBYTES
---------- ------------------------------ --- ----------
   5242880 USERS                          YES 3.4360E+10

01:01:57 SYS @ owl:>alter tablespace users add datafile size 4m;

Tablespace altered.

01:02:43 SYS @ owl:>select file_id,bytes from dba_data_files where tablespace_name='USERS';

   FILE_ID      BYTES
---------- ----------
         7    5242880
         5    4194304

01:04:09 SYS @ owl:>alter database datafile 5 resize 5242880;

Database altered.

01:05:08 SYS @ owl:>select file_id,bytes from dba_data_files where tablespace_name='USERS';

   FILE_ID      BYTES
---------- ----------
         7    5242880
         5    5242880

01:05:15 SYS @ owl:>

+++++++++++

[oracle@db-asm-1 ~]$ expdp parfile=expdp_tbs.par

Export: Release 12.2.0.1.0 - Production on Thu Dec 14 01:31:12 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA parfile=expdp_tbs.par
W-1 Startup took 1 seconds
W-1 Processing object type DATABASE_EXPORT/TABLESPACE
W-1      Completed 3 TABLESPACE objects in 0 seconds
W-1 Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /u01/app/oracle/admin/owl/dpdump/tbs.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Thu Dec 14 01:31:19 2017 elapsed 0 00:00:06

+++++++++++

[oracle@db-asm-1 ~]$ impdp parfile=impdp_tbs.par

Import: Release 12.2.0.1.0 - Production on Thu Dec 14 01:32:51 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
W-1 Startup took 0 seconds
W-1 Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA parfile=impdp_tbs.par
W-1 Processing object type DATABASE_EXPORT/TABLESPACE
W-1      Completed 3 TABLESPACE objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Thu Dec 14 01:32:54 2017 elapsed 0 00:00:02

+++++++++++

[oracle@db-asm-1 ~]$ cat /u01/app/oracle/admin/owl/dpdump/tablespaces_ddl.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/TABLESPACE
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
  SIZE 73400320
  AUTOEXTEND ON NEXT 73400320 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;


CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  SIZE 33554432
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
CREATE TABLESPACE "USERS" DATAFILE
  SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M,
  SIZE 5242880
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;


[oracle@db-asm-1 ~]$

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

01:40:59 SYS @ hawk2:>select bytes,tablespace_name,autoextensible,maxbytes from dba_data_files where tablespace_name='USERS';

     BYTES TABLESPACE_NAME                AUT   MAXBYTES
---------- ------------------------------ --- ----------
   5242880 USERS                          YES 3.4360E+10

01:41:17 SYS @ hawk2:>alter tablespace users add datafile size 4m;

Tablespace altered.

01:41:24 SYS @ hawk2:>select file_id,bytes from dba_data_files where tablespace_name='USERS';

   FILE_ID      BYTES
---------- ----------
         6    5242880
         2    4194304


01:41:34 SYS @ hawk2:>alter database datafile 2 resize 5242880;

Database altered.

01:41:56 SYS @ hawk2:>select file_id,bytes from dba_data_files where tablespace_name='USERS';

   FILE_ID      BYTES
---------- ----------
         6    5242880
         2    5242880

01:42:02 SYS @ hawk2:>

++++++++++

[oracle@racnode-dc1-2 ~]$ expdp parfile=expdp_tbs.par

Export: Release 12.1.0.2.0 - Production on Thu Dec 14 01:43:19 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA parfile=expdp_tbs.par
Startup took 12 seconds
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 4 TABLESPACE objects in 2 seconds
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /u01/app/oracle/12.1.0.2/db1/rdbms/log/tbs.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Thu Dec 14 01:44:34 2017 elapsed 0 00:00:43

++++++++++

[oracle@racnode-dc1-2 ~]$ impdp parfile=impdp_tbs.par

Import: Release 12.1.0.2.0 - Production on Thu Dec 14 01:45:48 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Startup took 1 seconds
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA parfile=impdp_tbs.par
Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 4 TABLESPACE objects in 1 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Thu Dec 14 01:45:57 2017 elapsed 0 00:00:05

[oracle@racnode-dc1-2 ~]$

++++++++++

[oracle@racnode-dc1-2 ~]$ cat /u01/app/oracle/12.1.0.2/db1/rdbms/log/tablespaces_ddl.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/TABLESPACE
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
  SIZE 26214400
  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

   ALTER DATABASE DATAFILE
  '+DATA/HAWK/DATAFILE/undotbs1.260.962253853' RESIZE 152043520;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  SIZE 213909504
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE
  SIZE 26214400
  AUTOEXTEND ON NEXT 26214400 MAXSIZE 32767M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

   ALTER DATABASE DATAFILE
  '+DATA/HAWK/DATAFILE/undotbs2.265.962254263' RESIZE 235929600;
  
CREATE TABLESPACE "USERS" DATAFILE
  SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M,
  SIZE 4194304
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

   ALTER DATABASE DATAFILE
  '+DATA/HAWK/DATAFILE/users.269.962674885' RESIZE 5242880;
[oracle@racnode-dc1-2 ~]$

December 2, 2017

Goldengate 12.3 Automatic CDR

Filed under: 12.2,GoldenGate,oracle — mdinh @ 11:51 pm

Automatic Conflict Detection and Resolution

Requirements: GoldenGate 12c (12.3.0.1) and Oracle Database 12c Release 2 (12.2) and later.

Automatic conflict detection and resolution does not require application changes for the following reasons:

  • Oracle Database automatically creates and maintains invisible timestamp columns.
  • Inserts, updates, and deletes use the delete tombstone log table to determine if a row was deleted.
  • LOB column conflicts can be detected.
  • Oracle Database automatically configures supplemental logging on required columns.

I have not had the chance to play with this yet and just only notice the documentation has been updated with details.

 

 

Next Page »

Blog at WordPress.com.