Create password verification function in oracle 12c databases

Create password verification function in oracle 12c databases:

Password verification function can be created both in CDB and in PDB level. They are independent from one another.

Here it is the demo.

@?/rdbms/admin/utlpwdmg.sql
Function created.
Grant succeeded.
Function created.
Grant succeeded.
Function created.
Grant succeeded.

Profile altered.

select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT
--------------------------------------------------------------------------------
ORA12C_VERIFY_FUNCTION

alter session set container=PRDBORCL1;

Session altered.

select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT
--------------------------------------------------------------------------------
NULL


@?/rdbms/admin/utlpwdmg.sql

Function created.

Grant succeeded.
Function created. 
Grant succeeded. 
Function created. 
Grant succeeded. 
Profile altered. 


select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';
LIMIT
--------------------------------------------------------------------------------
ORA12C_VERIFY_FUNCTION

-- CDB level
alter profile default limit password_verify_function null;

Profile altered.

select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';
LIMIT
--------------------------------------------------------------------------------

NULL

--- PDB level

alter session set container=PRDBORCL1;

Session altered.

select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT

--------------------------------------------------------------------------------

ORA12C_VERIFY_FUNCTION

Disable Password Policy in Oracle Database 12c and solution to ORA-28003: password verification for the specified password failed

Disable Password Policy in Oracle Database  12c and solution to ORA-28003: password verification for the specified password failed

-- Problem:



ORA-28003: password verification for the specified password failed

ORA-20001: Password length less than 8



--Solution:





---Check if PASSWORD_VERIFY_FUNCTION is used:



SQL> select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';



LIMIT

--------------------------------------------------------------------------------

ORA12C_VERIFY_FUNCTION





--It is enabled .





--Turn off verification:



alter profile default limit password_verify_function null;

Profile altered.



SQL>  select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';





LIMIT

--------------------------------------------------------------------------------

NULL









--Turn on verification:





SQL> alter profile default limit password_verify_function ORA12C_VERIFY_FUNCTION;



Profile altered.



SQL>  select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';



LIMIT

--------------------------------------------------------------------------------

ORA12C_VERIFY_FUNCTION


 

What is patchset , patch set update and one off patch:

What is patchset , patch set update and one off patch:

Patch set:

Patch set consist of bug fixes for known issues and plus some new futures, we use OUI to install patch set, it is known as upgrade to the next level. It changes the db release number i.e. 4^th digit number.

PSU Patch set update: Quarterly releases, contains fixes for known critical issues for the patch set. It is installed with using Opatch. PSUs are cumulative.

One of patch:

One of patch is a ptch which addresses a particular single bug. It is usually applied with opatch. We install one off patch only when we hit a oracle bug.

DBPITR recovery issues with RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed start until time

-- Please note correct set of backup is must for recovery.. no backup means no recovery..

 list incarnation;



List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       ORCL     1452601228       PARENT  1          07-07-2014 05:38:47

2       2       ORCL     1452601228       PARENT  1594143    09-10-2016 22:34:55

3       3       ORCL     1452601228       CURRENT 1755910    10-10-2016 00:01:34



RMAN> reset database to incarnation 2;



database reset to incarnation 2






list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1452601228       PARENT  1          07-07-2014 05:38:47
2       2       ORCL     1452601228       CURRENT 1594143    09-10-2016 22:34:55
3       3       ORCL     1452601228       ORPHAN  1755910    10-10-2016 00:01:34

RUN
{
SHUTDOWN IMMEDIATE;
startup mount;
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME "TO_DATE('09-10-2016 23:40:00','DD-MM-YYYY HH24:MI:SS')" ;
alter database open resetlogs;
alter pluggable database all open;
}

----Problem:



RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 10/10/2016 09:13:27

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed

 start until time '2016/10/09 23:40:00' using backup controlfile

ORA-00283: recovery session canceled due to errors

ORA-19912: cannot recover to target incarnation 2



RMAN>

RMAN>





-----Solution:



list backup of controlfile;







 ls -ltr /u01/app/oracle/fast_recovery_area/ORCL/autobackup_bkp/2016_10_09/o1_mf_s_924824392_czpfxrrv_.bkp

-rw-r----- 1 oracle dba 18055168 Oct  9 23:39 /u01/app/oracle/fast_recovery_area/ORCL/autobackup_bkp/2016_10_09/o1_mf_s_924824392_czpfxrrv_.bkp



