MySQL to Oracle Heterogeneous Unidirectional Replication using Slave Server Bin log In this article we will see MySQL to Oracle Heterogeneous Unidirectional Replication using Slave Server Bin log First setup Master -slave replication between two mysql servers. Use below link to setup Master-slave Replication MySQL… Read More
SSH Connectivity between two nodes (Manual) In this article we will see how to make SSH connection manually between two nodes. Node Details : Node1 : RAC1 Node2 : RAC2 Follow below steps to setup SSH In Node1 (RAC1) : Create directory called .ssh… Read More
Implementing Interval Partition with Enable Row Movement In this article , we will learn how to implement interval partition with enable row movement. Table Creation with interval partition (without interval Row Movement. Create user
|
1 2 3 |
SYS>>grant dba to ajay identified by ajay; Grant succeeded. |
Now create table with interval partition.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SYS>>CREATE TABLE AJAY.MIC_INS_DD ( ID VARCHAR2(32 CHAR) NOT NULL, NAME VARCHAR2(35 CHAR) NOT NULL, KEY VARCHAR2(30 CHAR) NOT NULL, MIC_TAG_ID DATE default (to_date('01-01-1999', 'MM-DD-YYYY')) ) TABLESPACE MIC_DATA RESULT_CACHE (MODE DEFAULT) PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( MAXSIZE UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) PARTITION BY RANGE (MIC_TAG_ID) INTERVAL( NUMTODSINTERVAL(1, 'DAY')) ( PARTITION VALUES LESS THAN (TO_DATE('01-01-1999', 'MM-DD-YYYY', 'NLS_CALENDAR=GREGORIAN')) LOGGING NOCOMPRESS TABLESPACE MIC_DATA PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 8M NEXT 1M MAXSIZE UNLIMITED MINEXTENTS 1 MAXEXTENTS UNLIMITED BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; |
Now try to… Read More
Online Segment Shrink A segment is a logical storage structure that contains data structure (segment type) of database objects. A segment is a set (unordered) of extents. A segment and all its extents are stored in one tablespace. From Oracle Database 10g, we can use the segment shrinking capability to reclaim unused… Read More
Generate DDL for Tables. In this article , we will see Generating DDL for tables in various methods. Generating DDL is much easier using Plsql Package Plsql Package : DBMS_METADATA.GET_DDL Method 1 : The below script will help you to take DDL only for a… Read More
SQL92_SECURITY Oracle Security Audit: Q: How to improve security level for DML transactions?. Ans: By setting the SQL92_SECURITY parameter we can improve the security level for DML transactions. The configuration option SQL92_SECURITY specifies whether table-level SELECT privileges are required to execute an update or… Read More
PERFORMING RMAN CLONING ON DIFFERENT SERVERS In this article we will implement RMAN Cloning on different servers.
|
1 2 3 4 |
Server 1 : PRIMARY Ip Address : 192.168.0.101 Instance name : Primary Database Name : Primary |
|
1 2 3 4 |
Server 2 : Clone server Ip Address : 192.168.0.102 Instance name : clone_pr Database Name : clone_pr |
Server 1 : Check instance information on server 1
|
1 2 3 4 |
SYS>>select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PRIMARY - PRIMARY@primary.orcl.com - prod_stand - 12.1.0.2.0 - READ WRITE - 25-APR-2019 20:49:24 - PRIMARY |
Check schema report by connecting RMAN
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
[oracle@primary ~]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Mon Apr 29 20:07:30 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD (DBID=1836049007) RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name PROD_STAND List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 300 SYSTEM YES /u01/oradata/primary/system.dbf 2 310 SYSAUX NO /u01/oradata/primary/sysaux.dbf 3 100 UNDOTBS1 YES /u01/oradata/primary/undotbs.dbf 4 150 USERDATA NO /u01/oradata/primary/userdata.dbf 5 10 TEST NO /u01/oradata/primary/test.dbf 6 10 DMTS1 NO /u01/oradata/primary/dmts1.dbf 7 10 DMTS2 NO /u01/oradata/primary/dmts2.dbf 8 10 TS_2K NO /u01/oradata/primary/ts_2k.dbf 9 50 TS1 NO /u01/oradata/primary/ts1.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 100 TEMP1 100 /u01/oradata/primary/temp1.dbf |
Check archive-log
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[oracle@primary ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 29 20:09:43 2019 Copyrght (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, OLAP, Advanced Analytics and Real Application Testing options SYS>>archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oradata/primary/arch Oldest online log sequence 157 Next log sequence to archive 158 Current log sequence 158 |
Shutdown the database and keep database… Read More
TOP SQL’s In this article,we will get notified for TOP SQL’s ORDERED BY ELAPSED TIME Step 1 : Create temporary tables to store TOP SQL’s
|
1 2 3 4 5 |
SYS>>CREATE TABLE dbmon.TOP_ELAPSEDTIME_SQLIDS ( SQL_ID VARCHAR2(20 CHAR), LASTMODIFIEDDATE DATE DEFAULT SYSDATE NOT NULL ); |
Another table to store ALL SQL’s.
|
1 2 3 4 5 |
SYS>>CREATE TABLE dbmon.ALL_ELAPSEDTIME_SQLIDS ( SQL_ID VARCHAR2(20 CHAR), LASTMODIFIEDDATE DATE DEFAULT SYSDATE NOT NULL ); |
Step 2 : Create the below scripts to capture TOP SQL’s in tables… Read More
Oracle 12c DataGuard Switchover and Switchback Please check my previous article on Active standby database. How to build ACTIVE STANDBY DATABASE using RMAN DUPLICATE command in 12c.(Video included) After configuring data guard, databases would be either primary and standby database role, and we can altered… Read More
Dear Readers, In this article, we will see the following Database Archiving in Oracle Database 12c. In this article, We will see how we can implement In-Database Archiving. As an oracle DBA we have biggest challenge is how to deal with historical vast quantities of… Read More
