How to identify ORACLE_HOME of an Oracle instance process?

How to identify ORACLE_HOME of an Oracle instance process?
Use strings /proc//environ | grep ORACLE_HOME

For, instance,
ps -ef|grep pmon
oracle 9466     1  0  2016 ?        00:29:36 ora_pmon_DBName

$ strings /proc/9466/environ | grep ORACLE_HOME
ORACLE_HOME=/oracle/app/product ...

oracle preinstall

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol6.repo

oracle-rdbms-server-11gR2-preinstall
oracle-rdbms-server-12cR1-preinstall

echo "proxy=http://Proxy:Port/" >> /etc/yum.conf

No incremental levels for DB INCR in v$rman_backup_job_details

It is advised to use V$RMAN_BACKUP_JOB_DETAILS to identify status of backup jobs. But it is difficult to understand when it was taken full backup of Oracle DB due to in the INPUT_TYPE of the output you can find "DB INCR" value as for FULL Incremental level 0 backups as for Incremental level 1 backups.

You can find way to clarify incremental levels or to find out when was taken FULL backup with the V$BACKUP_SET_DETAILS view.

For instance,
select * from V$BACKUP_SET_DETAILS  where BACKUP_TYPE='I' and INCREMENTAL_LEVEL='0'

Ad hoc useful querries (SQL Server)

SELECT
        SERVERPROPERTY('MachineName') as Host,
        SERVERPROPERTY('InstanceName') as Instance,
        SUBSTRING(@@VERSION, 1,25) as Version,
        SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/
        SERVERPROPERTY('ProductVersion') AS 'Patch Level',
        SERVERPROPERTY('ProductLevel') as SP, /* RTM or SP1 etc*/
        Case SERVERPROPERTY('IsClustered')
        when 1     then 'CLUSTERED' else
                  'STANDALONE' end as ServerType

My MoSCoW education 2017

Must have:
1) Install VirtualBox - Jan
2) Install Oracle12c  - Jan
3) Clusterware & RAC - Jan-Feb
4) New Features Ora 12c - Jan-Feb
5) OEM Cloud Control - March
6) Performance labs - April
7) MSSQL AlwaysOn lab - May

Should have:
1) Postgresql install - Jan
2) RHEL 7 new features - Sept

Links:
1) Обучающие курсы, видео и учебники по Visio 2013

https://support.office.com/ru-ru/article/%D0%9E%D0%B1%D1%83%D1%87%D0%B0%D1%8E%D1%89%D0%B8%D0%B5-%D0%BA%D1%83%D1%80%D1%81%D1%8B-%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-%D0%B8-%D1%83%D1%87%D0%B5%D0%B1%D0%BD%D0%B8%D0%BA%D0%B8-%D0%BF%D0%BE-Visio-2013-93e93e94-0899-4530-aebb-c70fd39faee5

2) Updating your Database Management Skills to SQL Server 2014

 https://mva.microsoft.com/en-us/training-courses/updating-your-database-management-skills-to-sql-server-2014-8313?l=S3j693Yy_704984382  

If you change password of Oracle USER1 wrongly

Go to the previous backup or copy of database and select user's password hash

SQL> SELECT name, password  FROM sys.user$ WHERE name='USER1';

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
USER1
48F7196FF1A87215

On the problem DB change password by issue:

SQL> alter user USER1 identified by values '48F7196FF1A87215';

User altered.

yum provides */uuencode

I always sent to myself files with uuencode. And today I was surprised when I did not find it in one of the systems.
'yum list |grep uuencode' didn't show any package
Then I googled and found advise to install sharutils

 To understand it was enough to issue
 yum provides */uuencode
which found the package with uuencode

sharutils-4.7-6.1.el6.x86_64 : The GNU shar utilities for packaging and unpackaging shell archives
Repo        : rhel6-server-...
Matched from:
Filename    : /usr/bin/uuencode

After 'yum install sharutils' the 'which uuencode' is sucsessful
/usr/bin/uuencode

MSDN related links

Unlimited Dev & Test Enviroments with MSDN Subscriptions

