Enable flashback database and restore Procedure in 12.1 RDBMS

 

Hai, in this blog we learn how to recreate Flashback for ORACLE Database 12.1

List of Topics (Linux, Database, RAC, EBS)

For Backup and Restore Database and Application:

1. Make tar backup of DB_HOME

2. RMAN Full Backup

3. Flashback restore Point

4. Full Apps tier Backup;   (tar -cvzf /u01/PROD_APPS.tar.gz PROD_APPS)

+++-----the following in Orange color is RMAN backup Script-----+++

sql 'alter system archive log current';

sql "alter session set nls_date_format=''dd.mm.yyyy hh24:mi:ss''";

RUN

{

CONFIGURE CONTROLFILE AUTOBACKUP ON;

configure retention policy to recovery window of 30 days;

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;

set command id to 'SCGOnlineBackupFull';

backup AS COMPRESSED BACKUPSET full database tag SC_FULL format '/u01/RMAN/%d_%T_%s_%p_FULL' ;

sql 'alter system archive log current';

backup tag SC_ARCHIVE format '/u01/RMAN/%d_%T_%s_%p_ARCHIVE' archivelog all not backed up ;

backup tag SC_CONTROL current controlfile format '/u01/RMAN/%d_%T_%s_%p_CONTROL';

crosscheck archivelog all;

delete noprompt archivelog until time 'SYSDATE-8';

delete noprompt obsolete device type disk;

}

+++----------------- END OF SCRIPT ----------------------+++

