AMM RAC database and switch RAC database to ASMM

Calculate the current memory usage for our AMM RAC database and switch RAC database to ASMM

SQL> select sum(bytes/1024/1024) Current_SGA_SIZE_in_MB from v$sgastat;
CURRENT_SGA_SIZE_IN_MB
----------------------
        904.844437
SQL> select sum(bytes/1024/1024) MAX_SGA_SIZE_in_MB from  v$sgainfo    where name = 'Maximum SGA Size';
MAX_SGA_SIZE_IN_MB
------------------
    1592.84766
SQL> show parameter memory_max_target;
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target             big integer 1600M
SQL> select (value/1024/1024) Current_PGA_IN_USE_in_MB from v$pgastat where name = 'total PGA inuse';
CURRENT_PGA_IN_USE_IN_MB
------------------------
          122.085938
SQL> select (value/1024/1024) MAX_PGA_ALLOCATED_in_MB from v$pgastat where name = 'maximum PGA allocated';
MAX_PGA_ALLOCATED_IN_MB
-----------------------
         167.658203
SQL> select (value/1024/1024) PGA_TARGET_in_MB    from v$pgastat where name = 'aggregate PGA target parameter';
PGA_TARGET_IN_MB
----------------
         480
Our current AMM uses the following memory
 - memory reserved  for PGA/SGA: 1600 MByte 
 - current PGA size 120 MB
 - current SGA size 904 MB
 - free memory for future PGA/SGA usage: ~ 600 MByte

For switching ASMM this can be translated into 
  SGA_MAX_SIZE             : 1400 MByte
  SGA_TARGET               : 1000 MByte
  PGA_AGGREGATE_TARGET     :  480 Mbyte

For further tuning check : V$PGA_TARGET_ADVICE

Execute the  following commands.
Disable AMM
  SQL> alter system reset memory_max_target scope=spfile  sid='*';
  SQL> alter system reset memory_target  scope=spfile  sid='*';

Enable ASMM
  SQL> alter system set SGA_MAX_SIZE=1400m scope=spfile  sid='*';
  SQL> alter system set SGA_TARGET=1000m scope=spfile  sid='*'; 
  SQL> alter system set PGA_AGGREGATE_TARGET=480m scope=spfile  sid='*';  

Reboot database and verify that we have switched from AMM to ASMM
SQL> show parameter memory
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
memory_max_target             big integer 0
memory_target                 big integer 0
--> AMM disabled 

SQL> show parameter sga
NAME                     TYPE          VALUE
------------------------ ----------- ------------------------------
sga_max_size             big integer 1408M
sga_target               big integer 912M

SQL> show parameter pga
NAME                     TYPE     VALUE
------------------------ ----------- ------------------------------
pga_aggregate_target     big integer 480M

--> ASMM enabled !

 Review impact on OS resources after switchging for AMM to ASMM

The switch from AMM to ASMM frees space in /dev/shm but allocates shared memomry for the SGA
$ ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status                             
0x00000000 3145746    oracle     640        16777216   41                      
0x00000000 3178515    oracle     640        1459617792 41                      
0xe1106fe8 3211284    oracle     640        2097152    41                      

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 2.0G  200M  1.9G  10% /dev/shm

Moving the LOB from one Tablespace to another Tablespace

As a part of changing the Bigfile Tablespace to normal Tablespace, :

Post activities considerations:

Check the size of LOB in the Tablespace which we are going to move.

check the count of objects using dba_lobs, dba_indexes, dba_tables with corresponding Tablespace.

Create the necessary tablespace and datafiles with sufficient space.

Change the default tablespace if needed to the new one.

Give quota to the user as unlimited for the created tablespace.

use the below script and generate the .sql file for the movement.

Moving LOB:

SQL> spool /home/oracle/movelob.sql

SET HEADING OFF

SET pagesize 200

SET linesize 200

