AnasAbdin
No title available
Discoholic 🪩
wallacepolsom
if i look back, i am lost
Show & Tell
pixel skylines
d e v o n
ellievsbear
DEAR READER
Stranger Things
"I'm Dorothy Gale from Kansas"
he wasn't even looking at me and he found me
TVSTRANGERTHINGS
I'd rather be in outer space 🛸
we're not kids anymore.
#extradirty
PUT YOUR BEARD IN MY MOUTH
🪼
⁂
seen from France
seen from Canada
seen from Germany
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from United States
seen from France
seen from China
seen from United States
seen from United States
seen from Malaysia
seen from Brazil
@laurentschneider
Oracle 18c/19c and ActiveDirectory
Oracle 18c/19c and ActiveDirectory
With Oracle 18c and even better in Oracle 19c, you can manage your Oracle database users in Active Directory. This was supposed to be a very nice new feature as many of us struggle with many thousand users spread over many versions, environments, platforms and even cloud or exadata.
Is this going to help you?
No 😛 Oracle provides support for Active Directory only on Windows. Client and databasem…
View On WordPress
Select from cdb_* views
Select from cdb_* views
There is no privileges strong enough for you to view all objects in all databases
Let’s try
as sys:
SQL> select con_id, count(*) from cdb_objects group by con_id; CON_ID COUNT(*) ---------- ---------- 1 22749 3 22721
as non-sys
SQL> create user c##u identified by ***; User created. SQL> grant create session, select any dictionary to c##u; Grant…
View On WordPress
changing container in plsql
changing container in plsql
One of the today’s challenge, since Oracle 12c deprecated non-cdb, is to make the dba scripts CDB-aware.
If you are lucky enough to have no 11g around, you can mostly replace DBA_* by CDB_*
OLD:
SQL> select count(*) from dba_users; COUNT(*) ---------- 121
NEW: non-cdb
SQL> select con_id, count(*) from cdb_users group by con_id; CON_ID COUNT(*) ------ ---------- 0 121
View On WordPress
on input and output file descriptors
on input and output file descriptors
Let’s start with some basics. The basics works as well on Unix, Linux and Windows. Later techniques only work on linux/unix
$ ls -l hosts -rw-r--r--. 1 root root 211 Oct 5 2015 hosts $ ls -l xxx ls: cannot access xxx: No such file or directory $ read x foo $
Outpout and error are displayed on screen and input is read from your keyboard
The output is kwown as…
View On WordPress
register database in Cloud Control with command line
register database in Cloud Control with command line
a common task of the DBA is to add databases in EM. If you do it 1000 times, you’ll get bored. I guess…
sure you could go to EM Cloud Management Pack, but maybe you use SQLPLUS + Create database + catalog/catproc.
Okay, there are some ways/hacks to do it with emcli/emctl, but let’s face it, there is a tool around the corner that does everything for you : DBCA
Wait, that GUI thing? Yes and no.…
View On WordPress
Audit pluggable database
In the old now-deprecated maybe-soon-desupported non-cdb infrastructure, AUDIT’ing was done right after connect / as sysdba.
In single-tenant (or multi-tenant), things get complicated.
Once again, the doc must be read at least twice 😉
If you issue an audit statement in the root, then the database performs auditing across the entire CDB, that is, in the root and all PDBs […] all common users are…
View On WordPress
19c
19c is a mini-release. Remember it is a new name for the second 12cR2 patchset, after 12.2.0.2/18c
https://mikedietrichde.com/2019/02/13/oracle-database-19-2-for-exadata-is-now-available-for-download/ was the first to mention it. By looking up in the doc I found
. Distinct listagg
. Desupport sqlplus product profile
. listener.log log rotation
Go to the doc to find more https://docs.oracle.com/en…
View On WordPress
Oracle 19c
2014/05/distinct-listagg I wrote : Too bad the DISTINCT keyword was not implemented
what a good surprise to see this working in Oracle 19 : 19C LISTAGG DISTINCT
View On WordPress
check invalid directories
To get the status of a directory, I wrote my own function, which uses DBMS_LOB.FILEEXISTS.
CREATE FUNCTION status (DIRECTORY_NAME VARCHAR2) RETURN VARCHAR2 IS BEGIN IF (DBMS_LOB.FILEEXISTS (BFILENAME (DIRECTORY_NAME, '.')) = 1) THEN RETURN 'VALID'; ELSE RETURN 'INVALID'; END IF; EXCEPTION WHEN OTHERS THEN RETURN SQLERRM; END; /
SELECT directory_name NAME, directory_…
View On WordPress
check invalid database link for the DBA
check invalid database link for the DBA
followup of http://laurentschneider.com/wordpress/2017/09/check-invalid-database-link.html If you need to check db link in another schema, you need to create code that run with that schema.
base on the example from yesterday, here is an extended version for the dba
CREATE FUNCTION dba_status (owner VARCHAR2, db_link VARCHAR2) RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION; status VARCHAR2…
View On WordPress
check invalid database link
check invalid database link
If one database link is invalid, you cannot select through it SQL> select * from dual@z; select * from dual@z * ERROR at line 1: ORA-02019: connection description for remote database not found
However, there is no STATUS command in your user_db_links table that you could use. For this purpose, write a function. create function status(db_link varchar2) return varchar2 is x number;
View On WordPress
list targets
https://twitter.com/laurentsch/status/910108527439548417
As correctly pointed out by dhoogfr , the proper way to list targets on an agent is to use list target $ emctl config agent listtargets Oracle Enterprise Manager Cloud Control 13c Release 2 Copyright (c) 1996, 2016 Oracle Corporation. All rights reserved. [srv01.example.com, host] [srv01.example.com:3872, oracle_emd] [agent13c2_2_srv01.exam…
View On WordPress
sid and pluggable
I wrote about SID there. (CONNECT_DATA=(SID=DB01)) is undocumented since Oracle 11gR1 and breaks things with multi-tenant / single-tenant.
You create a container CDB01 and a pluggable DB01, you can connect with
sqlplus "scott/tiger@ (description=(address= (host=srv01)(port=1521)(protocol=tcp)) (connect_data=(service_name=DB01.example.com)))"
But one of your application has hardcoded…
View On WordPress
default listener port
Long time ago, Maxime Yuen registered 1521 for nCube License Manager.
By googling I found : Ellison cleans house at nCube, and since them 1521 has been used as a default port for Oracle. Still, you’ll see nCube in IANA.ORG service names port numbers and in /etc/services the nCube name. I don’t know which one came first, Oracle using 1521 or Larry investing in nCube, but I am pretty sure it’s…
View On WordPress
lock sys
In the old days, locking sys had not much effect. SQL> alter user sys identified by *** account lock; User altered. SQL> select account_status from dba_users where username='SYS'; ACCOUNT_STATUS -------------------------------- LOCKED SQL> conn / as sysdba Connected. SQL> conn sys/** as sysdba Connected. SQL> conn sys/***@db01 as sysdba Connected.
Well, in the very-old days, Oracle7, or with…
View On WordPress
Single-Tenant over bequeath connections
Single-Tenant over bequeath connections
If you follow Oracle recommendation to use SingleTenant CDB instead of Non-CDB, then a lot of things will break.
I won’t go into details, but basically, in this new architecture, you have one core container, called CDB$ROOT, which is the default container where you connect to if you connect locally sqlplus / as sysdba SQL> select cdb from v$database; CDB --- YES SQL> select…
View On WordPress
Legacy users get ORA-01017 in 12.2
Legacy users get ORA-01017 in 12.2
The default case insensitive string disappeared in 12cR2, let’s call it the 10G string in this post, but it was the same since Oracle 7 at least. It was introduced in V5 or V6 to replace clear-text passwords.
What’s happening then with my ultra-old-accounts?
You could well set a new password (or the same password again) to each account to be migrated to 11g passwords before moving to 12cR2.
If…
View On WordPress