pfile and spfile in oracle database

Atikh Shaikh 12c, oracle, Oracle 12c, oracle 19c No comments

In this short article, we will discuss the server parameter file and initialization parameter file i.e., spfile and pfile

pfile - parameter file, is a text-based file, readable and editable with any text editor

spfile- server parameter file, binary file, readable with only some utility and can not be edited.

pfile is called the initialization parameter file because this is the first file used to start (initialize) the database, spfile is created later on.

The default extension of these files is ".ora", in an ASM file name can be with any random or fixed number i.e. spfiletechnodb.1223.343543 (technodb is database name)

The following parameters can be seen in the parameter files

memory parameters such as memory target, sga, pga, shared_pool, java pool, large pool
database and instance name (can be different)
parameters related to different physical files such as control file, datafiles, archive files
processes and session details
trace file location, audit settings, and many more

the very first stage of the database startup is "nomount" and that’s where this parameter file will get read, it will load database memory and process settings and takes the location of control files.

The default location of the parameter file is based on the operating system you are using

in Linux/Unix it is located at $ORACLE_HOME/dbs location and for windows operation system it is %ORACLE_HOME%\database

Parameter change

Assume your database is running on pfile and you want to change the parameter of the database, then you need to perform the below steps

shutdown the database
add/change the parameter in pfile
start the database

Now assume your database is running on spfile- then based on condition, you may not at all need to restart the database

change the parameter using alter system command
Restart the database if required

using nid to change oracle database SID or oracle database name

Atikh Shaikh oracle No comments

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

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

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

Installation of Python 3.10 on windows

Atikh Shaikh mysql, oracle, PostgreSQL, python No comments

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

Introduction to Python and Writing first program

Atikh Shaikh oracle, python No comments

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.

ORA-39161: Full database jobs require privileges

Atikh Shaikh 12c, oracle, oracle19c No comments

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

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

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

Check datapump expdp/impdp job status

Atikh Shaikh Backup and Recovery, oracle No comments

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

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

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.

pfile and spfile in oracle database

using nid to change oracle database SID or oracle database name

Installation of Python 3.10 on windows

Introduction to Python and Writing first program

ORA-39161: Full database jobs require privileges

Check datapump expdp/impdp job status

Author

Categories

Blog Archive

Popular Posts

Labels

Pages