select 'ALTER TABLE <owner>.'||TABLE_NAME||' MOVE LOB('||COLUMN_NAME||') STORE AS (TABLESPACE <Tablespace_name>) parallel 5 nologging;' from dba_lobs where TABLESPACE_NAME='<Tablespace_name>';

Note: The above query will include all the LOB,LOBSEGMENT,LOBINDEXES

Moving Table:

SQL> spool /home/oracle/moveTables.sql

SET HEADING OFF

SET PAGESIZE 200

SET LINESIZE 200

select ' ALTER TABLE <owner>.'||TABLE_NAME||' MOVE TABLESPACE <Tablespace_name>) parallel 5 nologging;' from dba_tables where owner='<owner name>';

Moving Index:

SQL> spool /home/oracle/moveIndex.sql

SET HEADING OFF

SET long 9999

SET linesize 200

select 'alter index <owner>.'||index_name||' from dba_indexes 'rebuild tablespace <Tablespace_name>)   online parallel 3 nologging;' where owner='<owner>.';

Run the queries and generate the sql scripts 

Make sure that there is no unwanted space in the sql script.

Move the Huge size LOB separately and the corresponding tables and indexes to make sure that huge volumes moves first.

Next move the Remaining LOB then Tables and Index correspondingly using the scripts.

Then check with the old tablespace if there are any objects left, if so move those to the new tablespace

Check the counts with previously taken counts if every this is fine ,then if needed drop the tablespace with the datafiles.

Have Your LOB in new tablespace :-)

Oracle 12c Multitenant Backup and Recovery

Single RMAN backup database command will backup the root container database as well as all the pluggable databases.

[oracle@db12c ~]$ rman  target /

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 10:05:37 2016

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

connected to target database: DB12C (DBID=1373556589)

RMAN> backup database FORMAT '/rman_bak/%d_%T_%s_%p.BAK';

Starting backup at 06-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=388 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/db12c/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/db12c/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db12c/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/db12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_2_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_3_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/db12c/prodb2/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/db12c/prodb2/system01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/db12c/prodb2/prodb2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_4_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/db12c/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db12c/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_5_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 06-SEP-16

Starting Control File and SPFILE Autobackup at 06-SEP-16
piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-1373556589-20160906-00 comment=NONE
Finished Control File and SPFILE Autobackup at 06-SEP-16

Backups can also be performed at the pluggable database level.

RMAN> backup pluggable database prodb1 FORMAT '/rman_bak/%d_%T_%s_%p.BAK';

Starting backup at 06-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_11_1.BAK tag=TAG20160906T102038 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 06-SEP-16

Starting Control File and SPFILE Autobackup at 06-SEP-16
piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-1373556589-20160906-03 comment=NONE
Finished Control File and SPFILE Autobackup at 06-SEP-16

RMAN> 

RMAN> backup pluggable database prodb2 FORMAT '/rman_bak/%d_%T_%s_%p.BAK';

Starting backup at 06-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/db12c/prodb2/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/db12c/prodb2/system01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/db12c/prodb2/prodb2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_13_1.BAK tag=TAG20160906T102159 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 06-SEP-16

Starting Control File and SPFILE Autobackup at 06-SEP-16
piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-1373556589-20160906-04 comment=NONE
Finished Control File and SPFILE Autobackup at 06-SEP-16

We can also use RMAN to connect to an individual pluggable database instead of the container database.

RMAN> list backup of database;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
16      Full    679.84M    DISK        00:00:07     06-SEP-16    
        BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: TAG20160906T102707
        Piece Name: /rman_bak/DB12C_20160906_16_1.BAK
  List of Datafiles in backup set 16
  File LV Type Ckp SCN    Ckp Time  Name
  —- — —- ———- ——— —-
  8       Full 2508046    06-SEP-16 /u01/app/oracle/oradata/db12c/prodb1/system01.dbf
  9       Full 2508046    06-SEP-16 /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
  10      Full 2508046    06-SEP-16 /u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf

