Restore point

Check DiskGroup Name ...

select listagg (name, ' | ') within group (order by name) "     DiskGroupName"  from v$asm_diskgroup;

     DiskGroupName

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

DATA | ALOG

select name,database_role,open_mode,flashback_on,log_mode from v$database;

NAME      DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON       LOG_MODE

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

  RAC     PRIMARY          READ WRITE           NO                 ARCHIVELOG

show parameter recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 0

recovery_parallelism                 integer     0

remote_recovery_file_dest            string

ALTER SYSTEM set db_recovery_file_dest_size=40G scope=both sid='*' ; 

ALTER SYSTEM SET db_recovery_file_dest='+LOGS' sid='*';

ALTER SYSTEM set db_recovery_file_dest_size=40G scope=both sid='*' ; 

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest='+LOGS' sid='*';

System altered.

CREATE RESTORE POINT test_anuj GUARANTEE FLASHBACK DATABASE;

CREATE RESTORE POINT test_anuj GUARANTEE FLASHBACK DATABASE;SQL>

Restore point created.

SQL> 

set linesize 300 pagesize 300

 select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID

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

CONTROL FILE                             0                         0               0          0

REDO LOG                                 0                         0               0          0

ARCHIVED LOG                             0                         0               0          0

BACKUP PIECE                             0                         0               0          0

IMAGE COPY                               0                         0               0          0

FLASHBACK LOG                          .98                         0               2          0

FOREIGN ARCHIVED LOG                     0                         0               0          0

AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

RESTORE POINT ONLY    <<<<<<<<<<if FLASHBACK_ON - NO 

set pagesize 200 linesize 300

col scn for 999,999,999,999,999

col incar for 99

col name for a25

col time for a40

col storage_size for 999,999,999,999

col guarantee_flashback_database for a35

select 

      database_incarnation# as incar,

      scn,

      name,

      time,

      storage_size,

      guarantee_flashback_database

from  v$restore_point

order by 4;

INCAR                  SCN NAME                      TIME                                         STORAGE_SIZE GUARANTEE_FLASHBACK_DATABASE

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

    3          263,845,411 TEST_ANUJ                 01-JUN-18 02.07.57.000000000 PM               209,715,200 YES

alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;

col name for a50

select * from v$flashback_database_logfile;

NAME                                                     LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME       TYPE          CON_ID

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

+LOGS/IRAC/FLASHBACK/log_1.678.977666877                   1          2          1  209715200     263845409 01-06-2018 14:07 NORMAL             0

+LOGS/IRAC/FLASHBACK/log_2.677.977666883                   2          2          1  209715200             0                  RESERVED           0

SQL> create table test_anuj as select * from scott.emp ;

Table created.

CREATE RESTORE POINT test_anuj1 GUARANTEE FLASHBACK DATABASE;

CREATE RESTORE POINT test_anuj1 GUARANTEE FLASHBACK DATABASE;SQL>

Restore point created.

set pagesize 200 linesize 300

col scn for 999,999,999,999,999

col incar for 99

col name for a25

col time for a40

col storage_size for 999,999,999,999

col guarantee_flashback_database for a35

select 

      database_incarnation# as incar,

      scn,

      name,

      time,

      storage_size,

      guarantee_flashback_database

from  v$restore_point

order by 4;

INCAR                  SCN NAME                      TIME                                         STORAGE_SIZE GUARANTEE_FLASHBACK_DATABASE

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

    3          263,845,411 TEST_ANUJ                 01-JUN-18 02.07.57.000000000 PM                         0 YES

    3          263,846,069 TEST_ANUJ1                01-JUN-18 02.16.28.000000000 PM               209,715,200 YES

PROMPT How Far Back Can We Flashback To (Time)?

PROMPT

select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log;

PROMPT

PROMPT How Far Back Can We Flashback To (SCN)?

PROMPT

col oldest_flashback_scn format 99999999999999999999999999

select oldest_flashback_scn from v$flashback_database_log;

PROMPT

PROMPT Flashback Area Usage

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

PROMPT

col round(space_limit/1048576) heading "space allocated (mb)" format 999999

col round(space_used/1048576)  heading "space used (mb)"      format 99999

