using nid to change oracle database SID or oracle database name

In this article, we will discuss using the nid utility to change database sid or database name without dropping and recreating the database, before starting this activity make sure you have a good backup of the database as a rollback strategy.

 current database name:        technodb

new database name:              oracledb

 we need to perform one step after another. 

Step 1: Create pfile from spfile or take backup of pfile or password file

SQL> show parameter pfile

 NAME                  VALUE

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

spfile                C:\APP\ORACLE\PRODUCT\11.2.0.4\DBHOME_1\ DATABASE\SPFILETECHNODB.ORA

 

SQL> create pfile from spfile;

File created.

SQL>

Step 2: shutdown the database and again open the database using the startup mount option

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup mount

ORACLE instance started. 

Total System Global Area 3156725760 bytes

Fixed Size                  2179896 bytes

Variable Size            1728056520 bytes

Database Buffers         1409286144 bytes

Redo Buffers               17203200 bytes

Database mounted.

 

Step 3: run nid utility with syntax

nid target=sys/<password>@technodb dbname=oracledb 

C:\app\oracle>nid target=sys/<password>@technodb dbname=oracledb

 

DBNEWID: Release 11.2.0.1.0 - Production on Sat Jul 2 23:10:18 2022

 

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

Connected to database TECHNODB (DBID=2727431841)

Connected to server version 11.2.0

Control Files in database:

    C:\APP\ORACLE\ORADATA\TECHNODB\CONTROL01.CTL

    C:\APP\ORACLE\FLASH_RECOVERY_AREA\TECHNODB\CONTROL02.CTL

Change database ID and database name TECHNODB to ORACLEDB? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 2727431841 to 3726858970

Changing database name from TECHNODB to ORACLEDB

    Control File C:\APP\ORACLE\ORADATA\TECHNODB\CONTROL01.CTL - modified

    Control File C:\APP\ORACLE\FLASH_RECOVERY_AREA\TECHNODB\CONTROL02.CTL - modified

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\SYSTEM01.DB - dbid changed, wrote new name

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\SYSAUX01.DB - dbid changed, wrote new name

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\UNDOTBS01.DB - dbid changed, wrote new name

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\USERS01.DB - dbid changed, wrote new name

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\EXAMPLE01.DB - dbid changed, wrote new name

    Datafile C:\APP\ORACLE\ORADATA\TECHNODB\TEMP01.DB - dbid changed, wrote new name

    Control File C:\APP\ORACLE\ORADATA\TECHNODB\CONTROL01.CTL - dbid changed, wrote new name

    Control File C:\APP\ORACLE\FLASH_RECOVERY_AREA\TECHNODB\CONTROL02.CTL - dbid changed, wrote new name

    Instance shut down

 

Database name changed to ORACLEDB.

Modify parameter file and generate a new password file before restarting.

Database ID for database ORACLEDB changed to 3726858970.

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

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Successfully changed database name and ID.

DBNEWID - Completed successfully.

C:\app\oracle>

Step 4: As in above, we can see the database name is changed, and the database is shutdown now we need to change it in spfile as well. Open database in nomount mode and change database name using alter system command

 

C:\app\oracle >sqlplus 

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 2 23:12:19 2022 

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

Enter user-name: /as sysdba

Connected to an idle instance. 

SQL> startup nomount

ORACLE instance started. 

Total System Global Area 3156725760 bytes

Fixed Size                  2179896 bytes

Variable Size            1728056520 bytes

Database Buffers         1409286144 bytes

Redo Buffers               17203200 bytes

SQL>

 

SQL> alter system set db_name=oracledb scope=spfile;

System altered.

SQL>

 

Step 5: if you are using database on Linux/Unix- change the entry in oratab and if you are using the database on windows, then create a new sid using oradim utility, I have my database on windows, so I have created a new sid in windows services using oradim and delete old sid

 C:\windows\system32>oradim -new -sid oracledb

Instance created.

 

C:\windows\system32>oradim -delete -sid technodb

