Oracle DB Admin – Page 5

Change DBID in Oracle

5 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, This article will see the following Change DBID in Oracle. NID is the tool that is used to change DBID or database name. Before changing the NID safer side take the backup of the database, and related files dependent on DB.

[root@detedb1 ~]$ ora_env
List of instances oracle : { test1 dbdr11 dbcs16 }
   Environnement oracle
  ~~~~~~~~~~~~~~~~~~~~
    ORACLE_SID ...... : dbcs16
    ORACLE_HOME ..... : /u01/app/oracle/11.2.0.3/db_home
    TNS_ADMIN ....... : /u01/app/oracle/product/11.2.0.3/db_home/network/tnsadmin
    NLS_LANG ........ : american_america.AL32UTF8
    listener...: LISTENER_DBCS

[root@detedb1 ~]$ ora_env

List of instances oracle : { test1 dbdr11 dbcs16 }

Environnement oracle

~~~~~~~~~~~~~~~~~~~~

ORACLE_SID ...... : dbcs16

ORACLE_HOME ..... : /u01/app/oracle/11.2.0.3/db_home

TNS_ADMIN ....... : /u01/app/oracle/product/11.2.0.3/db_home/network/tnsadmin

NLS_LANG ........ : american_america.AL32UTF8

listener...: LISTENER_DBCS

[root@detedb1 ~]$ nid TARGET=SYS

DBNEWID: Release 11.2.0.3.0 - Production on Thu Feb 4 19:23:28 2019

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

Password:
Connected to database DBCS16 (DBID=3216085472)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/11.2.0.3/db_home / dbcs16 /control01.ctl
   /u01/app/oracle/11.2.0.3/db_home / dbcs16 /control02.ctl

Change database ID of database DBCS16? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3216085472 to 2519222153
    Control File     /u01/app/oracle/11.2.0.3/db_home / dbcs16 /control01.ctl - modified
    Control File     /u01/app/oracle/11.2.0.3/db_home / dbcs16 /control02.ctl - modified
    Datafile File     /u01/app/oracle/11.2.0.3/db_home / dbcs16 /system_01.db - dbid changed
    Datafile File     /u01/app/oracle/11.2.0.3/db_home / dbcs16/ sysaux_01.db - dbid changed
    Datafile File     /u01/app/oracle/11.2.0.3/db_home / dbcs16 /undo1_01.db - dbid changed
    Datafile File     /u01/app/oracle/11.2.0.3/db_home / dbcs16 /ttools_01.db - dbid changed
    Datafile           /u01/app/oracle/11.2.0.3/db_home / dbcs16 /sbg_d1_01.db - dbid changed
    Datafile 	/u01/app/oracle/11.2.0.3/db_home / dbcs16 /sbg_i1_01.db - dbid changed
    Datafile 	/u01/app/oracle/11.2.0.3/db_home / dbcs16 /temp1_01.db - dbid changed
    Control File 	/u01/app/oracle/11.2.0.3/db_home / dbcs16 /control01.ctl - dbid changed
    Control File 	/u01/app/oracle/11.2.0.3/db_home / dbcs16 /control02.ctl - dbid changed
    Instance shut down

Database ID for database DBCS16 changed to 2519222153.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

[root@detedb1 ~]$ nid TARGET=SYS

DBNEWID: Release 11.2.0.3.0 - Production on Thu Feb 4 19:23:28 2019

Password:

Connected to database DBCS16 (DBID=3216085472)

Connected to server version 11.2.0

Control Files in database:

/u01/app/oracle/11.2.0.3/db_home / dbcs16 /control01.ctl

/u01/app/oracle/11.2.0.3/db_home / dbcs16 /control02.ctl

Change database ID of database DBCS16? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 3216085472 to 2519222153

Control File /u01/app/oracle/11.2.0.3/db_home / dbcs16 /control01.ctl - modified

Control File /u01/app/oracle/11.2.0.3/db_home / dbcs16 /control02.ctl - modified

Datafile File /u01/app/oracle/11.2.0.3/db_home / dbcs16 /system_01.db - dbid changed

Datafile File /u01/app/oracle/11.2.0.3/db_home / dbcs16/ sysaux_01.db - dbid changed

Datafile File /u01/app/oracle/11.2.0.3/db_home / dbcs16 /undo1_01.db - dbid changed

Datafile File /u01/app/oracle/11.2.0.3/db_home / dbcs16 /ttools_01.db - dbid changed

Datafile /u01/app/oracle/11.2.0.3/db_home / dbcs16 /sbg_d1_01.db - dbid changed

Datafile /u01/app/oracle/11.2.0.3/db_home / dbcs16 /sbg_i1_01.db - dbid changed

Datafile /u01/app/oracle/11.2.0.3/db_home / dbcs16 /temp1_01.db - dbid changed

Control File /u01/app/oracle/11.2.0.3/db_home / dbcs16 /control01.ctl - dbid changed

Control File /u01/app/oracle/11.2.0.3/db_home / dbcs16 /control02.ctl - dbid changed

Instance shut down

Database ID for database DBCS16 changed to 2519222153.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.

DBNEWID - Completed succesfully.

Create and configure a virtual cloud network(VCN)

5 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, This article will see the following Create and configure a virtual cloud network(VCN). Sign in to Oracle Cloud Infrastructure Console using your cloud tenant name, user name, and password. From the OCI services menu, click Virtual Cloud Networks under Networking. Choose the appropriate… Read More

Install SQLT and Query Slowness resolved over SQLT profile method

5 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, This article will see the following Install SQLT and Query Slowness resolved over SQLT profile method. Installing SQLT SQLT installs under its own schemas SQLTXPLAIN and SQLTXADMIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1,… Read More

