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>

Creating a PDB from SEED

[oracle@myorasrv rdbms]$ . 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 rdbms]$ mkdir $ORACLE_BASE/oradata/cdb2/pdb2_1
[oracle@myorasrv rdbms]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 1 05:41:41 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 pluggable database pdb2_1 admin user pdb2_1_admin
  2  identified by oracle_4U roles=(connect)
  3  create_file_dest='/u01/app/oracle/oradata/cdb2/pdb2_1';

Pluggable database created.

SQL> col name for a10
SQL> select con_id,name,open_mode,dbid,con_uid from v$pdbs;

    CON_ID NAME       OPEN_MODE        DBID    CON_UID
---------- ---------- ---------- ---------- ----------
     2 PDB$SEED   READ ONLY   159907668  159907668
     3 PDB2_1     MOUNTED     1547403331 1547403331

SQL> alter pluggable database pdb2_1 open;

Pluggable database altered.

SQL> exit
 
[oracle@myorasrv ~]$ netca

Oracle Net Services Configuration:
..............................
[oracle@myorasrv ~]$ sqlplus sys@pdb2_1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 1 05:53:36 2016

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

Enter password:

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> !lsnrctl status
 
.....
 Service pdb2_1 has 1 instance(s).
.... 

SQL> connect sys\@localhost:1521\/pdb2_1 as sysdba
Enter password:
Connected.
SQL> connect pdb2_1_admin@PDB2_1
Enter password:
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB2_1
======================================