RMAN> 

RMAN> list backup by file;

List of Datafile Backups
========================

File Key     TY LV S Ckp SCN    Ckp Time  #Pieces #Copies Compressed Tag
—- ——- –  – – ———- ——— ——- ——- ———- —
8    16      B  F  A 2508046    06-SEP-16 1       1       NO         TAG20160906T102707
9    16      B  F  A 2508046    06-SEP-16 1       1       NO         TAG20160906T102707
10   16      B  F  A 2508046    06-SEP-16 1       1       NO         TAG20160906T102707

Loss of Tempfile at pluggable database level.Temp file is automatically created when the pluggable database is closed and opened.

SQL> select name from v$tempfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/temp01.dbf
/u01/app/oracle/oradata/db12c/pdbseed/pdbseed_temp012016-08-10_05-31-03-PM.dbf
/u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf
/u01/app/oracle/oradata/db12c/prodb2/temp012016-08-10_05-31-03-PM.dbf

SQL> alter session set container=prodb1;

Session altered.

SQL> select name from v$tempfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf

SQL> !rm -rf /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf 

SQL> !ls -l /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf
ls: cannot access /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf: No such file or directory

SQL> alter pluggable database prodb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database prodb1 open read write;

Pluggable database altered.

SQL> select name from v$tempfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf

SQL> !ls -l /u01/app/oracle/oradata/db12c/prodb1/temp*
-rw-r—– 1 oracle oinstall 20979712 Sep  6 10:37 /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         3 PRODB1                         READ WRITE NO

Loss of Non-System data file at pluggable database level.

[oracle@db12c ~]$ sqlplus sys@prodb1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 6 10:46:17 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> select name from v$datafile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/undotbs01.dbf
/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf

SQL> !rm -rf /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf

SQL> !ls -l /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
ls: cannot access /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf: No such file or directory

SQL> alter tablespace sysaux offline;
alter tablespace sysaux offline
*
ERROR at line 1:
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown immediate
Pluggable Database closed.
SQL> startup
ORA-01113: file 10 needs media recovery
ORA-01110: data file 10:
'/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf'

SQL> conn / as sysdba
Connected.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PRODB1                         MOUNTED
         4 PRODB2                         READ WRITE NO

[oracle@db12c ~]$ rman target sys/oracle@prodb1

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 11:09:14 2016

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

connected to target database: DB12C (DBID=1373556589, not open)

RMAN> select name from v$datafile;

using target database control file instead of recovery catalog

NAME                                                                          
——————————————————————————–

/u01/app/oracle/oradata/db12c/undotbs01.dbf
 

/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
 

/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
 

/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
 

RMAN> restore tablespace sysaux;

Starting restore at 06-SEP-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=773 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /rman_bak/DB12C_20160906_16_1.BAK
channel ORA_DISK_1: piece handle=/rman_bak/DB12C_20160906_16_1.BAK tag=TAG20160906T102707
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 06-SEP-16

RMAN> recover tablespace sysaux;

Starting recover at 06-SEP-16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 06-SEP-16

[oracle@db12c ~]$ sqlplus sys/oracle@prodb1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 6 11:12:03 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> recover datafile 10;
Media recovery complete.

SQL> recover datafile 8;
Media recovery complete.
SQL> alter pluggable database prodb1 open read write;

Pluggable database altered.

SQL> alter tablespace sysaux online;

Tablespace altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         3 PRODB1                         READ WRITE NO

Loss of SYSTEM tablespace datafile at pluggable database level.
The entire container database has to be shut down and mounted and pluggable database recovered.

SQL> !rm -rf /u01/app/oracle/oradata/db12c/prodb1/system01.dbf

SQL> !ls -l  /u01/app/oracle/oradata/db12c/prodb1/system01.dbf
ls: cannot access /u01/app/oracle/oradata/db12c/prodb1/system01.dbf: No such file or directory

