Skip to content
Advertisements

Posts from the ‘Administration’ Category

ORA-19963 ORA-27041 OSD-04002

While doing restore controlfile to new host in windows server, getting below issue

ORA-19963: error getting logical block size of file F:\BACKUP\O1_MF_S_966071800_
F6C0G8CF_.BKP
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.

Solution :

Windows, you need to give full permission of directory to users which contents of backup files.

Advertisements

ORA-27102 OSD-00026 O/S-Error: (OS 1455)

I am trying to restore the database to new host, While doing the first step, I try to start the database,

SQL> startup nomount pfile=’F:\backup\init.ORA’;
ORA-32006: SEC_CASE_SENSITIVE_LOGON initialization parameter has been deprecated

ORA-00600: internal error code, arguments: [kmgs_component_init_5], [15], [], []
, [], [], [], [], [], [], [], []

 

Solution :

The below error message appeared in the alert log file.

ORA-27102: out of memory
OSD-00026: additional error information
O/S-Error: (OS 1455) The paging file is too small for this operation to complete.
Mon Jan 22 14:21:37 2018
Error: Failed to allocate SGA start_addr 00000007D0000000 size 134217728

It seems to be a Memory issue on the server. I have crosschecked the server RAM and init.ora file Memory target size.  The Server RAM size is lesser than memory target size. so I have adjusted the memory target size in init.ora file according to the Server RAM and tried to start the database as a nomount stage, its okay.

Or you need to increase RAM on the server if you need more.

Oracle 12c SE2 installation guide on OEL 7.4

Please find the document here

Oracle 12c SE2 installation guide on OEL 7

12c SQL loader express mode

In Oracle Database12c, SQL*Loader has a new feature called express mode that makes loading CSV files faster and easier. With express mode, there is no need to write a control file for most CSV files you load. Instead, you can load the CSV file with just a few parameters on the SQL*Loader command line.

Example :

SQL> create table dept(deptno number,deptname varchar2(20),deptlocation varchar2(20));

Table created.

SQL> desc dept;
Name Null? Type
—————————————– ——– —————————-
DEPTNO NUMBER
DEPTNAME VARCHAR2(20)
DEPTLOCATION VARCHAR2(20)

oracle:oradb]$ cat testload.dat
001,IT Dept,Riyadh
002,Accounting,Riyadh
003,HR Dept,Bahrain

 

[premiadb:oracle:testdb]$ sqlldr userid=testme data=testload.dat table=dept
Password:

SQL*Loader: Release 12.1.0.2.0 – Production on Mon Jan 15 09:28:42 2018

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

Express Mode Load, Table: DEPT
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO

Table DEPT:
3 Rows successfully loaded.

Check the log files:
dept.log
dept_%p.log_xt
for more information about the load.
[premiadb:oracle:testdb]$ sqlplus testme

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 15 09:28:58 2018

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

Enter password:
Last Successful login time: Mon Jan 15 2018 09:28:45 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from dept;

DEPTNO DEPTNAME DEPTLOCATION
———- ——————– ——————–
1 IT Dept Riyadh
2 Accounting Riyadh
3 HR Dept Bahrain

 

For more detail, Please refer

http://www.oracle.com/technetwork/database/enterprise-edition/learnmore/sqlldr-express-mode-wp-1991038.pdf

 

good trick for changing password securly in oracle

Good information,

http://oracle-help.com/oracle-rac/changing-password-securely-oracle/

 

 

ORA-01017: invalid username/password ora_dba group

C:\Users\mazar>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 7 14:15:59 2018

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

ERROR:
ORA-01017: invalid username/password; logon denied

 

Solution: added ora_dba group to user

C:\Windows\system32>net localgroup

C:\Windows\system32>net localgroup ora_dba mazar /add
The command completed successfully.

 

C:\Users\mazar>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jan 7 14:21:45 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL>

 

Trigger for open all pdb

create trigger open_all_pdbs
after startup on database
begin
execute immediate ‘alter pluggable database all open’;
end open_all_pdbs;
/

Drop database link using sys

Step 1:

select * from dba_db_links

Step 2:  create a procedure

Create or replace procedure Drop_DbLink(schemaName varchar2, dbLink varchar2 ) is
plsql varchar2(1000);
cur number;
uid number;
rc number;
begin
select
u.user_id into uid
from dba_users u
where u.username = schemaName;
plsql := ‘drop database link “‘||dbLink||'”‘;
cur := SYS.DBMS_SYS_SQL.open_cursor;
SYS.DBMS_SYS_SQL.parse_as_user(
c => cur,
statement => plsql,
language_flag => DBMS_SQL.native,
userID => uid
);
rc := SYS.DBMS_SYS_SQL.execute(cur);

SYS.DBMS_SYS_SQL.close_cursor(cur);
end;
/

 

Step 3: Drop database link using sys

 

exec Drop_DbLink(‘owner’,’dblinkname’);

ezyconnect error ORA-12504

[premiadb:oracle:oradb]$ sqlplus apps@”ebsuat.xxxx.com:1586/ebsuat”

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 11 11:04:08 2017

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

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Cause :

Syntax issue

Solution :

[premiadb:oracle:oradb]$ sqlplus apps@\”ebsuat.xxxx.com:1586/ebsuat\”

SQL*Plus: Release 12.1.0.2.0 Production on Mon Sep 11 11:04:30 2017

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

 

Enable DDL logging

Enable DDL Logging:

ENABLE_DDL_LOGGING enables or disables the writing of a subset of data definition language (DDL) statements to a DDL log.

SQL> show parameter enable_ddl_logging

NAME TYPE VALUE
———————————— ———– ——————————
enable_ddl_logging boolean FALSE

SQL> alter system set enable_ddl_logging=TRUE scope=both;

System altered.

SQL> show parameter enable_ddl_logging

NAME TYPE VALUE
———————————— ———– ——————————
enable_ddl_logging boolean TRUE

SQL> select value from v$diag_info where name=’ADR Home’;

VALUE
——————————————————————————–
/premia/app/12c/oracle/diag/rdbms/oradb/oradb

SQL>

SQL> select value from v$diag_info where name=’ADR Home’;

VALUE
——————————————————————————–
/premia/app/12c/oracle/diag/rdbms/oradb/oradb

SQL> conn test/test123
Connected.
SQL> create table invoicenum(invoice number);

Table created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

[premiadb:oracle:oradb]$ pwd
/premia/app/12c/oracle/diag/rdbms/oradb/oradb/log/ddl

[premiadb:oracle:oradb]$ cat log.xml

<msg time=’2017-02-21T15:25:47.163+03:00′ org_id=’oracle’ comp_id=’rdbms’
msg_id=’opiexe:4383:2946163730′ type=’UNKNOWN’ group=’diag_adl’
level=’16’ host_id=’premiadb’ host_addr=’10.1.4.66′>
<txt>create table invoicenum(invoice number)
</txt>
</msg>

Reference :

https://docs.oracle.com/database/121/REFRN/GUID-6FBA6147-D545-4E7D-94F0-A97EC1C721AE.htm#REFRN10302