https://blogs.msdn.microsoft.com/govdev/2012/04/18/unlimited-dev-test-enviroments-with-msdn-subscriptions/

MSDN Pricing https://www.visualstudio.com/products/how-to-buy-vs

About the SQL Server License Terms 

https://msdn.microsoft.com/en-us/library/ms143522(v=sql.120).aspx

Quite good paper on SQL Server 2012 with links at the end
https://www.directionsonmicrosoft.com/licensing/2013/06/licensing-sql-server-development-and-test



Licensing SQL Server Dev/Test Environments
http://www.sqlservercentral.com/blogs/nebraska-sql-from-dba_andy/2012/07/27/licensing-sql-server-devtest-environments/

Practices & Pitfalls of MSDN Subscription Licensing

ssh keys for Named credentials

ssh-keys credentials for OEM CC
Put ssh public keys in the home (authorized_keys) of user on OEM server and in home of user of Oracle DB server (authorized_keys)

TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

One of the ways to resolve the error "TNS-12514: TNS:listener does not currently know of service requested in connect descriptor" could be registering DB Service Name as following:

SQL> alter system set local_listener=DBServiceName scope=both;

System altered.

SQL> alter system register;

System altered.

SQL> exit

$ lsnrctl reload

Outlook reminders don't work propperly

I've just used article "How to make Outlook Calendar reminders stay on top in Windows 7" http://superuser.com/questions/251963/how-to-make-outlook-calendar-reminders-stay-on-top-in-windows-7?rq=1  to make Outlook reminders popped-up during working in other applications. Testing.

To identify most appropriated Oracle block size on given file system

To identify most appropriated Oracle block size on file system mounted to /oracle/TEST/ we tried the following tests:

oracle$ time dd if=/dev/zero of=/oracle/TEST/16K.test bs=16k count=16384
16384+0 records in
16384+0 records out
268435456 bytes (268 MB) copied, 2.64192 seconds, 102 MB/s

real    0m2.651s
user    0m0.017s
sys     0m0.223s
oracle$ time dd if=/dev/zero of=/oracle/TEST/8K.test bs=8k count=32768
32768+0 records in
32768+0 records out
268435456 bytes (268 MB) copied, 2.57524 seconds, 104 MB/s

real    0m2.578s
user    0m0.014s
sys     0m0.386s
oracle$ time dd if=/dev/zero of=/oracle/TEST/4K.test bs=4k count=65536
65536+0 records in
65536+0 records out
268435456 bytes (268 MB) copied, 2.62402 seconds, 102 MB/s

real    0m2.626s
user    0m0.005s
sys     0m0.379s

"alter system register" does not work

Today my new DB created from a template script was not registered in the listener. And c was not working. Denis advised me to add the local_listener parameter into database. When I checked this parameter it was in place. But its port was wrong - instead of 1521  it was another port.
After I corrected the port and issued the "alter system register" the database successfully registered in the listener.

Как Вам обучение в формате Training-on-Demand?

Недавно я прошел курс Oracle 12c New Features в формате Training-on-Demand.

Приемлемый ли формат?

Такой формат тренинга в случае, если дают возможность пройти тренинг из дома, то да, приемлемый. Даже более эффективный, чем у наших российских тренеров на месте.

Вообще это мое личное мнение, но попробую выразить свои ощущения. Более высокую эффективность вижу в том, что волей неволей получаешь весь материал, который предполагается по программе, т.к. есть возможность прокрутки, паузы с тем, чтобы подробнее ознакомиться с понятиями, которые ты плохо знаешь, из других источников или документации – на курсах в классе часто не все дается или по многим темам пробегаешь что называется «галопом по европам». В надежде, что дома повторишь и разберешься, ты уезжаешь, но как правило, дома нет времени и актуальность теряется и соответственно глубина полученных знаний на порядок ниже.

На видеокурсе у меня есть возможность потратить на понимание и изучение той или иной темы больше времени. Есть возможность копать в глубину, поставив на паузу видео.

