KTEXPERTS – Page 52 – KNOWLEDGE SHARING PLATFORM

RENAMING INSTANCE

7 years ago by Binnary Ajay KumarOracle DB Admin

RENAMING INSTANCE In this article we will learn how to rename a oracle instance. Step 1 : Check instance information

SYS>>select instance_name from v$instance;
Inst-Name
----------
primary

SYS>>select instance_name from v$instance;

Inst-Name

----------

primary

My instance name is primary. No i want to convert Primary to PROD. Step 2 : Shutdown the database

SYS>>shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>>exit
Disconnected from 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>>shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS>>exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Step 3 : Change… Read More

Renaming the Oracle Database.

7 years ago by Binnary Ajay KumarOracle DB Admin

Renaming the Oracle Database. In this article we will learn how to rename a oracle database using trace controlfile . RENAMING DATABASE : Step 1 : Check the database name

SYS>>select name from v$database;
NAME
----------------------------------------
PRIMARY

SYS>>select name from v$database;

NAME

----------------------------------------

PRIMARY

My Old Database name is PRIMARY Step 2 : Create a trace control file… Read More

Controlfile Multiplexing and De-Multiplexing In Oracle12c

7 years ago by Binnary Ajay KumarOracle DB Admin

Controlfile Multiplexing and De-Multiplexing In Oracle12c In this article we will learn what controlfile contains,How to perform controlfile Multiplexing and De-Multiplexing . Control file : Controlfile is a Physical file. Controlfile is a small binary file which contains the current physical structure of database. Control… Read More

Different Block Size of ORACLE

7 years ago by Binnary Ajay KumarOracle DB Admin

Different Block Size of ORACLE Oracle 9i supports tablespaces with different block sizes. This allows large tables and indexes to have a larger block size than smaller objects. It also means that tablespaces can be transported between databases with differing block sizes. The database is… Read More

Storage Management in Oracle -3

7 years ago by Binnary Ajay KumarOracle DB Admin

Storage Management in Oracle -3 In article we will learn creating tablespaces with storage parameters(DEMO). Before reading this article please check previous articles. Storage Management in Oracle -2 Demo On Storage Management : To view information about storage management use DBA_TABLESPACES or V$TABLESPACE

SYS>> desc dba_tablespaceselect TABLESPACE_NAME,EXTENT_MANAGEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces;
TS-Name    Extent-Man   Init-Ext   Next-Ext    Min-Ext    Max-Ext    Pct-Inc
---------- ---------- ---------- ---------- ---------- ---------- ----------
SYSTEM     DICTIONARY      16384      16384          1        505         50
SYSAUX     LOCAL           65536                     1 2147483645
UNDOTBS1   LOCAL           65536                     1 2147483645
TEMP       LOCAL         1048576    1048576          1                     0
USERDATA   LOCAL           65536                     1 2147483645
TEST       LOCAL           65536                     1 2147483645

6 rows selected.

SYS>> desc dba_tablespaceselect TABLESPACE_NAME,EXTENT_MANAGEMENT,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE from dba_tablespaces;

TS-Name Extent-Man Init-Ext Next-Ext Min-Ext Max-Ext Pct-Inc

---------- ---------- ---------- ---------- ---------- ---------- ----------

SYSTEM DICTIONARY 16384 16384 1 505 50

SYSAUX LOCAL 65536 1 2147483645

UNDOTBS1 LOCAL 65536 1 2147483645

TEMP LOCAL 1048576 1048576 1 0

USERDATA LOCAL 65536 1 2147483645

TEST LOCAL 65536 1 2147483645

6 rows selected.

Note… Read More

Oracle:HEALTH CHECK REPORT(Capturing Archive information).

7 years ago by Vinod SiramOracle DB Admin

Oracle:HEALTH CHECK REPORT(Capturing Archive information). In this article we will implement to capture the Archive information using Shell script. Lets create a table which can store Archive information. Connect to SYS user and execute below statement.

CREATE TABLE DBMON.ARCHIVE_USAGE_HISTORY
(
  GENERATION_DATE  DATE                         NOT NULL,
  INST_ID          NUMBER                       NOT NULL,
  TOTAL_DAY_GB     NUMBER
)
TABLESPACE DATA01
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE TABLE DBMON.ARCHIVE_USAGE_HISTORY

(

GENERATION_DATE DATE NOT NULL,

INST_ID NUMBER NOT NULL,

TOTAL_DAY_GB NUMBER

)

TABLESPACE DATA01

RESULT_CACHE (MODE DEFAULT)

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

INITIAL 64K

NEXT 1M

MAXSIZE UNLIMITED

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

FLASH_CACHE DEFAULT

CELL_FLASH_CACHE DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

Create unique index and primary key on ARCHIVE_USAGE_HISTORY… Read More

Storage Management in Oracle -2

7 years ago by Binnary Ajay KumarOracle DB Admin

Storage Management in Oracle -2 In this article we will learn about Storage Parameters . Before reading this article please go through with previous article. Storage Management in Oracle -1 Storage Hierarchy Summary In summary, the hierarchy of storage in Oracle is as follows: 1.… Read More

Storage Management in Oracle -1

7 years ago by Binnary Ajay KumarOracle DB Admin

Storage Management in Oracle -1 In this article we will learn about Storage Management . A Database is a collection of Logical and Physical Phases.In Logical phase we have DB objects. A Tablespace is a logical storage container in Oracle that comes at the top… Read More

ORACLE APPS : EBS ARCHITECTURE-1

7 years ago by Rajesh kumarOracle Apps DBA

E-Business Suite(EBS) ARCHITECTURE-1 Hello Everyone previously we learnt about Introduction to apps dba and EBS. Please check below link. ROLES & RESPONSIBILITIES OF ORACLE APPS DBA Now let us see about EBS architecture. When we come to oracle EBS basically it is a triple tier… Read More

Moving Online Data files in Oracle 12c

7 years ago by Binnary Ajay KumarOracle 12C New Features

Dear Readers, In this article, we will see the following Moving Online Data files in Oracle 12c. In order to rename, move and relocate a datafile up to 11g, we had to put a tablespace or database completely offline. As we can see here drawback… Read More