[oracle@rac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 2 18:41:24 2021

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, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL>

SQL> startup mount

set linesize 200;

select name,open_mode,LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;

NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON       FORCE_LOGGING

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

PROD      MOUNTED              NOARCHIVELOG NO                 YES

SQL>

SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL>

SQL> alter database open;

Database altered.

SQL> select name,open_mode,LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;

NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON       FORCE_LOGGING

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

PROD      READ WRITE           ARCHIVELOG   YES                YES

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from gv$restore_point;

no rows selected

SQL> show parameter spfile;

[oracle@proddb01 dbs]$ cat initPROD1.ora

spfile=+DATA/spfilePROD.ora

[oracle@proddb01 dbs]$ cp initPROD1.ora initPROD1.ora_flb

SQL> create pfile from spfile; -- to be on safer side

SQL> alter system set db_recovery_file_dest='+RECO' scope=spfile sid='*';

System altered.

SQL> alter system set db_recovery_file_dest_size=1500G scope=spfile sid='*';

System altered.

SQL>

SQL> shut immediate

SQL> startup

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      +RECO

db_recovery_file_dest_size           big integer 1500G

SQL>

SQL> SELECT * FROM v$recovery_area_usage;

SQL> SELECT * from v$recovery_file_dest;

Informational:

If gave wrong parameter and unable to startup database

SQL> startup nomount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated

ORA-01262: Stat failed on a file destination directory

Linux-x86_64 Error: 2: No such file or directory

SQL> show parameter spfile;

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

SQL>

SQL> create pfile from spfile='+DATA/spfileprod.ora';

File created.

SQL>

[oracle@proddb01 dbs]$ grep db_recovery initPROD1.ora

*.db_recovery_file_dest='FRA'

*.db_recovery_file_dest_size=1610612736000

[oracle@proddb01 dbs]$

[oracle@proddb01 dbs]$ vi initPROD1.ora  (correct the wrong parameter and start DB)

*.db_recovery_file_dest='+FRA'

[oracle@proddb01 dbs]$

[oracle@proddb01 dbs]$ srvctl config database -d PROD

Database unique name: PROD

Database name: PROD

Oracle home: /oracle/PROD/app/oracle/product/12.1.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/spfilePROD1.ora

SQL> startup pfile='initPROD1.ora'

SQL> create spfile='+DATA/spfileprod.ora' from pfile; (if file already exits it will over ride)

File created.

SQL> 

SQL> shut immediate

[oracle@proddb01 dbs]$ rm -rf spfileprod.ora

[oracle@proddb01 dbs]$ mv initPROD1.ora initPROD1.ora_del

[oracle@proddb01 dbs]$ mv initPROD1.ora_flb initPROD1.ora

[oracle@proddb01 dbs]$ cat initPROD1.ora

spfile=+DATA/spfilePROD.ora

SQL> startup

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

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

spfile                               string      +DATA/spfileprod.ora

SQL>

SQL>

SQL> show parameter db_reco

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      +FRA

db_recovery_file_dest_size           big integer 1500G

SQL>

If Applying Big Patches like DB-PSU, ad/txk, EBS Upgrade, patch then modify sga pga accordingly and run gather stats to speedup process verify on TEST servers:

select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name like 'FND_ST%' order by last_analyzed;--1162

SQL> show user

SYS

SQL> show parameter ga

pga_aggregate_target                 big integer 4G

sga_max_size                         big integer 4G

sga_target                           big integer 4G

db_recovery_file_dest_size           big integer 1500G

SQL> 

alter system set sga_max_size=16G scope=spfile sid='*';

alter system set sga_target=16G scope=spfile sid='*';

alter system set pga_aggregate_target=16G scope =spfile sid='*';

alter system set db_recovery_file_dest_size=1500G scope =spfile sid='*';

IF required run gather stat for SYS Schema to optimize ADOP patching performance (for major update like ad/txk or EBS upgrade) 

(approx. 30+10+1 or depends on DB) min in total for 3 three scripts:

DBMS_STATS.GATHER_SCHEMA_STATS got "'DBMS_STATS.AUTO_SAMPLE_SIZE' must be declared" (Doc ID 2804534.1)

SQL> show user

SYS

>$vi $ORACLE_HOME/sqlplus/admin/glogin.sql

set sqlprompt "_user'@'_connect_identifier 'on' _date> "

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

alter session set nls_date_format='dd/mm/yyyy hh:mi:sspm';

SYS@UAT on 07-Feb-2023 13:49:21>

begin

dbms_stats.gather_schema_stats('SYS',options=>'GATHER STALE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => TRUE);

end;

/

 

PL/SQL procedure successfully completed.

SYS@UAT on 07-Feb-2023 14:02:02>

SYS@UAT on 07-Feb-2023 14:09:41> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

SYS@UAT on 07-Feb-2023 14:11:29>

SYS@UAT on 07-Feb-2023 14:11:40> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

SYS@UAT on 07-Feb-2023 14:12:32>

IF required run gather FND_STATS for ALL objects with estimate percent 40: (approx. 30 mins)

- unlock object stats if fnd_stats is failing with error like

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 26426

ORA-06512: at line 1

ORA-06512: at "APPS.FND_STATS", line 719

ORA-06512: at "APPS.FND_STATS", line 2639

ORA-06512: at "APPS.FND_STATS", line 1604

ORA-06512: at "APPS.FND_STATS", line 1702

ORA-06512: at "APPS.FND_STATS", line 823

ORA-06512: at line 1

SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics  where STATTYPE_LOCKED is not null;

SQL> conn / as sysdba

EXEC DBMS_STATS.unlock_schema_stats('APPS');

EXEC DBMS_STATS.unlock_schema_stats('APPLSYS');

EXEC DBMS_STATS.unlock_schema_stats('SYS');

EXEC DBMS_STATS.unlock_schema_stats('SYSTEM');

EXEC DBMS_STATS.unlock_schema_stats('ODM');

EXEC DBMS_STATS.unlock_schema_stats('AJAR_LIVE');

EXEC DBMS_STATS.unlock_schema_stats('UAT');

EXEC DBMS_STATS.unlock_schema_stats('SQLTXPLAIN');

SQL> conn apps/apps_pswd 

APPS@UAT on 07-Feb-2023 14:17:19>

exec FND_STATS.GATHER_SCHEMA_STATISTICS('ALL',30,30,'NOBACKUP',NULL,'LASTRUN','GATHER AUTO',30,'N');

PL/SQL procedure successfully completed.

APPS@UAT on 07-Feb-2023 14:21:43>

###############################################################

###############################################################

###############################################################

Issue occurred while upgrading EBS Database from 12.1 to 19c using EXPDP doc (Doc ID 2554156.1)

Section 5: Export Source Database Objects

3 Gather statistics into a table

Then, as the apps user, run the script 7.10.2.1 of My Oracle Support Knowledge Document 1581549.1

Best Practices for Minimizing Oracle E-Business Suite Release 12.2.n Upgrade Downtime (Doc ID 1581549.1)  

7.10.2.1 Exporting Statistics

select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name like 'FND_ST%' order by last_analyzed;

conn apps/scapps1

set verify off 

whenever sqlerror exit failure rollback; 

whenever oserror exit failure rollback; 

DECLARE 

BEGIN 

   FND_STATS.BACKUP_SCHEMA_STATS(schemaname => 'ALL', statid => 'expdp>'); 

EXCEPTION 

WHEN OTHERS THEN 

   RAISE_APPLICATION_ERROR(-20000, sqlerrm ||' Error while executing FND_STATS.BACKUP_SCHEMA_STATS package.'); 

END;

/ 

exit;

ERROR at line 1:

ORA-20000: ORA-20002: Version of statistics table "APPLSYS"."FND_STATTAB" is

too old.  Please try upgrading it with dbms_stats.upgrade_stat_table Error

while executing FND_STATS.BACKUP_SCHEMA_STATS package.

ORA-06512: at line 6

SQL> EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('APPLSYS','FND_STATTAB');

BEGIN DBMS_STATS.UPGRADE_STAT_TABLE('APPLSYS','FND_STATTAB'); END;

ERROR at line 1:

ORA-20000: Could not find anything wrong with statistics table

APPLSYS.FND_STATTAB.  If problem persists, please drop and recreate with

dbms_stats.drop_stat_table and dbms_stats.create_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11513

ORA-06512: at line 1

Permanent Fix for Project's Summarization Processes Erroring with APPLSYS.FND_STATTAB After Every Gather Schema Stats. (Doc ID 2023458.1)

Solution: Perform the following action plan:

Please connect to SQL*Plus as APPS user and run the following line:

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('APPLSYS','FND_STATTAB');

- If it gives error - "ORA-20000: Could not find anything wrong with statistics table", then

Please perform below as APPS user in SQL session:-

EXECUTE DBMS_STATS.drop_stat_table('APPLSYS', 'FND_STATTAB');

EXECUTE DBMS_STATS.create_stat_table('APPLSYS', 'FND_STATTAB');

exec ad_zd_table.patch('APPLSYS','FND_STATTAB');

Check FLASHBACK folder in ASM it should be created now:

ASMCMD [+RECO/PROD/FLASHBACK] > ls

log_1.564.1090260889

log_2.566.1090260891

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN

-----------

1.4474E+13

SET NUMWIDTH 20;

SQL>

SQL> create restore point before_cutover guarantee flashback database;

SQL> create restore point before_adtxk guarantee flashback database;

Restore point created.

SQL> select name from gv$restore_point;

SQL> select * from gv$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME

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

RESTORE_POINT_TIME                                                          PRE

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

NAME                                                                                                                                 CON_ID

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

4.3186E+10                     2 YES    524288000 29-NOV-21 01.26.19.000000000 PM

                                                                            YES

BEFORE_ADTXK                                                                                                                              0

For restore back to flash back point:

SQL> startup mount

SQL> select name from gv$restore_point;

SQL> flashback database to restore point BEFORE_ADTXK; (approx. 2 mins) works even if we delete archivelog all

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

 

SQL> drop restore point BEFORE_ADTXK;

Restore point dropped.

SQL> alter database flashback off;

Database altered.

SQL> set linesize 200

select name,open_mode,LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;

NAME      OPEN_MODE            LOG_MODE     FLASHBACK_ON       FORCE_LOGGING

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

PROD      READ WRITE           ARCHIVELOG   NO                 YES

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            +RECO

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL>

######################################################################################################################################

######################################################################################################################################

FLASHBACK RESTORE PROCEDURE FOR RAC DB

[oracle@prd1 ~]$ srvctl stop database -d PRODCDB_jed164

[oracle@prd1 ~]$ srvctl start database -d PRODCDB_jed164 -o mount

[oracle@prd1 ~]$ srvctl status database -d PRODCDB_jed164

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PRODPDB                        READ WRITE NO

SQL>

SQL> flashback database to restore point BEFORE_ADDNODE;

Flashback complete.

SQL>

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-38777: database must not be started in any other instance

SQL> exit

[oracle@prd1 ~]$ srvctl status database -d PRODCDB_jed164

Instance PRODCDB1 is running on node prd1

Instance PRODCDB2 is running on node prd2

[oracle@prd1 ~]$

[oracle@prd1 ~]$ srvctl stop instance -db PRODCDB_jed164 -instance "PRODCDB2"

[oracle@prd1 ~]$ !sq

SQL>  alter database open resetlogs;

Database altered.

SQL> exit

[oracle@prd1 ~]$ srvctl stop database -d PRODCDB_jed164

[oracle@prd1 ~]$ srvctl start database -d PRODCDB_jed164

[oracle@prd1 ~]$ srvctl status database -d PRODCDB_jed164

######################################################################################################################################

######################################################################################################################################

Thanks for Reading

Regards,

Mohammed Areefuddin.

Suggested Topics :

Linux	DATABASE	RMAN	RAC	EBS
5.7 Install	DATABASE Install		11201 Install	R1226 Install
Open Filer Instl	Active DataGaurd	Rman DataGuard	12102 RAC INSTAL	R1229 M7 Clone
DNS in Linux 7	WIN-LINUX MIGRATE	RMAN DB CLONE	RAC DataGuard	Pluggable DB Clone
lpadmin cups	ASMCMD ASM Migrt	RMAN ASM Migrt	RAC DB Clone	appsutil for DB
	11204 M-Upgrade		RAC - RAC Clone	JDK JRE upgrade
	12102 M-Upgrade		12C-19C RAC UPGRADE	Add EBS Node
	Flashback DB			ADOP in R12.2.9
				12102 EBS UPGRADE
Linux Basics	DATABASE Basics	RMAN Basics	RAC Basic	EBS Basics