Rollback database in RAC using guranteed restorepoint(GRP)

A. Create guaranteed restore point in testqa database.

Database name : TESTQA,

Instances: TESTQA1,TESTQA2

SQL>create restore point RESTORE_TESTQA guarantee flashback database;

B. Restore database using GRP. 

1. srvctl stop database -d testqa -o immediate
2. srvctl start database -d testqa -o mount
3. SQL>flashback database to restore point RESTORE_TESTQA; --using sqlplus
4. If error about missing redo logs, restore archivelogs using RMAN
5. srvctl stop database -d testqa
6. srvctl start instance -d testqa -i testqa1 -o mount
7. SQL> alter database open resetlogs; --using sqlplus
8. srvctl start instance -d testqa -i testqa2
9. srvctl stop database -d testqa -o immediate
10. srvctl start database -d testqa
11. SQL> drop restore point RESTORE_TESTQA; --using sqlplus

Convert temporary tablespace from small file to big file.

Convert temporary tablespace from smallfile to bigfile.

1.check the current default temporary tablespace.

SQL> select property_name, property_value

    from database_properties

    where property_name like '%TEMP%';  2

PROPERTY_NAME              PROPERTY_VALUE

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

DEFAULT_TEMP_TABLESPACE    TEMP

2.Create new temporary tablespace TEMP2.

CREATE BIGFILE TEMPORARY TABLESPACE TEMP2 TEMPFILE

   SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 2G

TABLESPACE GROUP ''

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

3.Change the default temporary tablespace to TEMP2.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

4.Drop the smallfile TEMP tablespace.

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

5.Create new bigfile TEMP temporary tablespace.

CREATE BIGFILE TEMPORARY TABLESPACE TEMP TEMPFILE

   SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 2G

TABLESPACE GROUP ''

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

6.Change the default temporary tablespace to TEMP.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

7.Drop the TEMP2 tablespace.

DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;