cannot open database in NOARCHIVELOG

SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount quiet ORACLE instance started. Database mounted. SQL> alter database noarchivelog; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-00258: manual archiving in NOARCHIVELOG mode must identify log First time I see this. Let’s try to… Continue reading cannot open database in NOARCHIVELOG

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… Continue reading Select from cdb_* views

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… Continue reading Audit pluggable database

Privileges on a view

Granting too many privileges on a view could be disastrous. A view is often used as a security element; you grant access to only a subset of columns and rows to one user. Mostly only SELECT. If you want to grant update to only some rows, the security could be enhanced with the WITH CHECK… Continue reading Privileges on a view

in doubt transaction

Distributed transactions allows you to have multiple DML’s over multiple databases within a single transaction For instance, one local and one remote insert into t values(1); insert into t@db02 values(2); commit; If you lose connection to db02 and wants to commit, your database server may/does not know about the state of the remote transaction. The… Continue reading in doubt transaction

Published
Categorized as dba

How to migrate non-cdb to pdb

In case you are desesperately looking for an upgrade from non-cdb to pdb with two clicks, here is the answer from Mike : you can’t Nope – not via "upgrade". You can use Data Pump, Transportable Tablespaces or Full Transportable Export/Import as a direct option but you can't upgrade and plugin in one pass unfortunately… Continue reading How to migrate non-cdb to pdb

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_path PATH, status (directory_name) STATUS… Continue reading check invalid directories

Published
Categorized as dba Tagged

check invalid database link for the DBA

followup of check invalid database link 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 (4000);… Continue reading check invalid database link for the DBA

Published
Categorized as dba Tagged

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… Continue reading check invalid database link

Published
Categorized as dba Tagged

To shrink or to move

If you delete most of the rows in one table, you probably want to regain that space, to minimize IO and fragmentation. If you delete most of the rows in most of the tables, you probably also want to resize the datafile to reduce storage and backup space. In this case, you could move your… Continue reading To shrink or to move

Published
Categorized as 12cR2, dba Tagged

What is the instance name?

If your Oracle SID doesn’t match your instance name in init.ora, this is quite confusing. Check my previous post, what is sid in oracle In the instance_name column of the view v$instance, as well as in USERENV context, it matches the ORACLE_SID of the underlying operating system. SQL> var ORACLE_SID varchar2(9) SQL> set autoprint on… Continue reading What is the instance name?

Generate 11g password hash

An easy way to generate a value string from the ssl is to use openssl Let’s take a random salt of ABCDEFGHIJ. The length of 10 is important. The hexadecimal representation is -41-42-43-44-45-46-47-48-49-4A- $ echo “SafePassw0rDABCDEFGHIJ\c” | openssl dgst -sha1 (stdin)= 47cc4102144d6e479ef3d776ccd9e0d0158842bb With this hash, I can construct my value SQL> create user testuser identified… Continue reading Generate 11g password hash

Published
Categorized as dba, security

delete all data

How do you delete all data? The simplistic approach would be to truncate all tables SQL> select table_name from user_tables; TABLE_NAME ———- T1 SQL> truncate table t1; Table truncated. You cannot truncate if you have referential integrity constraints. SQL> truncate table t2; ORA-02266: unique/primary keys in table referenced by enabled foreign keys Ok, let’s disable… Continue reading delete all data

tracefile tim to readable date format

In trace file, the time is recorded, this could be used to measure time between two timestamps But how do you convert 31796862227375 to a human format? This is how I proceeded : SQL> oradebug SETMYPID Statement processed. SQL> oradebug tracefile_name /u01/log/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_32440740.trc SQL> alter session set nls_date_format=’YYYY-MM-DD_HH24:MI:SS’ sql_trace=true; Session altered. SQL> select ‘&_DATE’,’Start’ from dual;… Continue reading tracefile tim to readable date format

job_name cannot be null

exec dbms_scheduler.create_job(job_name=>null,job_type=>’PLSQL_BLOCK’,job_action=>’BEGIN NULL; END;’) ORA-27451: JOB_NAME cannot be NULL ORA-06512: at “SYS.DBMS_ISCHED”, line 146 ORA-06512: at “SYS.DBMS_SCHEDULER”, line 288 ORA-06512: at line 1 This sounds like a proper error message. A bit less obvious is the drop_job message SQL> exec dbms_scheduler.drop_job(job_name=>null) ORA-20001: comma-separated list invalid near ORA-06512: at “SYS.DBMS_UTILITY”, line 236 ORA-06512: at “SYS.DBMS_UTILITY”, line… Continue reading job_name cannot be null