SQL> alter session set container=prodb1;

Session altered.

SQL> shutdown abort
Pluggable Database closed.

To recover the pluggable database we need to connect to the container database, shutdown the container database (this will shutdown all other pluggable databases) , mount the container database and then recover the pluggable database.

[oracle@db12c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 6 13:26:26 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> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PRODB1                         MOUNTED
         4 PRODB2                         READ WRITE NO
        
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 3238002688 bytes
Fixed Size                  2929600 bytes
Variable Size            2046823488 bytes
Database Buffers         1174405120 bytes
Redo Buffers               13844480 bytes
Database mounted.

[oracle@db12c ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 13:29:19 2016

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

connected to target database: DB12C (DBID=1373556589, not open)

RMAN> restore tablespace prodb1:system;

Starting restore at 06-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1144 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/db12c/prodb1/system01.dbf
channel ORA_DISK_1: reading from backup piece /rman_bak/DB12C_20160906_21_1.BAK
channel ORA_DISK_1: piece handle=/rman_bak/DB12C_20160906_21_1.BAK tag=TAG20160906T111604
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 06-SEP-16

RMAN> recover tablespace prodb1:system;

Starting recover at 06-SEP-16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 06-SEP-16

RMAN> alter database open;

Statement processed

RMAN> recover datafile 10;

Starting recover at 06-SEP-16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 06-SEP-16

RMAN> recover datafile 9;

Starting recover at 06-SEP-16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 06-SEP-16

RMAN> alter pluggable database all open read write;

Statement processed

Loss of SYSTEM data file at the Container database level.
Note:If we lose the container database SYSTEM datafile we cannot connect to any of the pluggable databases as well.
We have to shutdown abort the container database and then mount the container database and perform offline recovery of the SYSTEM tablespace.

SQL> !rm -rf /u01/app/oracle/oradata/db12c/system01.dbf

SQL> select count(*) from dba_objects;
select count(*) from dba_objects
                     *
ERROR at line 1:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/db12c/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> alter session set container=prodb1;

Session altered.

SQL> alter session set container=prodb2;

Session altered.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PRODB1                         READ WRITE NO
         4 PRODB2                         READ WRITE NO
SQL> alter system flush buffer_cache;

System altered.

SQL> shutdown abort
ORACLE instance shut down.

[oracle@db12c ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 13:59:16 2016

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area    3238002688 bytes

Fixed Size                     2929600 bytes
Variable Size               2046823488 bytes
Database Buffers            1174405120 bytes
Redo Buffers                  13844480 bytes

RMAN> restore tablespace system;

Starting restore at 06-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=769 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/db12c/system01.dbf
channel ORA_DISK_1: reading from backup piece /rman_bak/DB12C_20160906_20_1.BAK
channel ORA_DISK_1: piece handle=/rman_bak/DB12C_20160906_20_1.BAK tag=TAG20160906T111604
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 06-SEP-16

RMAN> recover tablespace system;

Starting recover at 06-SEP-16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 06-SEP-16

RMAN> alter database open;

Statement processed

RMAN> alter pluggable database all open read write;

Statement processed

How to find the list of unusable index on the full database:-

set pagesize 9000
set linesize 2000
set long 20000

select dbms_metadata.get_ddl('INDEX', index_name, owner)
from all_indexes
where owner in(SELECT OWNER from (select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE')))
AND INDEX_NAME IN(SELECT INDEX_NAME from (select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE')));

===============

Schema Wise

set pagesize 232
set linesize 232
set feedback off
set echo off
set verify off
select OWNER,INDEX_NAME,INDEX_TYPE,PARTITION_NAME,SUBPARTITION_NAME, STATUS from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE') where OWNER='&schema_name';
spool off


===============
Generating the unusable command:

[oracle@xxxx swarn]$ more 2.sql
spool /u01/oracle/swarn/unusable_cmd.sql
set pagesize 232
set linesize 232
set feedback off
set echo off
set verify off
set heading off
select 'alter index '||OWNER||'.'||index_name||' unusable; 'from (select OWNER, INDEX_NAME from  (select OWNER,INDEX_NAME,INDEX_TYPE,NULL AS PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_indexes where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,PARTITION_NAME,NULL AS SUBPARTITION_NAME, STATUS from dba_ind_PARTITIONS where STATUS='UNUSABLE'
UNION ALL
select INDEX_OWNER,INDEX_NAME,NULL as INDEX_TYPE,NULL as PARTITION_NAME,SUBPARTITION_NAME,STATUS from dba_ind_SUBPARTITIONS where STATUS='UNUSABLE'));
spool off
====================

Introduction to Oracle 18C new features

 Source: Internet 

Author: User

The Oracle 18c was released in 2018-02-16 or with Oracle's cloud first concept, and 18c is now available on cloud and engineered Systems. Oracle 18c claims to be an autonomous database that reduces the work of many DBAs, and does a lot of DBA work worry about their work being impacted? In fact, the Oracle 18c does not reflect the particularly obvious, but this is a future trend and signal!

Looking at some of the official documentation, add some new features to share with you, 18C continues the characteristics of the 12C cloud database, and from 18C began to change the future version of the naming method.

Before you know the new features of 18C, you need to have a basic understanding of 12C. June 26, 2013, Oracle released the Oralce 12C. Cloud computing design. 12C introduces the new features of CDB and PDB in the multi-tenancy environment (multitenant environment) introduced in Oracle 12C database (NOTE: Multi-tenancy Technology (English: multi-tenancy technology) or multi-tenancy technology, is a software architecture technology that explores and implements how to share the same system or program components in a multiuser environment, and still ensures the isolation of data between users, allowing a database container (CDB) to host multiple pluggable databases (PDB).

CDB is all called container database, the Chinese translation is a data base container, the PDB is all called pluggable database, you can plug and Unplug databases. Before Oracle 12C, the instance and database were one-to-one or many-on relationships (RAC): an instance could be associated with only one database, and the database could be loaded by multiple instances. The instance and database cannot be a one-to-many relationship. When you enter Oracle 12C, the instance and database can be a one-to-many relationship.

After 12C has a basic understanding of the features, you can see some of the new features added after 18C, and here are some new practical features added.

(a) Availability

2. Oracle Data Guard multi-instance Redo Apply Supports Use of the Block change Tracking Files for RMAN Backups
   Now, Multiple-instance Redo Apply (also called MIRA), you can also support the backup method of BCT (Block change Tracking). This is a very efficient way of incrementally backing up large databases, where the master and slave are RAC (real-time application clusters), and backups take place from the library.
4. Automatic Correction of Non-logged Blocks at a Data Guard Standby Database
   Automatically corrects bad block problems caused by nologging. Two new standby logging modes are added (mainly to speed up the main library loading data):
   One is Standby nologging for Data availability, that is, the commit of the loading operation will be delay until all Standby apply data.
   sql> ALTER DATABASE SET STANDBY nologging for DATA availability;
   One is the Standby nologging for load performance, which is similar to the previous one, but when the Load data is encountered, the network bottleneck is not sent first, which guarantees the loading performance, but the loss of data, but the loss of data, will be retrieved from the primary again.
   sql> ALTER DATABASE SET STANDBY nologging for LOAD performance;
6. Shadow Lost Write Protection
   Create a Shadow tablespaces (Note: Big file tablespace) to provide protection. (Note: At this point you can not need ADG to provide additional lost write protection), write lost shadow protection, can be opened at the table space, database, data file level, to proactively check in advance and prevent write loss.
8. Backups from Non-cdbs is usable after migration to CDB
   The original non-cdb can be migrated in this manner as a PDB to the currently existing CDB.
10. Scalable sequences
    Adaptive sequences are designed to reduce competition under high concurrent DML, by building discontinuous sequences, beating and mitigating split contention on similar indexes, which is an enhancement from Oracle optimization best practices.
12. user-defined sharding Method
    Supports user-defined shards. The feature that existed in the beta version of 12.2 was canceled in the official release. Now, the release was released again.
14. Consistency levels for Multi-shard Queries
    Provides multishard_query_data_consistency initialization parameters that can be set before execution to avoid SCN synchronization when querying across shards.
16. Manual Termination of Run-Away queries
    Now, you can manually kill a statement and keep opening the session:
    ALTER SYSTEM CANCEL SQL.

(b) Big Data and data warehousing

1. Approximate top-n Query processing
   Note: In 18c, Approx_count and Approx_sum are added to match the use of Approx_rank.
2. LOB support with IMC, Big Data Sqllob
   The object also supports in memory.

III) Database Overall

2. Copying a PDB in an Oracle Data Guard environment has 2 new parameters to facilitate the creation of the PDB in the ADG environment.
   One is standby_pdb_source_file_directory, automatically looking for ADG data file path (note, before 18c, if you insert a PDB into a STANDBY environment in the CDB, you need to manually copy the files to the PDB path The other is standby_pdb_source_file_dblink to automatically find the ADG file path when remote clone is convenient (note: Before 18c, if it is a local clone, you do not have to copy the data file, but the remote clone, Need to be copied manually).
4. PDB Lockdown Profile Enhancements
   You can now create a PDB lockdown profile in application root and CDB root.
   You can now create another PDB lockdown profile based on a PDB lockdown profile.
   18c contains three default lockdown Profile:private_dbaas,saas,public_dbaas
6. refreshable PDB switchoverpdb Refresh
   supported the switchover. The switchover is divided into two scenarios in which the planned kernel is unplanned. Planned, can be cut back, mainly for the balance of the CDB load. Unplanned, mainly for PDB master failure, do not switch the entire CDB.
8. PDB Snapshot carouselpdb Snapshot
   Backup turntable, reserved 8 copies by default, backed up every 24 hours.
   ALTER pluggable DATABASE SNAPSHOT MODE every HOURS;
10. New Default location of Oracle Database Password File
    The password file path is already in oracle_base, not oracle_home, in order to remove changes to the oracle_home.
12. Read-only Oracle Home
    can be in DBCA or roohctl-enable to process read Only Oracle home installation, run Orabasehome command can check whether the current Oracle home is read only, if this command output results and Oracle_ho ME, it means that Oracle Home is read-write. If the output is oracle_base/homes/home_name, it means that ORACLE HOME is read-only. Set Oracle_home to read-only, the change file will be created in $ORACLE _base, which facilitates standardization, distribution sharing, rolling upgrades, and more.
14. Online merging of partitions and subpartitions
    Support Online consolidation of partitions, enhanced online maintainability. Note: You need to use the ONLINE keyword.
16. Concurrent SQL execution with SQL performance Analyzerspa
    Can be run in parallel (by default or serial) to help you complete the SPA test faster, this feature is useful when upgrading.

(d) Performance

1. Automatic in-memory
   The automatic in memory will evict infrequently accessed IM columns from memory in the case of Heat Map.
2. Database in-memory support for External Tables
   External tables Support IM features.
3. Memoptimized Rowstore
   There is a memoptimize pool area in the SGA, the size is set by the Memoptimize_pool_size parameter, and when Fast lookup is turned on, the memory area can be used for quick lookups. When Fast lookup is turned on, you need to add a keyword to the build table statement: When querying based on a primary key, you can use fast lookup. Memoptimized Rowstore will greatly improve the high-frequency query based on the primary key in the Internet of things.

(v) RAC and Grid

1. ASM Database Cloning
   ASM database cloning supports multi-tenant data, a feature that provides a native clone of data based on ASM redundancy, which can replace storage-level-based cloning or replication synchronization
2. Converting Normal or High redundancy disk Groups to Flex disk Groups without Restricted Mount
   Encourage the transition to flex DiskGroup.
   (vi) Security
3. Integration of Active Directory Services with Oracle Database
   and Microsoft's AD combination. Before 18c, you need to use Oracle Enterprise User Security (EUS) to interact, and now you can use centrally managed users (CMU) to directly connect AD users and groups and Ora CLE's users and role are mappiing.

These are the main features that are involved in the release of Oracle 18c, and more new features that need to be discovered and summed up in our practice, but from the perspective of Oracle's development strategy, later versions will become smarter, with cloud computing and artificial intelligence in the spring breeze, we wait and see, Expect Oracle to bring more surprises to us!

Introduction to Oracle 18C new features

dba_scheduler

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;

begin
        DBMS_SCHEDULER.create_job(
                job_name                => 'DoDSMaintenance',
                job_type                => 'stored_procedure',
                job_action      => 'DoDSMaintenance',
                start_date              => systimestamp,
                end_date                => null,
                repeat_interval => 'freq=daily; byhour=6; byminute=0; bysecond=0;',
                enabled         => true,
                auto_drop       => false,
                comments        => 'Do daily maintenance on the DS data');
end;
/



quit


select job,schema_user,last_date,next_date,total_time,interval,failures,what from dba_jobs where broken ='N' order by log_user;


select job, log_user,LAST_DATE,NEXT_DATE,NEXT_SEC from dba_jobs;


 col job for 999
col log_user for a14
col priv_user for a14
col schema_user for a14
col what for a49
set lines 120
select job, log_user, priv_user, schema_user, what, broken, failures from dba_jobs;

failover testing

select name,open_mode,database_role from v$database; NAME      OPEN_MODE            DATABASE_ROLE --------- -------------------- ---------------- RDP    READ WRITE           PRIMARY

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; Session altered.

select sequence#,first_time,next_time,applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED ---------- -------------------- -------------------- ---------        491 01-OCT-19 02-OCT-19 NO        491 01-OCT-19 02-OCT-19 YES        492 02-OCT-19 02-OCT-19 YES        492 02-OCT-19 02-OCT-19 NO

select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY

alter system archive log current;

alter database commit to switchover to standby; database altered; or alter database commit to switchover to standby with session shutdown;

shut immedite

startup nomount

alter database mount standby database; database altered;

alter database recover managed standby database disconnect from session;

alter system set log_archive_dest_state_2=defer;

Standby database  SDEFR2PB02

select name,open_mode,database_role from v$database; NAME      OPEN_MODE            DATABASE_ROLE --------- -------------------- ---------------- PRD    MOUNTED              PHYSICAL STANDBY

alter database recover managed standby database cancel;

alter database commit to switchover to primary with session shutdown; or alter database commit to switchover to primary;

shut immediate

startup mount

Alter database open; database altered.

SQL> select INSTANCE_NAME,name,open_mode,database_role from v$database,v$instance; INSTANCE_NAME    NAME      OPEN_MODE            DATABASE_ROLE ---------------- --------- -------------------- ---------------- RDSBY        PRD    READ WRITE           PRIMARY SQL> ! [oracle@SDEFRB02 ~]$ date Wed Oct  2 09:12:57 CEST 2019

Alter system switch logfile

SQL> alter system switch logfile   2  ; System altered. SQL> ! [oracle@SDEFB02 ~]$ date Wed Oct  2 09:15:53 CEST 2019

CONNECTIVITY CHECKING BY O & M TEAM

[oracle@SDEFB02 ~]$ sqlplus SQL*Plus: Release 18.0.0.0.0 - Production on Wed Oct 2 09:16:37 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle.  All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 SQL> show user; USER is "GBO_"