Также преподаватель выполняет все лабораторные работы в видео с объяснением причинно-следственных связей и их иллюстрациями случаями из своего опыта или особенностями прежних версий, что в некоторых практических заданиях очень важно для понимания. На курсах в классе порой не успеваешь и часто выполнение лабораторных либо сводится к перепечатыванию задания из методички, либо вообще пропускается и т.о. эффективность таких заданий порой нулевая.

Единственное, мне кажется одной недели на лабораторные работы маловато. Ведь на видео доступ дается на срок до 3 месяцев. Поэтому хорошо бы если бы давали возможность выбрать до трех недельных окон к лабораторным.

'purge recyclebin' does not purge recyclebin

SQL> select count(*) from dba_recyclebin;

  COUNT(*)
----------
     10419

SQL> purge recyclebin;

Recyclebin purged.

SQL> select count(*) from dba_recyclebin;

  COUNT(*)
----------
     10419

How to delete all objects of a user?

How to delete all objects of a user?

After googling I stopped on "Script to Drop a User's Objects" offered on the Toad World
https://www.toadworld.com/platforms/oracle/w/wiki/5013.script-to-drop-a-user-s-objects

Merging of all PDBs of CDBs into Single CDB

1) Merge all PDBs of cdb1 into cdb2
2) Drop cdb1

[oracle@myorasrv ~]$ . oraenv
ORACLE_SID = [cbd1] ? cdb1

[oracle@myorasrv ~]$ sqlplus / as sysdba

SQL> select name,open_mode from v$pdbs;

NAME                   OPEN_MODE
------------------------------ ----------
PDB$SEED               READ ONLY
PDB1_1                   READ WRITE

SQL> alter pluggable database pdb1_1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1_1 unplug into 'xmlPDB1_1.xml';

Pluggable database altered.

SQL> col PDB_NAME for a20
SQL> select pdb_name,status from cdb_PDBS
  2  where pdb_name='PDB1_1';

PDB_NAME         STATUS
-------------------- ---------
PDB1_1             UNPLUGGED

SQL> drop pluggable database pdb1_1 keep datafiles;

Pluggable database dropped.

SQL> exit

[oracle@myorasrv CDB]$ . oraenv
ORACLE_SID = [cdb1] ? cdb2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 is /u01/app/oracle
[oracle@myorasrv CDB]$ sqlplus / as sysdba

SQL> !cat compat.sql 
set serveroutput on
DECLARE
  compat BOOLEAN := FALSE;
  BEGIN
  compat := DBMS_PDB.CHECK_PLUG_COMPATIBILITY( 
  pdb_descr_file => '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/xmlPDB1_1.xml', pdb_name => 'pdb1_1');
  if compat then
  DBMS_OUTPUT.PUT_LINE('Is pluggable compatible? YES');
  else DBMS_OUTPUT.PUT_LINE('Is pluggable compatible? NO');
  end if;
end;
/

SQL> @compat.sql
Is pluggable compatible? YES

PL/SQL procedure successfully completed.

SQL> create pluggable database pdb1_1 using 'xmlPDB1_1.xml' nocopy;

Pluggable database created.

SQL> alter pluggable database pdb1_1 open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                   OPEN_MODE
------------------------------ ----------
PDB$SEED               READ ONLY
PDB2_1                   READ ONLY
PDB2_2                   READ WRITE
PDB_ORCL2               READ WRITE
PDB1_1                   READ WRITE

SQL> exit

[oracle@myorasrv CDB]$ . oraenv
ORACLE_SID = [cdb2] ? cdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 is /u01/app/oracle
[oracle@myorasrv CDB]$ sqlplus / as sysdba

SQL> shutdown immediate
SQL> startup mount restrict;
SQL> drop database;

Database dropped.

Audit invalid passwords and locked accounts for last 30 days

set linesize 180

col OS_USERNAME for a20

col USERNAME for a15

col date_Time for a20

col USERHOST for a20

col error for a30

select to_char(TIMESTAMP,'dd-mon-yyyy hh24:mi') "Date_Time",RETURNCODE,

DECODE(RETURNCODE,'1017','Invalid username/password','28000','The account is locked') "Error",

OS_USERNAME,USERNAME,USERHOST from dba_audit_trail