Instance deleted.

 

C:\windows\system32>

 

Step 6: Now change spfile name to with oracle database name  from spfiletechnodb.ora to spfileoracledb.ora and open the database step-wise as we need to open the database using resetlogs options

SQL> startup nomount

ORACLE instance started. 

Total System Global Area 3156725760 bytes

Fixed Size                  2179896 bytes

Variable Size            1728056520 bytes

Database Buffers         1409286144 bytes

Redo Buffers               17203200 bytes

SQL>

SQL> alter database mount;

Database altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      ORACLEDB

SQL>

SQL> alter database open resetlogs;

 

Step 7: Change entry in the listener.ora and tnsnames.ora and just reload the listener using command

lsnrctl reload LISTENER 

In this way, you can database name has been changed, remember, only the database name can be changed

Read More

Installation of Python 3.10 on windows

This is going to be a short article with just a few steps for the installation of python

Step 1: download python for windows for installation from https://www.python.org/downloads/windows

Step 2: click on .exe file for execution, you will see below the window

click on Add Python 3.10 to PATH as well. then click on Install Now 

Step 3: Monitor installation progress

Step 4: Click on finish /close

Step 4: Verify- open the command prompt and type the command python, it will display the version of python

Read here: Introduction and running the first program 

Read More

Introduction to Python and Writing first program

Python is an open-source programming language, open-source refers to free availability and cost-effectiveness. 

Python is a cross-platform language- it means once the program is written in python, the same code can be executed on any operating system platform such as Windows, Linux, Unix, Mac, etc. python is object-oriented programming.

Python has a very huge library of built-in functions and is easy to learn. Using the python application will be able to connect to any major databases such as MySQL, Oracle, or PostgreSQL. 

Control structure in python is used to control the run of the python program. program is basically a set of instructions provided to the system. The program might be just straight execution or might be skipping a few statements and continuing run or jumping to statement and then again coming back to the original statement. 

Writing the first program and executing it

First of all, you need to have python installed on the machine in order to run python programs. 

you can download installed from https://www.python.org/downloads/windows, you can follow simple instructions from here, once the installation is done, just verify using the command python

C:\Users\shaik>python

Python 3.10.5 (tags/v3.10.5:f377153, Jun  6 2022, 16:14:13) [MSC v.1929 64 bit (AMD64)] on win32

Type "help", "copyright", "credits" or "license" for more information.

>>> 

>>> 

Once you get confirmation that python is installed, write the below code in a text file and save it as hello.py

#This is first program of hello world

print("hellow world!!! :) ") 

Once the writing is done, open command prompt and cd to location when hello.py is kept.

and run the script/program using python hello.py

In this way, you can run the python program on your windows machine.

Read More

ORA-39161: Full database jobs require privileges

I was trying to take full export backup of the database using user from the pluggable database, the export command threw below error

 

Users\shaik>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

 

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:42:47 2022

Version 19.3.0.0.0

 

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

Password:

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

ORA-31631: privileges are required

ORA-39161: Full database jobs require privileges

 

I realized that SYS, SYSTEM does not need any explicit privileges to be granted while performing export and import, but while using any other user, we need to grant proper privileges to take full database export.

 

SQL> alter session set container=technopdb;

 

Session altered.

 

SQL> grant DATAPUMP_EXP_FULL_DATABASE to techno_user;

 

Grant succeeded.

 

Here I granted DATAPUMP_EXP_FULL_DATABASE privileges to user and tried taking export and it worked

 

C:\Users\shaik>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

 

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:44:50 2022

Version 19.3.0.0.0

 

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

Password:

 

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "TECHNO_USER"."SYS_EXPORT_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

 

 

Here we need to note that, taking export and performing import requires special privileges

Read More

Check datapump expdp/impdp job status

 As we learned how to use data pump export/import utilities here, now we will discuss checking the datapump job and taking action on it like terminating the job or resuming, etc.

