Thinking Out Loud

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

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

Using sed to backup file and remove lines

Filed under: awk_sed_grep — mdinh @ 1:13 am
[oracle@racnode-dc1-2 ~]$ cd /u01/app/oracle/12.1.0.2/db1/rdbms/log/

--- DDL will fail since datafile is hard coded!
[oracle@racnode-dc1-2 log]$ cat 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;

--- Remove ALTER and RESIZE from sql file.
--- Most likely the incorrect way to do this since TBS may be undersized.

12.2 Datapump Improvements actually does this the right way.

[oracle@racnode-dc1-2 log]$ sed -i.bak '/ALTER DATABASE DATAFILE\|RESIZE/ d' tablespaces_ddl.sql

[oracle@racnode-dc1-2 log]$ ls -l tablespace*
-rw-r--r-- 1 oracle dba 1214 Dec 14 02:03 tablespaces_ddl.sql
-rw-r--r-- 1 oracle dba 1488 Dec 14 01:45 tablespaces_ddl.sql.bak

[oracle@racnode-dc1-2 log]$ diff tablespaces_ddl.sql tablespaces_ddl.sql.bak
14a15,16
>    ALTER DATABASE DATAFILE
>   '+DATA/HAWK/DATAFILE/undotbs1.260.962253853' RESIZE 152043520;
24a27,28
>    ALTER DATABASE DATAFILE
>   '+DATA/HAWK/DATAFILE/undotbs2.265.962254263' RESIZE 235929600;
32a37,38
>    ALTER DATABASE DATAFILE
>   '+DATA/HAWK/DATAFILE/users.269.962674885' RESIZE 5242880;

[oracle@racnode-dc1-2 log]$ cat 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;

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;

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;

[oracle@racnode-dc1-2 log]$

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.

 

 

Create a free website or blog at WordPress.com.