col name                       heading "flashback location"   format a50

select name, round(space_limit/1048576),round(space_used/1048576) from v$recovery_file_dest;

create table test_anuj1 as select * from scott.emp ;

SQL> create table test_anuj1 as select * from scott.emp ;

Table created.

SQL>startup mount;

SQL> flashback database to restore point TEST_ANUJ1;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from test_anuj1;    <<<< this table gone 

select * from test_anuj1

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select * from test_anuj;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

Via Rman ...

RUN {

        STARTUP MOUNT;

        FLASHBACK DATABASE TO RESTORE POINT 'TEST_ANUJ';

        ALTER DATABASE OPEN RESETLOGS;

        SHUTDOWN IMMEDIATE;

}

RMAN>

RUN {

        STARTUP MOUNT;

        FLASHBACK DATABASE TO RESTORE POINT 'TEST_ANUJ';

        ALTER DATABASE OPEN RESETLOGS;

        SHUTDOWN IMMEDIATE;

}

RMAN>

RMAN> 

Oracle instance started

database mounted

Total System Global Area    4294967296 bytes

Fixed Size                     8628936 bytes

Variable Size               1577059640 bytes

Database Buffers            2701131776 bytes

Redo Buffers                   8146944 bytes

Starting flashback at 01-JUN-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=372 instance=ibrac2 device type=DISK

starting media recovery

archived log for thread 2 with sequence 440 is already on disk as file +DATA/IBRAC/ARCHIVELOG/2018_06_01/thread_2_seq_440.688.977670083

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

Finished flashback at 01-JUN-18

Statement processed

database closed

database dismounted

Oracle instance shut down

set pagesize 200 linesize 300

col scn for 999,999,999,999,999

col incar for 99

col name for a25

col time for a40

col storage_size                 for 999,999,999,999

col guarantee_flashback_database for a35

select 

      database_incarnation# as incar,

      scn,

      name,

      time,

      storage_size,

      guarantee_flashback_database

from  v$restore_point

order by 4;

INCAR                  SCN NAME                      TIME                                         STORAGE_SIZE GUARANTEE_FLASHBACK_DATABASE

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

    3          263,845,411 TEST_ANUJ                 01-JUN-18 02.07.57.000000000 PM                         0 YES

    3          263,846,069 TEST_ANUJ1                01-JUN-18 02.16.28.000000000 PM               209,715,200 YES

SQL> drop restore point TEST_ANUJ1;

Restore point dropped.

set pagesize 200 linesize 300

col scn for 999,999,999,999,999

col incar for 99

col name for a25

col time for a40

col storage_size for 999,999,999,999

col guarantee_flashback_database for a35

SELECT 

      database_incarnation# as Incar,

      scn,

      name,

      time,

      storage_size,

      guarantee_flashback_database

FROM  v$restore_point

ORDER BY 4

INCAR                  SCN NAME                      TIME                                         STORAGE_SIZE GUARANTEE_FLASHBACK_DATABASE

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

    3          263,845,411 TEST_ANUJ                 01-JUN-18 02.07.57.000000000 PM               209,715,200 YES

col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999

col round(space_used/1048576) heading "Space Used (MB)" format 99999

col name Heading "Flashback Location" format a50

select name, round(space_limit/1048576),round(space_used/1048576) from v$RECOVERY_FILE_DEST;

Flashback Location                                 Space Allocated (MB) Space Used (MB)

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

+LOGS                                                             40960             402

PROMPT How Far Back Can We Flashback To (Time)?

PROMPT

select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log;

SQL> How Far Back Can We Flashback To (Time)?

Oldest Flashback Time

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

01-jun-2018 14:08:03

col oldest_flashback_scn format 99999999999999999999999999

select oldest_flashback_scn from v$flashback_database_log;

       OLDEST_FLASHBACK_SCN

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

                  263845411

schema refresh

Tuesday, 8 December 2015

Schema Refresh step by step

Below are the details step for schema refresh . The below steps are very helpful when you want to refresh schemas in QA/DEV and want to keep all the previous grants and privileges after refresh .

1. Take the backup of the schema in target database.