Important Oracle v$parameters (SecureCRT)

5 years ago by Vinod SiramSecure CRT

Hi Dears, In this article, we will see Important Oracle v$parameters (SecureCRT) Tab Name : v$parameter Button Name : SGA

col name for a50 
set linesize 300 
col value for a40  
select name,value from v$parameter where name like '%sga%'; 
select &sga/1024/1024/1024 GB from dual;

col name for a50

set linesize 300

col value for a40

select name,value from v$parameter where name like '%sga%';

select &sga/1024/1024/1024 GB from dual;

Output :

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
sga_max_size                                       4294967296
pre_page_sga                                       TRUE
lock_sga                                           FALSE
allow_group_access_to_sga                          FALSE
sga_target                                         4294967296
unified_audit_sga_queue_size                       1048576

NAME VALUE

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

sga_max_size 4294967296

pre_page_sga TRUE

lock_sga FALSE

allow_group_access_to_sga FALSE

sga_target 4294967296

unified_audit_sga_queue_size 1048576

select &sga/1024/1024/1024 GB from dual; 
Enter value for sga: 4294967296

        GB
----------
         4

select &sga/1024/1024/1024 GB from dual;

Enter value for sga: 4294967296

----------

Button Name : SGA_TARGET

col name for a50 
set linesize 300 
col value for a40  
select name,value from v$parameter where name like '%sga_target%'; 
select &sga_target/1024/1024/1024 GB from dual;

col name for a50

set linesize 300

col value for a40

select name,value from v$parameter where name like '%sga_target%';

select &sga_target/1024/1024/1024 GB from dual;

Output :

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
sga_target                                         4294967296

select &sga_target/1024/1024/1024 GB from dual; 
Enter value for sga_target: 4294967296

        GB
----------
         4

NAME VALUE

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

sga_target 4294967296

select &sga_target/1024/1024/1024 GB from dual;

Enter value for sga_target: 4294967296

----------

Button Name : PGA_Aggregate_target

SQL>   col name for a50 
SQL> set linesize 300 
SQL> col value for a40  
select name,value from v$parameter where name like '%pga_aggregate_target%'; 
select &pga_aggregate_target/1024/1024/1024 GB from dual;

SQL> col name for a50

SQL> set linesize 300

SQL> col value for a40

select name,value from v$parameter where name like '%pga_aggregate_target%';

select &pga_aggregate_target/1024/1024/1024 GB from dual;

Output :

SQL> select &pga_aggregate_target/1024/1024/1024 GB from dual; 

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
pga_aggregate_target                               2147483648

SQL> select &pga_aggregate_target/1024/1024/1024 GB from dual;

NAME VALUE

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

pga_aggregate_target 2147483648

SQL> Enter value for pga_aggregate_target: 2147483648

        GB
----------
         2

SQL> Enter value for pga_aggregate_target: 2147483648

----------

Button Name :… Read More

How to Switch/Recreate Undo Tablespace at PDB Level(12cR2)

5 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see How to Switch/Recreate Undo Tablespace at PDB Level(12cR2) In Oracle Database 12c Release 1 all containers in an instance shared the same undo tablespace. In Oracle 12c Release 2 each container in an instance can use its… Read More

Manage SQL Profile in Oracle Database

5 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see Manage SQL Profile in Oracle Database A SQL profile is a collection of auxiliary statistics on a query, including all tables and columns referenced in the query. The profile is stored in the data dictionary. The optimizer… Read More

Resource Manager in Oracle

5 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see Resource Manager in Oracle Resource Manager is an enterprise edition feature which will restrict sessions consuming high resources. We can assign shares to sessions depends on priority of session. We can restrict degree of parallelism for group… Read More

Oracle Sample Unix script to monitor select query during Load Test

5 years ago by Rakesh SahuOracle DB Admin

Hi Dears, In this article, we will see Oracle Sample Unix script to monitor select query during Load Test

#!/bin/bash
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
ORACLE_SID=ORCL

while :
do
sqlplus -s / as sysdba <<EOF
set pagesize 0
set linesize 300
select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('|
|W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv\$session where BLOCKING_SESSION >0) W,
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv\$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID;
EOF
date
echo "Hit [CTRL+C] to stop!"
sleep 5
done

#!/bin/bash

ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

ORACLE_SID=ORCL

while :

sqlplus -s / as sysdba <<EOF

set pagesize 0

set linesize 300

select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('|

|W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from

(select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv\$session where BLOCKING_SESSION >0) W,

(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv\$session ) B

where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID;

EOF

date

echo "Hit [CTRL+C] to stop!"

sleep 5

done

Output :

Wed Mar 24 11:54:39 PDT 2021
Hit [CTRL+C] to stop!

no rows selected

Wed Mar 24 11:54:44 PDT 2021
Hit [CTRL+C] to stop!

no rows selected

Wed Mar 24 11:54:39 PDT 2021

Hit [CTRL+C] to stop!

no rows selected

Wed Mar 24 11:54:44 PDT 2021

Hit [CTRL+C] to stop!

no rows selected

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates KTEXPERTS is always… Read More

Configure Active DataGuard and DG BROKER

5 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see Configure Active DataGuard and DG BROKER. Oracle Active Data Guard allows DBAs and users to run real-time queries on a physical standby system for reporting. Queries and reports can be offloaded from the production system to… Read More

Difference between ROWID and ROWID BATCHED in Oracle

5 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see Difference between ROWID and ROWID BATCHED in Oracle. TABLE ACCESS BY INDEX ROWID BATCHED is new execution plan operation introduced in oracle 12c to improve performace by make efficient and minimal read writes to the disk. It… Read More