We will take backup of full database, there is a way to add job name to backup using JOB_NAME parameter, if do not use this parameter, the oracle will automatically generate one job in the database which can be checked under dba_datapump_jobs table or can be checked at server level logs of the expdp/impdp

Below is the command I am using to take export of full database 

expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Here,

I have created directory export_dir pointing to a physical location having read write access, from the command prompt, I executed the command to take export of full database

C:\Users\shaik>expdp userid=techno_user@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:44:50 2022

Version 19.3.0.0.0

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

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "TECHNO_USER"."SYS_EXPORT_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/STATISTICS/MARKER

While this was running, I opened a new command prompt and logged in to the database and checked view dba_datapump_jobs

SQL> select owner_name, job_mode, operation,job_name from dba_datapump_jobs;

OWNER_NAME  JOB_MODE  OPERATION  JOB_NAME

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

TECHNO_USER  FULL      EXPORT     SYS_EXPORT_FULL_01

here I noted job name SYS_EXPORT_SCHEMA_01 and the same can be found from the export command log

Starting "TECHNO_USER"."SYS_EXPORT_FULL_01":  userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

 

Once you get the job name, use the below command to check the status of the export taken using expdp

 

C:\Users\shaik>expdp userid =techno_user@technopdb attach=SYS_EXPORT_FULL_01

Export: Release 19.0.0.0.0 - Production on Tue Jun 14 20:45:38 2022

Version 19.3.0.0.0

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

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Job: SYS_EXPORT_FULL_01

  Owner: TECHNO_USER

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: 19C1A2C6616645CDB516452D40951467

  Start Time: Tuesday, 14 June, 2022 20:44:54

  Mode: FULL

  Instance: technodb

  Max Parallelism: 1

  Timezone: +00:00

  Timezone version: 32

  Endianness: LITTLE

  NLS character set: AL32UTF8

  NLS NCHAR character set: AL16UTF16

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        userid=techno_user/********@technopdb directory=export_dir dumpfile=expdp_full_db.dmp logfile=expdp_full.log full=y

     TRACE                 0

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Job heartbeat: 6

  Dump File: C:\DOWNLOADS\ARCH\EXPDP_FULL_DB.DMP

    bytes written: 135,168

Here you can see almost every detail of the export job running. Using the help command, you can get a list of activities you can perform

the job can be killed using the kill immediate command

Export> kill job=immediate;

Are you sure you wish to stop this job ([yes]/no): yes

In this way, you can do a lot of things with export attach command. 

 

Read More

Rename pluggable database

There might be a situation when you created a pluggable database with the wrong name then it's not time to worry, you can simply rename it using rename command, in another situation where the client requested to change the name of the pluggable database, then rename command will to amazing work. Here we will discuss renaming oracle PDB

 

Below are the pluggable database present in my system I want to rename technopdb3 to technopdb2 

Below are steps to perform it

The first step is to close the pluggable database 

 

SQL> alter pluggable database technopdb3 close immediate;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB3                     MOUNTED

         5 TECHNOPDB_NEW                  READ WRITE NO

SQL>

 

pluggable database technopdb3 has been closed, now open same pluggable database in restricted mode 

 

SQL> alter pluggable database technopdb3 open restricted;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                      OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB3                     READ WRITE YES

         5 TECHNOPDB_NEW                  READ WRITE NO

 

now connect to pluggable database using alter session command and execute rename command as mentioned below 

 

SQL> alter session set container=technopdb3;

 

Session altered.

 

SQL> alter pluggable database technopdb3 rename global_name to technopdb2;

 

Pluggable database altered.

 

 

Now close the pluggable database and open in normal mode

 

SQL> alter pluggable database close;

Pluggable database altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> show pdbs

 

    CON_ID CON_NAME                      OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 TECHNOPDB                      READ WRITE NO

         4 TECHNOPDB2                     READ WRITE NO

         5 TECHNOPDB_NEW                  READ WRITE NO

SQL>

 

In this way, technopdb3 has been renamed to technopdb2

Read More