expdp directory=<directory_name> dumpfile=backup_%U.dmp logfile=Backup.log parllel=<n> compression=all
schemas=Schema_name

2. sys_privs.sql :
---------------

set echo off
set head off
set pagesize 0
set feedback off
set linesize 200
spool sys_privs.sql
select 'grant '||privilege||' to '||grantee||' ; '  from dba_sys_privs where grantee in ('schema_name');
spool off

3.owner.sql :
-----------

spool owner.sql
select 'grant '||privilege||' on '||OWNER||'.'||table_name||' to  '||grantee||' ; ' from dba_tab_privs WHERE OWNER in ('schema_name');
spool off

4.pre_refresh_role_privs.sql :
----------------------------

set echo off
set head off
set pagesize 0
set feedback off
set linesize 200
spool pre_refresh_role_privs.sql
select 'grant '||granted_role||' to '||grantee||' ; '  from dba_role_privs where grantee in ('schema_name');
spool off

5.Check the object count in the database under that schema
 SELECT object_type,COUNT(*) FROM DBA_OBJECTS WHERE OWNER in ('schema_name') group by object_type ;

6. check the table count
SELECT COUNT(*) FROM DBA_tables WHERE OWNER in ('schema_name');

7.drop all the object under that schema
select 'drop '||object_type||' '||owner||'."'||object_name||'";' from dba_objects where owner in ('schema_names');

8.drop all the tables under that schema
select 'drop table  '||owner||'.'||table_name||' cascade constraints;' from dba_tables where owner in('schema_names');

9.purge the recyclebin
select 'purge table '||owner||'."'||object_name||'";' FROM dba_RECYCLEBIN where owner='schema_name' ;

10.now import data into target database from source dumpfile.
 impdp dumpfile=<dumpfile_name> logfile=import.log directory=<directory_name> schemas=schema_name parallel=<n>

11.check the count of objects after refresh for each schema
SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER in ('schema_name');

12.check the table count
SELECT COUNT(*) FROM DBA_tables WHERE OWNER='schema_name';

13.run the spool file
@sys_privs.sql

14. run the spool file
@owner.sql

15.run the spool file
pre_refresh_role_privs.sql

-------------- Schema refresh is complete ----------------------

Schema Refresh Using Datapump

Schema refresh is one of the routine task in DBA's life, for moving the objects from one schema to another schema or one database to another database normally we use expdp and impdp or normal export and import as well but here i am going to explain through data pump because compare to normal export and import data pump is faster . below post will explain you complete schema refresh solution. 

Oracle Data Pump is introduced from oracle 10g, Oracle Data Pump enables very high speed movement of data and metadata from one database to another database. Before performing the schema refresh check whether that user having EXP_FULL_DATABASE role or not.

Follow below steps in source and target database sides :

Source Database Side :

Step 1:

Check the Schema Exist or Not. Schema must be there in source database.

SQL> select username, account_status, created from dba_users where username='ORACLE';

Step 2:

Check the schema size

SQL> select owner, sum(bytes)/1024/1024  "MB"  from dba_segments where owner="ORACLE";

Step 3:

Take the count of schema objects, this will be use full after complete the refresh to compare both target and source schema objects.

SQL> select object_type, count(*) from dba_objetcts where owner='ORACLE' group by object_type;

Step 4:

Before going to take the export, first check mount point size where you're  going to store the export dumpfile, if mount point doesn't have sufficient space export job gets fail.

example :

]$  cd /d01/exports

exports]$ df  -h  .  (in Linux)  df  -g . (AIX and Sun Solaris)

Step 5 :

Create a datapump directory in database level, default datapump directory  location is " /app/oracle/product/10.2.0/rdbms/log/".

First create a directory in OS Level

]$ mkdir -p  /d01/exports

Next create in database levele

SQL>  create or replace directory DATAPUMP as '/d01/exports';

Step 6 :

Now take the export of schema.

]$ expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR dumpfile= ORACLE_DBNAME.dmp logfile= ORACLE_DBNAME.log schemas=ORACLE

Step 7 :

If  you're going to import on same server means no need to move the dumpfile anywhere, incase if you're going to import this dump file in some other server, copy this dumpfile through SCP command

