Archive for the ‘Oracle’ Category

Enable Pluggable Database(PDB) Archivelog Mode

August 26th, 2019, posted in Oracle Queries
Share

Enabling archive log mode -12c pluggable database/Container database

Since the Redologs are created at container database level in 12c and not at pluggable database level.
(Enabling archivelog at pluggable database level is not possible). Archiving is done at CDB’s.

You can check archive log mode either by querying v$database or archive-log list :

 

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
CDB      READ WRITE           NOARCHIVELOG

(OR)

SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence        13

***************  ***************
To enable the Archive-log mode
***************  ***************immam_dba,dba immam,imam dba,dba imam,oracle clone issue,oracle database,oracle application,oracle clone issue,ora oracle

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size      2929352 bytes
Variable Size    541068600 bytes
Database Buffers   239075328 bytes
Redo Buffers      5455872 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;
Database altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
CDB      READ WRITE           ARCHIVELOG



SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     413
Next log sequence to archive   415
Current log sequence           415

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u03/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 27G

 

Share

Apache HTTP_Server – Failed to start a managed process after the maximum retry limit Log (HTTP_Server~1)

August 12th, 2019, posted in Oracle
Share

Oracle HTTP Server, residing on a Unix platform, fails to start using OPMN. For example, OPMN reports the generic error

 

opmnctl startall
opmnctl: starting opmn and all managed processes...
================================================================================
opmn id=oradb:6200
0 of 1 processes started.

ias-instance id=infra.oracle.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
HTTP_Server/HTTP_Server/HTTP_Server

Error
--> Process (pid=24042)
Failed to start a managed process after the maximum retry limit Log::
/opt/oracle/infra/opmn/logs/HTTP_Server~1

The HTTP_Server~1 OPMN log file just reports that the HTTP Server has been started, but there are actually no httpd processes present on the system.

There is also nothing written to the $ORACLE_HOME/apache/apache/logs directory even when HTTP Server logLevel is set to debug.

Furthermore there are no core / segmentation fault files created.

In order for the HTTP Server to listen on ports < 1024 e.g 80 and 443 the ownership and permissions of the Oracle HTTP Server binary – ‘.apachectl’ – have been changed as follows:

One possible work-around is to change .apachectl to belong to another group – such as the generic one users (rather than have the file belong to the oracle group – typically called ‘oinstall’) e.g

 

Solution:

chown root:users .apachectl
chmod 6750 .apachectl
Share

Without Editing tnsnames.ora file Creating a Database Link in Oracle

June 19th, 2019, posted in Oracle Queries
Share

Developers needed access to some objects from one schema to another using database link. To enable database link he tried to create entry in tnsnames.ora file but had a problem with insufficient permissions. As a developer he has limited privileges on Unix machines so he can’t edit and save tnsnames.ora file.

But there is solution for this little problem.
You can create functional database link without editing tnsnames.ora file.

 

Little Demo Case:

system@TEST11> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

5 rows selected.


system@TEST11> select * from dba_db_links;

1. no rows selected

Create database link testlink_db2 using full tns entry:

system@TEST11> create database link testlink_db2
connect to system identified by oracle
using
'(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=10.2.10.18)
(PORT=1525))
(CONNECT_DATA=
(SID=test10)))'
/

Database link created.

Now little check and cleanout:

system@TEST11> select * from v$version@testlink_db2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

5 rows selected.

-- cleanout
system@TEST11> drop database link testlink_db2;

Database link dropped.

 

From documentation:

http://download.oracle.com/docs/html/B13951_01/net.htm#i1153728

http:https://docs.oracle.com/cd/E18283_01/server.112/e17118/statements_5005.htm

server_name = (DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(PORT=port_number)
(HOST=host_name)
)
(CONNECT_DATA=(SERVICE_NAME=service_name)
)
)

where:

server_name is the name of an Oracle server that matches an entry in the RDB directory. An entry in the RDB directory can be added using the ADDRDBDIRE command.

TCP is the TCP protocol used for TCP/IP connections.

port_number is the port number of the Oracle Net listener. This is usually port number 1521.

host_name is the name that defines the system where the target Oracle server resides. This name must be in the local host definition on the AS/400 or in a name server on your network. The host name can also be entered as an IP address, for example, 161.14.10.12.

service_name is the service name of the Oracle server.

Share

Oracle 11g – MEMORY_MAX_TARGET and MEMORY_TARGET

May 22nd, 2019, posted in Oracle Queries
Share

Adjusting the memory_max_target based on available memory. 
This example is Linux x86-64.

If you can afford to set the memory_max_target higher then the memory_target this will give you room to grow the memory_target without restarting the database. 

SQL> show parameters memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 17920M

SQL> show parameters memory_max_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 17920M

System has 36GB physical RAM available.

SQL> !grep MemTotal /proc/meminfo
MemTotal: 36912956 kB

Server has now set aside 24GB for use with Oracle. Kernel shared memory parameter.

SQL>!df -h /dev/shm/

Filesystem Size Used Avail Use% Mounted on
tmpfs 24G 11G 14G 44% /dev/shm

Increase memory_max_target to 24GB.

SQL> ALTER SYSTEM SET memory_max_target = 24G SCOPE=SPFILE;
System altered.

Shutdown Oracle.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup Oracle.
SQL> startup
ORACLE instance started.

Total System Global Area 2.5655E+10 bytes
Fixed Size 2213776 bytes
Variable Size 2.0133E+10 bytes
Database Buffers 5368709120 bytes
Redo Buffers 151166976 bytes
Database mounted.
Database opened.

Update your pfile.

SQL> create pfile from spfile;
File created.

Verify the new settings. Max now 24GB and memory target is 17.9GB. We can now increase the memory_target if the need arises without shutting the database down.

SQL> sho parameters memory_max_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 24G

SQL> sho parameters memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 17920M
Share