Published
Categorized as dba Tagged

Irrecoverable part III : a fix

After part I:the problem, Part II: two reports here is part III. We have backed up archivelog during a full and deleted them before the full completed. RMAN> list backup of archivelog all; List of Backup Sets =================== BS Size Type Elapsed Completion — —— —- ——– ———- 15 4.00K DISK 00:00:00 13:31:08 BP Key:… Continue reading Irrecoverable part III : a fix

Irrecoverable full backup part II : reporting

After my post Can you restore from a full online backup ?, I needed to come up with a report. Assuming that each backup goes in a different directory, I just wrote two reports. Report gaps in v$backup_redolog (or rc_backup_redolog if you use the catalog) DIR FIRST_CHANGE# NEXT_CHANGE# ——- ————- ———— /bck01/ 284891 285140 /bck01/… Continue reading Irrecoverable full backup part II : reporting

Published
Categorized as dba, rman Tagged

Can you restore from a full online backup ?

The question is not HOW TO DO IT but WHETHER YOU CAN DO IT ! A typical backup script would contains something like BACKUP DATABASE PLUS ARCHIVELOG: backup database format ‘/u99/backup/DB01/20150518/full_0_%d_s%s_p%p’ plus archivelog format ‘/u99/backup/DB01/20150518/arc_%d_s%s_p%p’; Starting backup at 2015-05-18_18:27:55 current log archived input archived log thread=1 sequence=469 … piece handle= /u99/backup/DB01/20150518/arc_DB01_s86_p1 Finished backup at 2015-05-18_18:27:58… Continue reading Can you restore from a full online backup ?

bypass ora-20

When you really need to run one script, at all cost, an annoying error is ORA-00020: maximum number of processes (40) exceeded, which can even occurs as sysdba. Test case (21 is a not something to do in real life): SQL> alter system set processes=21 scope=spfile; System altered. SQL> startup force quiet ORACLE instance started.… Continue reading bypass ora-20

Published
Categorized as dba, sqlplus

switch user in Oracle

Almost a decade ago I wrote about su in sqlplus. This 10gR2 “new” feature allows delegation à la sudo. By checking the DBA_USERS in 12c I found PROXY_ONLY_CONNECT. According to Miguel Anjo, there is a secret syntax for allowing only the proxy user. SQL> ALTER USER app_user PROXY ONLY CONNECT; SQL> CONNECT app_user/xyz ERROR:ORA-28058: login… Continue reading switch user in Oracle

Unusable index

After table maintenance, like move or split partition, underlying indexes are marked as unusable. This boils down to segment reorganisation, not dictionary change. For instance : CREATE TABLE t(x NUMBER) PARTITION BY RANGE(x) (PARTITION p1 VALUES LESS THAN (MAXVALUE)); CREATE INDEX i ON t (x); INSERT INTO t VALUES (1); ALTER TABLE T SPLIT PARTITION… Continue reading Unusable index

last partition

if you really need to quickly find the latest partition per table, I have written this little gem WITH FUNCTION d (b BLOB, len number) RETURN DATE IS d DATE; BEGIN IF DBMS_LOB.SUBSTR (b, 1, 1) = hextoraw(’07’) and len=83 THEN DBMS_STATS.convert_raw_value (DBMS_LOB.SUBSTR (b, 12, 2), d); ELSE d := NULL; END IF; RETURN d;… Continue reading last partition

Do you have a partition in 2015

You need to check the high_value from dba_tab_partitions. Or you you could metadata. With metadata, it is not a long, it either clob or clob-xml. SELECT t.table_name, MAX ( TO_DATE ( REGEXP_SUBSTR ( EXTRACT ( (VALUE (x)), ‘HIBOUNDVAL’).getStringVal (), ‘ \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}’), ‘YYYY-MM-DD HH24:MI;:SS’)) high_value FROM user_tables t, TABLE ( XMLSEQUENCE ( EXTRACT ( xmltype… Continue reading Do you have a partition in 2015

poor man ActiveDirectory password checker

To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation. You probably were discouraged by the long long route to kerberos, where the 11.2.0.2 bugs are fixed in 11.2.0.4, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that… Continue reading poor man ActiveDirectory password checker