expdp]$ scp  -p  username@servername:\do1\targetlocation   (It will ask you target server password )

We have completed almost all steps in source database end, now we are moving to target database side,

Target Database :

Step 1 :

Check the mount pint size, it should be more then schema size.

]$  cd /d01/exports

exports]$ df  -h  .  (in Linux)  df  -g . (AIX and Sun Solaris)

Step 2 :

Create a directory same like how we have create for source database.

First create a directory in OS Level

]$ mkdir -p  /d01/exports

Next create in database levele

SQL>  create or replace directory DATAPUMP as '/d01/exports';

Step 3:

Take target schema backup before importing.(for safe side). with export command.

]$ expdp \'/ AS SYSDBA\' directory=DATAPUMP dumpfile= ORACLE_DBNAME.dmp logfile= ORACLE_DBNAME.log schemas=ORACLE

Step 4 :

SCRIPTS:Find complete list of  objects in the schema

Use this script when you do not need to drop the schema, But only the dependent objects in the schema. For ex :- to preserve DB-links, grants, privileges

Below scripts will generate all of the drop statements needed to drop almost all objects (sometimes 1-2 of them will have problems and you will have to manually drop those) from a specified schema (it prompts you for the schema).

EXECUTION

Login to the database where the schema to be dropped exists. Copy and paste the following script , double checking that you are in the correct database!:

--Initializations

set linesize 1000;

SET VERIFY OFF

col owner format a15;

col object_name format a30;

col object_type format a20;

--Select Non-system object owners

SELECT OWNER,OBJECT_TYPE,COUNT(*)

FROM SYS.DBA_OBJECTS

WHERE OWNER NOT IN ('SYS','SYSTEM','TSMSYS','ORACLE_OCM','WMSYS','PATMAN','OUTLN','PUBLIC','DBSNMP','XDB','APPQOSSYS','CTXSYS')

GROUP BY OWNER,OBJECT_TYPE

ORDER BY OWNER,OBJECT_TYPE;

--Select specific Owner, Object_Type & Count

SELECT OWNER,OBJECT_TYPE,COUNT(*)

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

GROUP BY OWNER,OBJECT_TYPE

ORDER BY OWNER,OBJECT_TYPE;

--Drops: Tables, Indexes, & Triggers

SELECT 'DROP TABLE '||OWNER||'.'||OBJECT_NAME||' CASCADE CONSTRAINTS PURGE;'

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('TABLE');

--Drops: Sequences, Views, Packages, Functions & Procedures, Synonyms

SELECT 'DROP '|| OBJECT_TYPE || ' ' ||OWNER||'.'||OBJECT_NAME||';'

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('PACKAGE','SEQUENCE','VIEW','FUNCTION','PROCEDURE','SYNONYM','TRIGGER');

--Drops: Types

SELECT 'DROP '|| OBJECT_TYPE || ' ' ||OWNER||'.'||OBJECT_NAME||' FORCE;'

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('TYPE');

--DO NOT DROP OR IMPORT DBLINKS, EXCLUDE=DATABASE_LINK

SELECT OWNER, OBJECT_TYPE, COUNT(*)

FROM SYS.DBA_OBJECTS

WHERE OWNER = '&&USER'

AND OBJECT_TYPE IN ('DATABASE LINK')

GROUP BY OWNER, OBJECT_TYPE;

OR

Drop the Schema at destination database:

SQL> Drop Schema <schema_name> cascade;

(Better drop only schema objects instead of dropping  schema)

Step 5 :

Import the dumpfile into target schema

impdp \'/ AS SYSDBA\' directory=DATAPUMP dumpfile=ORACLE_DATABASE.dmp logfile=ORACLE_DATABASE.log schemas=ORACLE

Step 6 :

Compare the Object Count with source database.

SQL> select object_type, count(*) from dba_objetcts where owner='ORACLE' group by object_type;

(If all the objects same and looks good go ahead and run utlrp.sql.

Step 7 :

Check invalid objects count

SQL> select owner, object_type, status, count(*)

from sys.dba_objects

where status = ‘INVALID’

group by owner, object_type, status

order by owner, object_type, status;

SQL> @?/rdbms/admin/utlrp.sql