sqlplus: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory
iii. Client re-installation
Cleanup up old binaries
[oracle@testlaba /]$ cd /rdbms[oracle@testlaba rdbms]$ ls -lrt
total 28
drwx------. 2 oracle root 16384 Jul 14 17:47 lost+found
drwx------. 3 oracle oracle 4096 Jul 19 12:47 client_dump
drwxr-xr-x. 2 oracle oracle 4096 Jul 19 12:59 oraInventory
drwx------. 6 oracle oracle 4096 Jul 19 13:49 app
What is oracle database ? Part VIII
5. Sqlnet
The "sqlnet.ora" file contains client side network configuration parameters located at "$ORACLE_HOME/network/admin" or "$ORACLE_HOME/net80/admin" directory on the client.
This file will also be present on the server if client style connections are used on the server itself.
Here is an example of an "sqlnet.ora" file.
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH set to specify the order of the naming methods used for client name resolution lookups
Tnsnames Set to resolve a net service name through the tnsnames.ora file on the clientLdap Set to resolve a database service name, net service name, or net service alias through a directory server.ezconnect or hostname Select to enable clients to use a TCP/IP connect identifier, consisting of a host name and optional port and service namecds Set to resolve an Oracle database name in a Distributed Computing Environment (DCE) environment.Nis Set to resolve an Oracle database name in a Distributed Computing Environment (DCE) environment.
If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.
NONE for no authentication methods. A valid username and password can be used to access the database.
ALL for all authentication methods
NTS for Windows NT native authentication
6. Control file
Control file is a small binary file that records the physical structure of the database. The control file also includes:
What is oracle database ? Part VII
2. Password
If the DBA wants to startup a stopped oracle instance whose database dictionary is not accessible how oracle would authenticate the DBA?
With the Help of password file & parameter remote_login_passwordfile or using OS authentication
This will allow authentication of the DBA to happen outside of the database either using password file or through the operating system (sqlplus “/ as sysdba”) from oracle owner.
The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.
The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.
Password files creation:
What is oracle database ? Part VI
2. Password
3. tnsnames
4. Listener
5. Sqlnet
6. Control file
7. Data files
8. Temp file
9. Online Redo Log File
10. Archive Log
1. Parameter
Parameter is a mandatory file required to start database instance in nomount stage by reading instance characteristics
Parameter files hold DB parameter & its respective values
Below Query can be used to find parameter file being used during db startup
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM v$parameter WHERE name = 'spfile';
Parameter files located in default location $ORACLE_HOME/dbs (UNIX) & %ORACLE_HOME%database (Windows)
PFILE:
What is oracle database ? Part V
6. ARCn
ARCn is background process spawn in ARCHIVELOG DB mode, where n ranges from 0-9
ARCn copies online redo log files to a designated storage device after
- A log switch has occurred
- alter database archivelog current; is executed
- No more online redo log file is available & to be overwritten
An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. The alert file keeps a record of when LGWR starts a new ARCn process.
Dynamic LOG_ARCHIVE_MAX_PROCESSES can be set to max 10.
Starting from one ARCn process oracle automatically starts up more ARCn processes till LOG_ARCHIVE_MAX_PROCESSES when the database workload requires more.
Arch can be stopped and started dynamically with alter system archive log stop|start or using below commands (deprecated 10g onwards)
archive log list
archive log stop
archive log start
archive log next
archive log all
archive log n
Query v$archive_processes to find out how many arch processes are running
7. RECO
In a distributed database, the recoverer process (RECO) automatically resolves failures in distributed transactions.
The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When RECO re-establishes a connection between the databases, it automatically resolves all in-doubt transactions, removing from each database's pending transaction table any rows that correspond to the resolved transactions
8. RVWR
The Recovery Writer process is responsible for writing to the flashback logs in the flash recovery area.
These logs are required to restore the database to a previous point in time by using the "flashback" option for Oracle databases (Oracle 10g and later)
What is oracle database ? Part IV
1. PMON
PMON is an oracle background process called process monitor.
PMON responsible for releasing Locks & cleaning up cache post abrupt termination of user process holding resources
PMON also communicates workload advisory to listener in real application cluster environment which helps listener to redirect connection to least loaded instance
PMON restarts dispatcher & server process if it fails
PMON is responsible for service registration to listener which avoids manual configuration of service in listener.ora file by supplying information like database/instance name & its current/maximum load, dispatcher/server mode (shared/dedicated) info.
2. SMON
SMON is system monitor process.
SMON is responsible for performing instance recovery during startup of DB post abrupt shutdown/termination of instance.
SMON performs two operation while performing instance recovery i.e. Roll forward & Roll Back during which it refers UNDO & Online Redo log file
SMON coalesces adjacent free extents into large free extents in dictionary managed tablespace
It wakes up every 5 min to perform Housekeeping activity, killing SMON background process terminates instance
In RAC, SMON process of one instance can perform instance recovery of other instance that has failed
SMON cleans up temporary segments that are no longer in use and recovers dead transactions which were skipped during system failure/instance recovery because of file-read errors or offline file status. Such transactions are recovered by SMON when the tablespace or data file is brought back online.
What is oracle database ? Part III
3. Large Pool
Large pool was brought in to optimize shared pool by isolating heavy IO servers operation like Backup/restoration using parameter LARGE_POOL_SIZE.
Large Pool allocates memory required during disk IO server process operations of Backup & recovery.
Also parallel query buffers & Oracle XA memory allocation taken care by large pool
select pool, name, bytes from v$sgastat where pool like '%large_pool%' order by pool, name;
Java pool memory is used for all session-specific Java code and data within the JVM.
The Java Pool Advisor statistics provide information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate. The Java Pool Advisor is internally turned on when statistics_level is set to TYPICAL or higher. These statistics resets when the advisor is turned off.
select pool, name, bytes from v$sgastat where pool like '%java%' order by pool, name;
What is oracle database ? Part II
Will walk through the SGA memory components in detail below
1. Database Buffer
Database buffer plays significant role in SGA by keeping blocks/granules read from datafile & allowing user processes to read/write the block concurrently. DB cache value is driven by parameter DB_CACHE_SIZE
Database buffer/Cache manages the blocks by an internal algorithm using write & LRU (Least Recently Used) lists
Write List holds all the dirty blocks, means the blocks which are modified by user processes in memory & should be written to DB file back again to retain consistent image of a row/data.
LRU list contains two ends, MRU (Most Recently Used) & LRU (Least Recently Used).
LRU end holds dirty, pined, free buffer/blocks. Pinned buffer means blocks which are currently in use & free is free J
Dirty blocks held by LRU end of LRU list are being moved to write list.
What is Oracle Database ? Part I
An Oracle Database is a Relational Database management System used to store & retrieve the related information.
An Oracle Database server/instance consists of shared memory structure, background processes & storage which handles the functional requirement to manage concurrent & shared data access by users.
Oracle Database product has evolve though its 8i, 9i, 10g & 11g version
Overview:
Oracle database server is a part of multitier architecture includes Client Machine/Webserver, Middleware Application & Database Server.
Client Machine/Webserver:
ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted]
Issue:
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted
], [], [], [], [], [], [], [], [], [], [], []
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 2 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted
Cause:
The issue occurs since 11g as we are now using /dev/shm when using native PLSQL compilation.
ORA-06512: at "SYS.KUPW$WORKER ORA-39126: Worker unexpected fatal error in KUPW$WORKER
Issue: Unable to export due to ORA-06512 ORA-39126 errors
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [MARKER]
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
Solution:
i. export backup taken using below EXPDP command terminated with fatal error
expdp as sysdba" FULL=YES directory=EXP_DMP dumpfile=Mydb_FULL.dmp logfile=Mydb_full.log CONTENT=METADATA_ONLY
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [MARKER]
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
Solution:
i. export backup taken using below EXPDP command terminated with fatal error
expdp as sysdba" FULL=YES directory=EXP_DMP dumpfile=Mydb_FULL.dmp logfile=Mydb_full.log CONTENT=METADATA_ONLY
Steps to create active data guard in 11g and up-gradation to 12c using manual method - IV
10. Post upgrade steps
i. Set cluster_database to TRUE
Alter system set cluster_database=TRUE scope=spfile;
Srvctl stop instance –d TESTDBPRD –I NEWDBPRD1
Srvctl start instance –d TESTDBPRD –I NEWDBPRD1
Srvctl start instance –d TESTDBPRD –I NEWDBPRD2
ii. Update profile of DB services with new 12c home
Srvctl upgrade database –d TESTDBPRD –o /rdbms/app/oracle/12.1.0.2/db
iii. Re verify if all components are in valid state in dba_registry
A. Issues Faced
i. ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted
iii. Re verify if all components are in valid state in dba_registry
A. Issues Faced
i. ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted
Steps to create active data guard in 11g and up-gradation to 12c using manual method - III
8. Initiate 12c upgrade
i. Copy current pfile to 12c $ORACLE_HOME/dbs on both nodes
ii. Open db in upgrade in new 12c home
[oracle@testlabdb005a ~]$ . ./.bash_profile_12c
[oracle@testlabdb005a ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Thu JAN 11 08:53:36 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
idle> startup upgrade
ORACLE instance started.
Total System Global Area 4.8318E+10 bytes
Fixed Size 4510496 bytes
Variable Size 6174018784 bytes
Database Buffers 4.2010E+10 bytes
Redo Buffers 129703936 bytes
Database mounted.
Database opened.
iii. Run upgrade script
cd $ORACLE_HOME/rdbms/admin
Steps to create active data guard in 11g and up-gradation to 12c using manual method - II
6. Activating standby
Following steps can be taken to activate the standby and opening db instance in read write mode
SQL> select name,open_mode,database_role ,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR GUARD_S
------------ ------------ -------------------- -------- -------
TESTDBPRD MOUNTED PHYSICAL STANDBY NOT ALLOWED DISABLED NONE
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
SQL> alter database recover managed standby database cancel;
Database altered.