where USERNAME='SPRO_DB'

and RETURNCODE in ('1017','28000')

and TIMESTAMP>SYSDATE-30

order by timestamp;

Cloning a Non-CDB into a CDB

Cloning a Non-CDB orcl2 into a CDB cdb2

1) First, it needs to switch source Non-CDB orcl2 DB to read only mode.

[oracle@myorasrv ~]$ . oraenv
ORACLE_SID = [cdb2] ? orcl2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 is /u01/app/oracle

[oracle@myorasrv ~]$ sqlplus / as sysdba

SQL> startup mount
SQL> alter database open read only;
SQL> exit

[oracle@myorasrv ~]$ mkdir /u01/app/oracle/oradata/cdb1/pdb_orcl2
[oracle@myorasrv ~]$

2) In the cdb2, create db link that allows a connection to remote non-CDB orcl2 DB

  [oracle@edbxr36p0 ~]$ . oraenv
ORACLE_SID = [orcl2] ? cdb2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 is /u01/app/oracle
[oracle@edbxr36p0 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 3 04:42:44 2016

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


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> CREATE DATABASE LINK link_orcl2
  2  CONNECT TO system IDENTIFIED BY oracle_4U
  3  USING 'orcl2';

Database link created.

3) Clone the source non-CDB DB into the CDB using the database link.

SQL> create pluggable database pdb_orcl2 from NON$CDB@link_orcl2
  2  create_file_dest='/u01/app/oracle/oradata/cdb1/pdb_orcl2';

Pluggable database created.

SQL> exit

4) Create net service name for pdb_prcl2 using netca

$ netca

4) Connect to pdb_orcl2 using the net service name to execute the
noncdb_to_pdb.sql before opening the PDB

[oracle@oramysrv ~]$ sqlplus sys@pdb_orcl2 as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
[oracle@oramysrv ~]$ sqlplus sys@pdb_orcl2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 4 03:39:14 2016

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

Enter password:

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
.....................
SQL> -- leave the PDB in the same state it was when we started
SQL> BEGIN
  2    execute immediate '&open_sql &restricted_state';
  3  EXCEPTION
  4    WHEN OTHERS THEN
  5    BEGIN
  6      IF (sqlcode <> -900) THEN
  7        RAISE;
  8      END IF;
  9    END;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL> alter pluggable database pdb_orcl2 open;

Pluggable database altered.
SQL> EXIT;

[oracle@myorasrv ~]$ sqlplus sys@pdb_orcl2 as sysdba

Enter password:

SQL> select count(empno) from scott.emp;

COUNT(EMPNO)
------------
      14
 

Cloning PDB within the same CDB

[oracle@myorasrv ~]$ . oraenv
ORACLE_SID = [cdb2] ? cdb2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 is /u01/app/oracle
[oracle@myorasrv ~]$ mkdir $ORACLE_BASE/oradata/cdb2/pdb2_2
[oracle@myorasrv ~]$ sqlplus / as sysdba

SQL> alter pluggable database pdb2_1 close;

Pluggable database altered.

SQL> alter pluggable database pdb2_1 open;

Pluggable database altered.

SQL> alter pluggable database pdb2_1 close;

Pluggable database altered.

SQL> alter pluggable database pdb2_1 open read only;

Pluggable database altered.

SQL> create pluggable database pdb2_2 from pdb2_1
  2  CREATE_FILE_DEST='/u01/app/oracle/oradata/cdb2/pdb2_2';

Pluggable database created.

SQL> select name,open_mode from v$pdbs;

NAME                   OPEN_MODE
------------------------------ ----------
PDB$SEED               READ ONLY
PDB2_1                   READ ONLY
PDB2_2                   MOUNTED

SQL> alter pluggable database pdb2_1 close;

Pluggable database altered.

SQL> alter pluggable database pdb2_1 open;

Pluggable database altered.

SQL> alter pluggable database pdb2_2 open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                   OPEN_MODE
------------------------------ ----------
PDB$SEED               READ ONLY
PDB2_1                   READ WRITE
PDB2_2                   READ WRITE

SQL>