SQL> !ls -R $ORACLE_BASE/oradata/cdb2/pdb2_1/*
/u01/app/oracle/oradata/cdb2/pdb2_1/CDB2:
2AAFE0C2280B728AE0533834960ABEB9

/u01/app/oracle/oradata/cdb2/pdb2_1/CDB2/2AAFE0C2280B728AE0533834960ABEB9:
datafile

/u01/app/oracle/oradata/cdb2/pdb2_1/CDB2/2AAFE0C2280B728AE0533834960ABEB9/datafile:
o1_mf_sysaux_cbxw5xtk_.dbf  o1_mf_system_cbxw5xtd_.dbf    o1_mf_temp_cbxw5xtl_.dbf

SQL> !ls -l $ORACLE_BASE/oradata/cdb2/pdb2_1/*
total 4
drwxr-x--- 3 oracle oinstall 4096 Feb  1 05:44 2AAFE0C2280B728AE0533834960ABEB9
 

Enable Unified Audit in Oracle 12c

First, stop all oracle processes (instances, listeners)
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

To monitor recent RMAN backup and recovery

SQL> select dbusername,rman_operation
  2  from unified_audit_trail
  3  where rman_operation is not null;

DBUSERNAME               RMAN_OPERATION
------------------------------ --------------------
SYS                   Backup
SYS                   Restore
SYS                   Recover
SYS                   Backup
SYS                   Restore
SYS                   Recover

6 rows selected.
 

Falshback from Common User Drop

$ export NLS_DATE_FORMAT='DD-MM-YYYY HH:MI:SS'
$ sqlplus / as sysdba
SQL> select flashback_on from v$DATABASE;

FLASHBACK_ON
------------------------
NO

SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

2)
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;

3) DROP USER
SQL> col username for a20
SQL> select USERNAME, COMMON, CON_ID from cdb_users
where username='C##_USER';

 USERNAME         COM     CON_ID
-------------------- --- ----------
C##_USER         YES      1
C##_USER         YES      6
C##_USER         YES      5
C##_USER         YES      4
C##_USER         YES      3

Log the SCN before dropping
SQL> select timestamp_to_scn(current_timestamp) from v$database;

TIMESTAMP_TO_SCN(CURRENT_TIMESTAMP)
-----------------------------------
                2812476


 SQL> drop user C##_USER cascade;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> FLASHBACK DATABASE TO SCN 2812476;
SQL > ALTER DATABASE OPEN READ ONLY;
SQL> 
SQL> col username for a20
SQL> select USERNAME, COMMON, CON_ID from cdb_users
  2  where username='C##_USER';

USERNAME         COM     CON_ID
-------------------- --- ----------
C##_USER         YES      1

 SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ ONLY;

SQL> select USERNAME, COMMON, CON_ID from cdb_users
where username='C##_USER';

USERNAME         COM     CON_ID
-------------------- --- ----------
C##_USER         YES      3
C##_USER         YES      5
C##_USER         YES      1
C##_USER         YES      4
C##_USER         YES      6


 SQL> SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN 2812476;
Flashback complete.
SQL > ALTER DATABASE OPEN RESETLOGS;

3) Check user
SQL> conn C##_USER
Enter password:
Connected.
SQL> conn c##_USER@PDB2
Enter password:
Connected.
SQL> connect C##_USER@PDB2_2
Enter password:
Connected.


4) Backup  DB
$ rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG delete all input;
...
RMAN> exit;

PITR on non-essential tablespace

PITR on non-essential tablespace

Lets' first log scn before issue.

SQL> select timestamp_to_scn(sysdate) from v$database;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
          2809058

SQL> delete from local_test.tab_test where rownum < 3;

2 rows deleted.

SQL> commit
  2  ;

Commit complete.

SQL> select * from local_test.tab_test;

     C
----------
     3
     4

[oracle@edbtr43p0 trace]$ rman target /

RMAN> alter pluggable database pdb2_2 close;

using target database control file instead of recovery catalog
Statement processed

RMAN> run {
2> set until scn = 2809058;
3> restore pluggable database pdb2_2;
4> recover pluggable database pdb2_2 auxiliary
5> destination='/u01/app/oracle/oradata';
6> alter pluggable database pdb2_2 open resetlogs;
7> }

RMAN> exit


$ sqlplus sys@pdb2_2 as sysdba
SQL> select * from local_test.tab_test;

     C
----------
     1
     2
     3
     4










RMAN> delete obsolete;
RMAN> backup database plus archivelog delete all input;

RMAN Recovery from Non-Essential Datafile loss, # SYSAUX 

 RMAN> list failure
2> ;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
362        HIGH     OPEN      23-JAN-16     One or more non-system datafiles are missing


RMAN> list failure detail;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
362        HIGH     OPEN      23-JAN-16     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 362
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  985        HIGH     OPEN      23-JAN-16     Datafile 3: '/u01/app/oracle/oradata/cdb2/sysaux01.dbf' is missing
    Impact: Some objects in tablespace SYSAUX might be unavailable


RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
362        HIGH     OPEN      23-JAN-16     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 362
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  985        HIGH     OPEN      23-JAN-16     Datafile 3: '/u01/app/oracle/oradata/cdb2/sysaux01.dbf' is missing
    Impact: Some objects in tablespace SYSAUX might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=365 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/cdb2/sysaux01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 3 
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/cdb2/cdb2/hm/reco_3475313480.hm


RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/cdb2/cdb2/hm/reco_3475313480.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 3 offline';
   restore ( datafile 3 );
   recover datafile 3;
   sql 'alter database datafile 3 online';


RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/cdb2/cdb2/hm/reco_3475313480.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 3 offline';
   restore ( datafile 3 );
   recover datafile 3;
   sql 'alter database datafile 3 online';

Do you really want to execute the above repair (enter YES or NO)? YES
...........................
 sql statement: alter database datafile 3 online
repair failure complete

RMAN> exit

Learning plan from workshop on Oracle Real Application Cluster

1 Installation and setting of Oracle VirtualBox, creation of RAC1 Virtual Machine (VM)

2 Installation of Oracle linux 64 bits operating system
3 Setting network cards and the /etc/hosts file
4 Tune RAC1 with yum: Kernel parameters, packages dependencies, user and groups
5 Clone RAC1 to RAC2, the second node of our Oracle RAC cluster
6 Creation of the shared disks from virtual box and present them to both racs
7 Creation of ASM disk for subsequent creation of diskgroups
8 Installation of the binaries GI 12c
Set the ssh connectivity between node 1 and node 2
Run runcluvfy to get all the check results
At this step if all is good, you have your first cluster 12c !
9 Installation of GI 12c silent mode
10 Installing Oracle 11gR2 binaries (graphic and silent)
11 Creating Database 11gR2 data (graphic and silent)
12 Learn all the command available to manage your cluster
With this course, you have in your hand a powerful environment, created with the last version of Oracle products and for a very low cost. You can simulate a production environment at home with your personal computer.
What are you going to get from this workshop?
Real Time Oracle Practice.
Install and set from scratch a 2 node cluster with Oracle 12c technology
Know all the important point to set a cluster with Oracle
Install and set Oracle virtual box for RAC
Install and set linux 6.6 64bits for RAC
Create and present shared disks to simulate you have a storage array
Install, configure and use oracle asmlib libraries to map the shared disks to ASM disks
Know and practice with the most important cluster command lines