---Check the nearest control file  that we need to restore it first and restore db.





restore controlfile from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup_bkp/2016_10_09/o1_mf_s_924824392_czpfxrrv_.bkp';

alter database mount;

RESTORE DATABASE;

RECOVER DATABASE UNTIL TIME "TO_DATE('09-10-2016 23:40:00','DD-MM-YYYY HH24:MI:SS')" ;

alter database open resetlogs;

alter pluggable database all open;

Backup and restore of pluggable databses, CDB, no cdb and DBPITR in oracle 12c

Backup and restore of pluggable databses, CDB in oracle 12c

Note: Please ensure required backup is available , it is mandatory when working on backup and recovery... No backup mean no recovery, as simple as that.

run

{

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

backup current controlfile spfile;

BACKUP   AS COMPRESSED BACKUPSET   DATABASE PLUS ARCHIVELOG  DELETE ALL INPUT format '/tmp/madhav/%d_inc0_%T_%U.bak';

}









select to_char(sysdate,'hh24:mi dd-mm-yyyy'), current_scn from v$database;







TO_CHAR(SYSDATE, CURRENT_SCN

---------------- -----------

08:44 07-10-2016    72629675



--- Point In Time Recovery (PITR) of a Pluggable Database (PDB) in oracle 12c



run

 {

 alter pluggable database pdborcl close immediate;

 restore pluggable database pdborcl;

 recover pluggable database pdborcl until time "to_date('08:44 07-10-2016','hh24:mi dd-mm-yyyy')"  auxiliary destination '/tmp';

 alter pluggable database pdborcl open resetlogs;

 }



---  Normal recovery of Pluggable Database (PDB) in oracle 12c



run

 {

 alter pluggable database pdborcl close immediate;

 restore pluggable database pdborcl;

 recover pluggable database pdborcl;

 alter pluggable database pdborcl open;

 }









--- Normal or Full restore, recovery of  of oracle 12c CDB and all of its PDBs

-- open cdb in mount status



run

 {

 restore database;

 recover database;

 alter database open;

 alter pluggable database all open ;

 }

--- Restore adn Recover of CDB in oracle 12c -- cdb levle

RUN 
{
alter pluggable database all close immediate;
SHUTDOWN IMMEDIATE; 
startup mount;
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME "TO_DATE('09-10-2016 23:40:00','DD-MM-YYYY HH24:MI:SS')" ;
alter database open resetlogs;
alter pluggable database all open;
}

---- Restore and Recover of NOCDB in oracle 12c
Note: Tested with multiple database open resetlogs; hence reset incarnation had to be done.

bash-4.2$ rman target sys/manager

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Oct 9 20:49:45 2016

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


connected to target database: BANUDB (DBID=881160953, not open)

RMAN>
RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BANUDB   881160953        PARENT  1          07-07-2014 05:38:47
2       2       BANUDB   881160953        PARENT  1594143    19-11-2015 03:18:19
3       3       BANUDB   881160953        PARENT  22187971   09-10-2016 03:20:09
4       4       BANUDB   881160953        CURRENT 22189494   09-10-2016 04:11:10

RMAN> reset database to incarnation 3;

database reset to incarnation 3

RMAN>


RMAN> RUN
{
SHUTDOWN IMMEDIATE;
startup mount;
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME "TO_DATE('09-10-2016 03:30:00','DD-MM-YYYY HH24:MI:SS')" ;
ALTER DATABASE OPEN read only;
}



List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BANUDB   881160953        PARENT  1          07-07-2014 05:38:47
2       2       BANUDB   881160953        PARENT  1594143    19-11-2015 03:18:19
3       3       BANUDB   881160953        CURRENT 22187971   09-10-2016 03:20:09
4       4       BANUDB   881160953        ORPHAN  22189494   09-10-2016 04:11:10

Tablespace Point-in-Time Recovery (TSPITR) with RMAN in oracle 12c database with mininum tablespaces backup

 Tablespace Point-in-Time Recovery (TSPITR) with RMAN in oracle 12c database with mininum tablespaces backup:

run

{

allocate channel c1 type disk format '/tmp/madhav/madhav_%U';

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

backup current controlfile spfile;

BACKUP TABLESPACE SYSTEM, SYSAUX, UNDOTBS1, MADHAV;

}







SQL> select * From madhav.test1;



         A          B

---------- ----------

         1          2

         2          3





select to_char(sysdate,'hh24:mi dd-mm-yyyy'), current_scn from v$database;





TO_CHAR(SYSDATE, CURRENT_SCN

---------------- -----------

04:10 07-10-2016    22052194









drop table madhav.test1;





select * from madhav.test1;

ORA-00942: table or view does not exist







run

{

SQL 'ALTER TABLESPACE madhav OFFLINE IMMEDIATE';

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT  '/tmp/madhav/madhav_%U';

recover tablespace madhav until time "to_date('04:10 07-10-2016 ','hh24:mi dd-mm-yyyy')" auxiliary destination '/tmp/';

SQL "ALTER TABLESPACE madhav ONLINE";

}







RMAN> select * From madhav.test1;



         A          B

---------- ----------

         1          2

         2          3

TABLE RECOVERY AND TABLE PARTITIONS RECOVERY IN DATABASE 12C USING RMAN

TABLE RECOVERY AND TABLE PARTITIONS  RECOVERY IN DATABASE 12C USING RMAN

rman target sys/manager





run

{

allocate channel c1 type disk format '/tmp/madhav/madhav_%U';

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

BACKUP AS BACKUPSET DATABASE ;

}



 select current_scn from v$database;



CURRENT_SCN

-----------

   22045558



 create table madhav.test1 (a number, b number);

 insert into  madhav.test1 values (1,2);

 insert into  madhav.test1 values (2,3);

 commit;

select *from madhav.test1;





         A          B

---------- ----------

         1          2

         2          3




  

 select current_scn from v$database;



using target database control file instead of recovery catalog

CURRENT_SCN

-----------

   22045726





  

 list backup;



 sqlplus madhav/madhav

 drop table madhav.test1;



select * From test1

              *

ERROR at line 1:

ORA-00942: table or view does not exist







 

  rman target sys/manager





 recover table madhav.test1 until scn 22045726    auxiliary destination '/tmp';





 EXIT





   sqlplus madhav/madhav





SQL> select *from madhav.test1;



         A          B

---------- ----------

         1          2

         2          3

 

Recover table partitions :

RECOVER TABLE MADHAV.TBL:TBL_PART98, MADHAV.TBL:TBL_PART99
    UNTIL SEQUENCE 22045726    AUXILIARY DESTINATION '/tmp'
    REMAP TABLE 'MADHAV'.'TBL':'TBL_PART98':'BKP_TBL_PART98',
                'MADHAV'.'TBL':'TBL_PART99':'BKP_TBL_PART99' 
    REMAP TABLESPACE 'TBL_TS':'TBL_BKP_TS';

Tablespace Point-in-Time Recovery(TSPIR) in Oracle 12c non-cdb

Tablespace Point-in-Time Recovery(TSPIR) in Oracle 12c non-cdb

 drop tablespace madhav including contents and datafiles;

 drop user madhav cascade;

 create tablespace madhav datafile '/oracle/app/oracle/oradata/madhav/madhav.dbf' size 10M autoextend on maxsize unlimited ;

 create user madhav identified by madhav default tablespace madhav;



 alter user madhav quota unlimited on madhav;

 alter user madhav temporary tablespace temp;

 grant connect, resource to madhav;

 grant connect, resource to madhav;

 conn madhav/madhav

sho user;

 create table test1 (a number, b number);

 insert into test1 values (1,2);

 insert into test1 values (2,3);

 commit;

select *from test1;



         A          B

---------- ----------

         1          2

         2          3





select to_char(sysdate,'hh24:mi dd-mm-yyyy') from dual;



TO_CHAR(SYSDATE,

----------------

01:33 07-10-2016




rman target sys/manager



run

{

allocate channel c1 type disk format '/tmp/madhav/madhav_%U';

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

}





select to_char(sysdate,'hh24:mi dd-mm-yyyy') from dual;



RMAN>

TO_CHAR(SYSDATE,

----------------

01:35 07-10-2016







 truncate table madhav.test1;

 select *From madhav.test1;


no rows selected










run

{

SQL 'ALTER TABLESPACE madhav OFFLINE IMMEDIATE';

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT  '/tmp/madhav/madhav_%U';

recover tablespace madhav until time "to_date('01:34 07-10-2016','hh24:mi dd-mm-yyyy')" auxiliary destination '/tmp/';

SQL "ALTER TABLESPACE madhav ONLINE";

}





RMAN> select *From madhav.test1;



         A          B

---------- ----------

         1          2

         2          3