Search This Blog

Tuesday, 15 August 2017

Archive log generation on an hourly basis:


Archive log generation on an hourly basis ,I got this from
http://www.dbapundits.com/blog/query-script/hourly-daily-archive-generation/




set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;



======================================================================
Gather schema stats in background:I got this from my colleague(Terry) 

----------------------------------------------------------------------
nohup ./gather_stats_RWSH023P.ksh >output.log &

[orcldb@dcplnx22502624 sql]$ cat gather_stats_RWSH023P.ksh
sqlplus / as sysdba << eof
set time on timing on echo on;
spool gather_stats_RWSH023P.log
exec dbms_stats.gather_schema_stats(ownname => 'JOBPRO',cascade => TRUE);
spool off
exit
eof

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


To Monitor stats job (%percentage)

select OPNAME from v$session_longops;

 
COLUMN sid FORMAT A10
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#
and opname ='Gather Schema Statistics'; 



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

Monday, 14 August 2017

REFRESH through RMAN Backup

Primary Database Information
*************************************

  Host   : CYBERJAYA (10.122.164.82)
  DB_NAME  : XESH999R
  Disk_Group : /u01/nfs/oradata, /u01/zfs

Standby Database Information
*************************************

  Host   :   PUTRAJAYA (10.122.164.39)
  DB_NAME  : XESH999R
  DB_UNIQUE_NAME: XESH999R_sty
  Disk_Group : /u01/nfs2/oradata, /u01/zfs


========================================================
Basic pre-check before deploying Active Standby database server.
------------------------------------------------------------------------------------------------

(i) Primary Server, Instance, Databases details.
 ********************************************************


  NAME      INSTANCE_NAME    HOST_NAME            DATABASE_ROLE        LOG_MODE        LOGINS     OPEN_MODE       STARTUP_TIME         VERSION
  --------- ---------------- -------------------- -------------------- --------------- ---------- --------------- -------------------- -----------
  XESH999R      XESH999R             CYBERJAYA.localdomain   PRIMARY              ARCHIVELOG      ALLOWED    READ WRITE      2017:08:14  11.2.0.4.0



 (ii) Archive log Should be enable & Flash Recovery Area Details.
 ******************************************************************************
 
  SQL> archive log list

  Database log mode              Archive Mode
  Automatic archival             Enabled
  Archive destination            USE_DB_RECOVERY_FILE_DEST
  Oldest online log sequence     18
  Next log sequence to archive   20
  Current log sequence           20

  SQL> show parameter recovery

  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------------------------
  db_recovery_file_dest                string      /u01/zfs/XESH999R
  db_recovery_file_dest_size           big integer 4182M
  recovery_parallelism                 integer     0


 (iii) File-System Details of PUTRAJAYA Side.
 ************************************************

  [oracle@CYBERJAYA ~]$ df -h /u01/nfs2/oradata
  Filesystem            Size  Used Avail Use% Mounted on
  /dev/mapper/vg_PUTRAJAYA-lv_root
       86G   22G   60G  27% /
   
  [oracle@CYBERJAYA ~]$ df -h /u01/zfs
  Filesystem            Size  Used Avail Use% Mounted on
  /dev/mapper/vg_PUTRAJAYA-lv_root
       86G   22G   60G  27% /

 
 

Step: 1
==============================================
Set environments and start rman for XESH999R ( @Primary Site).
--------------------------------------------------------------------------------
 
[oracle@m4xa103 ~]$ . oraenv
ORACLE_SID = [oracle] ? XESH999R
The Oracle base has been set to /u01/app/oracle
[oracle@m4xa103 ~]$

[oracle@m4xa103 ~]$ rman PUTRAJAYA /

Recovery Manager: Release 11.2.0.4.0 - production on Sat Aug 5 13:03:57 2017

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

connected to PUTRAJAYA database: XESH999R (DBID=3010714195)

Step: 2
============================================================================
Now check old backup details, but we need new backup with autocontrolfile ( @Primary Site).
------------------------------------------------------------------------------------------------------------------------------------


RMAN> list backup;
using PUTRAJAYA database control file instead of recovery catalog
specification does not match any backup in the repository

Step: 3
===========================================================================
Check controlfile autobakcup details, It's should ON, if Not then enable it. ( @Primary Site).
----------------------------------------------------------------------------------------------------------------------------------


RMAN> show all;

using PUTRAJAYA database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name XESH999R are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_XESH999R.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored


Step: 4
===============================================
Take full databae backup with archivelog ( @Primary Site).
----------------------------------------------------------------------------------


RMAN> backup database plus archivelog;

Starting backup at 14-AUG-17
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=950913403
input archived log thread=1 sequence=5 RECID=2 STAMP=950913414
input archived log thread=1 sequence=6 RECID=3 STAMP=950915922
input archived log thread=1 sequence=7 RECID=4 STAMP=950915925
input archived log thread=1 sequence=8 RECID=5 STAMP=950918291
input archived log thread=1 sequence=9 RECID=6 STAMP=950918867
input archived log thread=1 sequence=10 RECID=7 STAMP=950918898
input archived log thread=1 sequence=11 RECID=8 STAMP=950919116
input archived log thread=1 sequence=12 RECID=9 STAMP=950919118
input archived log thread=1 sequence=13 RECID=10 STAMP=950919119
input archived log thread=1 sequence=14 RECID=16 STAMP=950919173
input archived log thread=1 sequence=15 RECID=18 STAMP=950919788
input archived log thread=1 sequence=16 RECID=20 STAMP=950919790
input archived log thread=1 sequence=17 RECID=22 STAMP=950919791
input archived log thread=1 sequence=18 RECID=24 STAMP=950919793
input archived log thread=1 sequence=19 RECID=26 STAMP=950919794
input archived log thread=1 sequence=20 RECID=28 STAMP=950919795
input archived log thread=1 sequence=21 RECID=30 STAMP=950919796
input archived log thread=1 sequence=22 RECID=32 STAMP=950919796
input archived log thread=1 sequence=23 RECID=34 STAMP=950919796
input archived log thread=1 sequence=24 RECID=36 STAMP=950919797
input archived log thread=1 sequence=25 RECID=38 STAMP=950919797
input archived log thread=1 sequence=26 RECID=41 STAMP=950973779
input archived log thread=1 sequence=27 RECID=42 STAMP=951224601
input archived log thread=1 sequence=28 RECID=43 STAMP=951224614
input archived log thread=1 sequence=29 RECID=44 STAMP=951224875
channel ORA_DISK_1: starting piece 1 at 14-AUG-17
channel ORA_DISK_1: finished piece 1 at 14-AUG-17
piece handle=/u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_annnn_TAG20170814T130755_drbxgnbo_.bkp tag=TAG20170814T130755 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 14-AUG-17

Starting backup at 14-AUG-17
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=00001 name=/u01/nfs/oradata/XESH999R/XESH999R/system01.dbf
input datafile file number=00002 name=/u01/nfs/oradata/XESH999R/XESH999R/sysaux01.dbf
input datafile file number=00014 name=/u01/nfs/oradata/XESH999R/XESH999R/example01.dbf
input datafile file number=00003 name=/u01/nfs/oradata/XESH999R/XESH999R/undotbs01.dbf
input datafile file number=00004 name=/u01/nfs/oradata/XESH999R/XESH999R/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-AUG-17
channel ORA_DISK_1: finished piece 1 at 14-AUG-17
piece handle=/u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_nnndf_TAG20170814T130803_drbxgwpf_.bkp tag=TAG20170814T130803 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 14-AUG-17

Starting backup at 14-AUG-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=45 STAMP=951224968
channel ORA_DISK_1: starting piece 1 at 14-AUG-17
channel ORA_DISK_1: finished piece 1 at 14-AUG-17
piece handle=/u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_annnn_TAG20170814T130928_drbxkk2x_.bkp tag=TAG20170814T130928 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-AUG-17

Starting Control File and SPFILE Autobackup at 14-AUG-17
piece handle=/u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-AUG-17

RMAN> exit


Step: 5
===========================================
Creare some directiories on PUTRAJAYA side ( @PUTRAJAYA Site).
----------------------------------------------------------------------------

[oracle@PUTRAJAYA ~]$ mkdir -p  /u01/zfs/XESH999R

[oracle@PUTRAJAYA ~]$ mkdir -p /u01/nfs2/oradata/XESH999R

[oracle@PUTRAJAYA ~]$ mkdir -p /u01/app/oracle/admin/XESH999R/adump

Step: 6
===================================================================
Tranfer backupset,autobackup & password file to PUTRAJAYA location  ( @Primary Site).
-------------------------------------------------------------------------------------------------------------------


[oracle@m4xa103 XESH999R]$ pwd
/u01/zfs/XESH999R/XESH999R
[oracle@m4xa103 XESH999R]$ ls -lrth
total 16K
drwxr-x--- 2 oracle oinstall 4.0K Aug  2 16:30 flashback
drwxr-x--- 5 oracle oinstall 4.0K Aug  5 13:03 archivelog
drwxr-x--- 3 oracle oinstall 4.0K Aug  5 13:07 backupset
drwxr-x--- 3 oracle oinstall 4.0K Aug  5 13:09 autobackup

[oracle@m4xa103 XESH999R]$ scp -r backupset autobackup oracle@192.168.56.102:/u01/zfs/XESH999R/XESH999R
oracle@192.168.56.102's password:

o1_mf_annnn_TAG20170814T130755_drbxgnbo_.bkp                   100%   67MB  67.3MB/s   00:01
o1_mf_annnn_TAG20170814T130928_drbxkk2x_.bkp                   100%   57KB  56.5KB/s   00:00
o1_mf_nnndf_TAG20170814T130803_drbxgwpf_.bkp                   100% 1085MB  60.3MB/s   00:18
o1_mf_s_951224970_drbxklvm_.bkp                                100% 9888KB   9.7MB/s   00:00

[oracle@m4xa103 XESH999R]$ cd /u01/app/oracle/XESH999Ruct/11.2.0/db_1/dbs/
[oracle@m4xa103 dbs]$ ls -lrth

total 19M
-rw-r--r--. 1 oracle oinstall 2.8K May 15  2009 init.ora
-rw-r-----. 1 oracle oinstall   24 Jul 26 12:46 lkXESH999R
-rw-r-----. 1 oracle oinstall 2.5K Jul 26 14:22 orapwXESH999R
-rw-rw----. 1 oracle oinstall 1.6K Aug  1 00:35 hc_XESH999R.dat
-rw-r-----. 1 oracle oinstall 3.5K Aug  1 00:36 spfileXESH999R.ora
-rw-r-----. 1 oracle oinstall 9.4M Aug  1 00:54 snapcf_XESH999R.f
-rw-r-----  1 oracle oinstall   24 Aug  1 21:15 lkXESH999R
-rw-r-----  1 oracle oinstall 1.5K Aug  1 21:17 orapwXESH999R
-rw-rw----  1 oracle oinstall 1.6K Aug  5 13:03 hc_XESH999R.dat
-rw-r-----  1 oracle oinstall 3.5K Aug  5 13:04 spfileXESH999R.ora
-rw-r-----  1 oracle oinstall 9.6M Aug  5 13:09 snapcf_XESH999R.f

[oracle@m4xa103 dbs]$ scp orapwXESH999R oracle@192.168.56.102:/u01/app/oracle/XESH999Ruct/11.2.0/db_1/dbs/
oracle@192.168.56.102's password:

orapwXESH999R                                                    100% 1536     1.5KB/s   00:00


Step: 7
=============================================================================
On PUTRAJAYA server start database via rman and restore spfile from autobackup  ( @PUTRAJAYA Site).
-------------------------------------------------------------------------------------------------------------------------------------

[oracle@PUTRAJAYA ~]$ vi /etc/oratab

[oracle@PUTRAJAYA ~]$ . oraenv
ORACLE_SID = [oracle] ? XESH999R
The Oracle base has been set to /u01/app/oracle

[oracle@PUTRAJAYA dbs]$ rman PUTRAJAYA /

Recovery Manager: Release 11.2.0.4.0 - XESH999Ruction on Sat Aug 5 13:18:43 2017

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

connected to PUTRAJAYA database (not started)

RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/XESH999Ruct/11.2.0/db_1/dbs/initXESH999R.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                281019272 bytes
Database Buffers             780141444 bytes
Redo Buffers                   5517312 bytes

RMAN> restore spfile from '/u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp';

Starting restore at 14-AUG-17
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 14-AUG-17

RMAN> exit


Step: 8
==========================================================================================
Come to PUTRAJAYA SQL*Plus promte and create pfile from spfile and do some modification acording to PUTRAJAYA server.  ( @PUTRAJAYA Site).
--------------------------------------------------------------------------------------------------------------------------------------------------------------


[oracle@PUTRAJAYA ~]$ . oraenv
ORACLE_SID = [oracle] ? XESH999R
The Oracle base has been set to /u01/app/oracle

[oracle@PUTRAJAYA ~]$ cd $ORACLE_HOME/dbs
[oracle@PUTRAJAYA dbs]$ ls -lrth

-rw-r-----  1 oracle oinstall 3.5K Aug  5 13:28 spfileXESH999R.ora
[oracle@PUTRAJAYA dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 XESH999Ruction on Sat Aug 5 13:30:41 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit XESH999Ruction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile from spfile;

File created.

SQL> exit


[oracle@PUTRAJAYA dbs]$ ls -lrth

-rw-r-----  1 oracle oinstall 3.5K Aug  5 13:28 spfileXESH999R.ora
-rw-r--r--  1 oracle oinstall 1.4K Aug  5 13:30 initXESH999R.ora

[oracle@PUTRAJAYA dbs]$ vi initXESH999R.ora

*.audit_file_dest='/u01/app/oracle/admin/XESH999R/adump'
*.control_files='/u01/nfs2/oradata/XESH999R/XESH999R/control01.ctl','/u01/nfs2/oradata/XESH999R/XESH999R/control02.ctl'
*.db_recovery_file_dest='/u01/zfs/XESH999R'

:wq


Step: 9
======================================================================================
Remove spfile from dbs location, down previous stared instnace  & start it again via pfile  ( @PUTRAJAYA Site).
------------------------------------------------------------------------------------------------------------------------------------------------------

[oracle@PUTRAJAYA dbs]$ rm -rf spfileXESH999R.ora

[oracle@PUTRAJAYA dbs]$ ps -ef | grep pmon | grep -v grep
oracle    2437     1  0 13:19 ?        00:00:00 ora_pmon_XESH999R
[oracle@PUTRAJAYA dbs]$
[oracle@PUTRAJAYA dbs]$ !s
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 XESH999Ruction on Sat Aug 5 13:37:09 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit XESH999Ruction
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> shut immediate
ORA-01507: database not mounted


[oracle@PUTRAJAYA dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 XESH999Ruction on Sat Aug 5 13:38:03 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1006636072 bytes
Database Buffers          637534208 bytes
Redo Buffers                7094272 bytes
SQL> create spfile from pfile;

File created.

SQL> exit


Step: 10
=====================================================================================
Now start rman and restore controlfile from autobackup & put database in mount stage  ( @PUTRAJAYA Site).

[oracle@PUTRAJAYA dbs]$ rman PUTRAJAYA /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 5 13:38:27 2017

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

connected to PUTRAJAYA database: XESH999R (not mounted)

RMAN> restore controlfile from '/u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp';

Starting restore at 14-AUG-17
using PUTRAJAYA database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/nfs2/oradata/XESH999R/XESH999R/control01.ctl
output file name=/u01/nfs2/oradata/XESH999R/XESH999R/control02.ctl
Finished restore at 14-AUG-17

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Step: 11
========================================================
Now tell your newly stared db to your backup location  ( @PUTRAJAYA Site).
-------------------------------------------------------------------------------------------------

RMAN> catalog start with '/u01/zfs/XESH999R/XESH999R/';

Starting implicit crosscheck backup at 14-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 14-AUG-17

Starting implicit crosscheck copy at 14-AUG-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 14-AUG-17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp

searching for all files that match the pattern /u01/zfs/XESH999R/XESH999R/

List of Files Unknown to the Database
=====================================
File Name: /u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp


Step: 12
=============================================
Finally restore & recovery your database  ( @PUTRAJAYA Site).
-------------------------------------------------------------------------------

RMAN> run {
2> SET NEWNAME FOR DATAFILE 1 TO "/u01/nfs2/oradata/XESH999R/XESH999R/system01.dbf";                                                               
3> SET NEWNAME FOR DATAFILE 2 TO "/u01/nfs2/oradata/XESH999R/XESH999R/sysaux01.dbf";                                                               
4> SET NEWNAME FOR DATAFILE 3 TO "/u01/nfs2/oradata/XESH999R/XESH999R/undotbs01.dbf";                                                              
5> SET NEWNAME FOR DATAFILE 4 TO "/u01/nfs2/oradata/XESH999R/XESH999R/users01.dbf";                                                                
6> SET NEWNAME FOR DATAFILE 5 TO "/u01/nfs2/oradata/XESH999R/XESH999R/example01.dbf";
7> restore database;
8> switch datafile all;
9> recover database;
10> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 14-AUG-17
using channel ORA_DISK_1

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/nfs2/oradata/XESH999R/XESH999R/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/nfs2/oradata/XESH999R/XESH999R/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/nfs2/oradata/XESH999R/XESH999R/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/nfs2/oradata/XESH999R/XESH999R/users01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/nfs2/oradata/XESH999R/XESH999R/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_nnndf_TAG20170814T130803_drbxgwpf_.bkp
channel ORA_DISK_1: piece handle=/u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_nnndf_TAG20170814T130803_drbxgwpf_.bkp tag=TAG20170814T130803
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 14-AUG-17

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=951227735 file name=/u01/nfs2/oradata/XESH999R/XESH999R/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=951227735 file name=/u01/nfs2/oradata/XESH999R/XESH999R/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=951227735 file name=/u01/nfs2/oradata/XESH999R/XESH999R/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=951227735 file name=/u01/nfs2/oradata/XESH999R/XESH999R/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=951227735 file name=/u01/nfs2/oradata/XESH999R/XESH999R/example01.dbf

Starting recover at 14-AUG-17
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=30
channel ORA_DISK_1: reading from backup piece /u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_annnn_TAG20170814T130928_drbxkk2x_.bkp
channel ORA_DISK_1: piece handle=/u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_annnn_TAG20170814T130928_drbxkk2x_.bkp tag=TAG20170814T130928
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/zfs/XESH999R/XESH999R/archivelog/2017_08_14/o1_mf_1_30_drc080cv_.arc thread=1 sequence=30
channel default: deleting archived log(s)
archived log file name=/u01/zfs/XESH999R/XESH999R/archivelog/2017_08_14/o1_mf_1_30_drc080cv_.arc RECID=46 STAMP=951227736
unable to find archived log
archived log thread=1 sequence=31
RMAN-01471: ===========================================================
RMAN-01469: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-01471: ===========================================================
RMAN-03002: failure of recover command at 08/14/2017 13:55:37
RMAN-06144: media recovery requesting unknown archived log for thread 1 with sequence 31 and starting SCN of 1044850

RMAN> exit

******************************************
Note: the error can be ignored as RMAN has applied all the available archive log files and is now trying to apply a non-existent archive log file.the error can be ignored as RMAN has applied all the available archive log files and is now trying to apply a non-existent archive log file.
**********************************************



Step: 13
===================================================================================
Now try to start your database with resetlogs and will get below error, then rename your redologs. ( @PUTRAJAYA Site).
------------------------------------------------------------------------------------------------------------------------------------------------

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '/u01/nfs/oradata/XESH999R/XESH999R/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9


SQL> alter database rename file  '/u01/nfs/oradata/XESH999R/XESH999R/redo01.log' TO '/u01/nfs2/oradata/XESH999R/XESH999R/redo01.log';

Database altered.

SQL> alter database rename file  '/u01/nfs/oradata/XESH999R/XESH999R/redo02.log' TO '/u01/nfs2/oradata/XESH999R/XESH999R/redo02.log';

Database altered.

SQL> alter database rename file  '/u01/nfs/oradata/XESH999R/XESH999R/redo03.log' TO '/u01/nfs2/oradata/XESH999R/XESH999R/redo03.log';

Database altered.


Step: 14
========================================================================================
Again try to start your database with resetlogs and will get below error, Clear your one unclear redologfile. ( @PUTRAJAYA Site).
--------------------------------------------------------------------------------------------------------------------------------------------------------

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/nfs2/oradata/XESH999R/XESH999R/redo01.log'



SQL> select GROUP#,THREAD#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from v$log;

    GROUP#    THREAD#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1   52428800        512          1 NO  CLEARING_CURRENT       1044850 14-AUG-17   2.8147E+14
         3          1   52428800        512          1 YES CLEARING               1044763 14-AUG-17      1044850 14-AUG-17
         2          1   52428800        512          1 YES CLEARING               1041102 14-AUG-17      1044763 14-AUG-17

 
 
SQL>  alter database clear logfile '/u01/nfs2/oradata/XESH999R/XESH999R/redo01.log';

Database altered.

Step: 14
===========================================================================
Again try to start your database with resetlogs and you are done here..!!. ( @PUTRAJAYA Site).
---------------------------------------------------------------------------------------------------------------------------------


SQL> select GROUP#,THREAD#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from v$log;

    GROUP#    THREAD#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1   52428800        512          1 NO  CURRENT                1044850 14-AUG-17   2.8147E+14
         3          1   52428800        512          1 YES CLEARING               1044763 14-AUG-17      1044850 14-AUG-17
         2          1   52428800        512          1 YES CLEARING               1041102 14-AUG-17      1044763 14-AUG-17

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> exit

Wednesday, 2 August 2017

RMAN-08137 Error on Primary Database

I am going to explain how to bypass RMAN-08137 error when we use dataguard. When we try to delete archive log files in primary database,

See the  following error.
##############################################################################

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
backup_arch  02.08.2017 00:51:54 ERROR   > ------------------- some errors found, rman backup completed wirh ERRORS [rc=35].
backup_arch  02.08.2017 00:51:56 ERROR   > script ora_backup_arch terminated with ERRORS [rc=1]
backup_arch  02.08.2017 00:51:56 ERROR   > Archivelog backup terminated with ERRORS, email with logfile send to fmb-ts-XX-db-ops-exadata@XXXX.com
 #############################################################################

There is a hidden parameter to bypass this. This parameter has been introduced with 11.2.0.x version.Before this version  setting log_archive_dest_n parameter as null  is enough to define standby database is completely unreachable and you can delete archivelog.But after this version there is a hidden parameter  to do this operation.First of all we have to disable log_archive_dest_state_n parameter



SQL> show parameter log_archive_dest_state


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER

 => After you set this parameter as ‘DEFER’ we must set hidden parameter like following.Normally this parameter is TRUE.


=================================================================== 
 Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select x.ksppinm name,y.ksppstvl value from sys.x$ksppi x,sys.x$ksppcv y where x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance')
and x.indx=y.indx and x.ksppinm='_deferred_log_dest_is_valid';

NAME                           VALUE
------------------------------ ------------------------------
_deferred_log_dest_is_valid    TRUE

 => We can set this parameter like following.

 SQL> alter system set "_deferred_log_dest_is_valid" = FALSE scope=both;

 SQL> select x.ksppinm name,y.ksppstvl value from sys.x$ksppi x,sys.x$ksppcv y where x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance')
and x.indx=y.indx and x.ksppinm='_deferred_log_dest_is_valid';


NAME                           VALUE
------------------------------ ------------------------------
_deferred_log_dest_is_valid    FALSE

 After this change my archive deletion job is working fine.

Thursday, 20 July 2017

DG Switchover:(Switchover from _G1 (GREAT_SER) Primary Database to _C1(COOL_SER)
====================================================================
Through DGMGRL:

The SWITCHOVER is technically a database role transition, where replication is reversed and primary/standby databases are changing their
current database roles:

=> Initial Primary Database: <Database_Name>_G1 will become the current Standby database after Switchover
=> Initial Physical Standby: <Database_Name>_C1 will become the current Primary database after Switchover

Primary:
dgmgrl> show configuration;
dgmgrl> show database ‘<INSTANCE_NAME>_G1';
dgmgrl> show database ‘<INSTANCE_NAME>_C1';

 Connect on standby site (C1) with DG-Broker to the database:


Standby(it will become new primary after switchover)

server>. goto $ DBNAME
server> dgmgrl
dgmgrl> connect sys/<password>@<INSTANCE_NAME>_C1

dgmgrl> show configuration;
--- No errors are reported, then


=> Pre-Check for any replication delay and DG Broker errors.

Resolve any delay and error(s) before:

dgmgrl> switchover to ‘<INSTANCE_NAME>_C1';

=> Make <INSTANCE_NAME>_C1 the primary database. Invoke the actual switchover.
Connect on standby site (C1) with DG-Broker to the database:


server>. goto $ DBNAME
server> dgmgrl
dgmgrl> connect sys/<password>@<INSTANCE_NAME>_C1

After first swithover you have to add tempfile with reuse command on new primary(old standby).

=====================================================================
alter tablespace TEMP add tempfile '/oracle/XXXX/oratemp01/o_XXX_TEMP_1.dbf' size 1g reuse;
=====================================================================

Post Check if Standby database is only mounted:

You must have a license to use Active Data Guard, where the standby database is opened

dgmgrl> show configuration;
dgmgrl> show database ‘<INSTANCE_NAME>_G1';
dgmgrl> show database ‘<INSTANCE_NAME>_C1';

server> sqlplus /nolog
sqlplus> connect sys/<password>@<INSTANCE_NAME>_G1
sqlplus>

col line format a200
select to_char(sysdate,'DD.MM.YYYY-HH24:MI:SS')||': ' ||i.host_name||', '
||nvl(d.DB_UNIQUE_NAME,d.NAME)||'('||d.NAME||','||i.INSTANCE_NAME||'), ' ||translate(d.DATABASE_ROLE,' ','_')||',
'||translate(d.OPEN_MODE,' ','_')||', ' ||i.VERSION||', startuptime='||to_char(i.STARTUP_TIME,'DD.MM.YYYY-HH24:MI:SS') as line from
V$DATABASE d, V$INSTANCE i ;
If Standby database is opened and you do not have a license for Active Data Guard, restart and mount only the database.


Thursday, 29 June 2017

Standby SYNC check

Check redo received and applied on standby:
STANDBY SIDE:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  56063                 54996       1067
         2                     85                    85          0


Primary:

select ads.dest_id,max(sequence#) "Current Sequence",
 max(log_sequence) "Last Archived"
   from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
   where ad.dest_id=al.dest_id
   and al.dest_id=ads.dest_id
   and al.resetlogs_change#=(select max(resetlogs_change#) from v$archived_log )
 group by ads.dest_id;


   DEST_ID Current Sequence Last Archived
---------- ---------------- -------------
         1              678           678
         2              678           679

Check for GAP on Standby:
Primary:
select max(sequence#) from v$log_history;

Standby :
select max(sequence#) from v$log_history; 


How to find the GAP on Standby with primay:


SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

 Both primary and standby:

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#; 

select sequence#, first_time, next_time, applied from v$archived_log;

========================================================================
If you start a foreground session, control is not returned to the command prompt until recovery is canceled by another session.
To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

or

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING current ARCHIVED LOGFILE DISCONNECT;
     ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;



This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This command does not disconnect the current SQL session.
=======================================================================
To stop Redo Apply, issue the following SQL statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


--Monitoring Redo Apply on Physical Standby Databases---

SELECT PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE ROLE, SWITCHOVER_STATUS FROM V$DATABASE;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

select sequence#, applied from v$archived_log order by sequence# ;

select THREAD#,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED from v$archived_log;
select RECOVERY_MODE from v$archive_dest_status;

select ERROR,STATUS from v$archive_dest_status;

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

SELECT FS_FAILOVER_STATUS "FSFO STATUS",FS_FAILOVER_CURRENT_TARGET TARGET,FS_FAILOVER_THRESHOLD THRESHOLD,FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT" FROM V$DATABASE;

SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;

SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$LOG_HISTORY;

SELECT DEST_ID, APPLIED_SCN FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';
col DEST_NAME format a30;

SELECT DEST_NAME,STATUS,APPLIED_SEQ#,SYNCHRONIZATION_STATUS,GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_NAME='STANDBY_ARCHIVE_DEST';

============================================
Restore Point:

col name format a30
col value format a30

SELECT name, value  FROM gv$parameter WHERE name LIKE '%flashback%';

SELECT flashback_on, log_mode FROM v$database;

-- normal restore point
CREATE RESTORE POINT BOPPUDI_1_BEFORE_DEMAGE;

col name format a15
col time format a32
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                                  SCN TIME                                DATABASE_INCARNATION# GUA STORAGE_SIZE
------------------------------ ---------- ----------------------------------- --------------------- --- ------------
BOPPUDI_1_BEFORE_DEMAGE                       1.4690E+13 23-JUN-18 05.28.57.000000000 AM                         8 YES            0
BOPPUDI_2_BEFORE_DEMAGE                       1.4690E+13 23-JUN-18 08.39.53.000000000 AM                         8 YES   4.2950E+10


HOW TO RECOVER FROM RESTORE POINT:
-----------------------------------------------------------
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ;

shutdown immediate;

startup mount;

flashback database to restore point before_damage;

alter database open resetlogs;


DROP RESTORE POINT:
-------------------------------------
DROP RESTORE POINT BOPPUDI_RESTORE;





Friday, 3 March 2017

DBMS_METADATA.GET_DDL PACKAGE


DBMS_METADSATA package is used to get DDL of a particular object:



select dbms_metadata.get_ddl('DB_LINK','OBJECT_NAME','OWNER') from dual;
EXAMPLE:
select dbms_metadata.get_ddl('DB_LINK','CMIACC_CMIDEV','ESO_USER') from dual;

select dbms_metadata.get_ddl('MATERIALIZED_VIEW','OBJECT_NAME','OWNER') from dual;
EXAPPLE:
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MV_REPORT_PLANS','CMIACC') from dual;

select dbms_metadata.get_ddl('CLUSTER','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('CONTEXT','OBJECT_NAME') from dual;

select dbms_metadata.get_ddl('DB_LINK','OBJECT_NAME','PUBLIC') from dual;

select dbms_metadata.get_ddl('FUNCTION','OBJECT_NAME','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','OBJECT_NAME','SYSTEM') from dual;

select dbms_metadata.get_ddl('JAVA_SOURCE','OBJECT_NAME','ADAM') from dual

select dbms_metadata.get_ddl('JAVA_SOURCE','OBJECT_NAME','SYS') from dual

select dbms_metadata.get_ddl('LIBRARY','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('OPERATOR','OBJECT_NAME') from dual;

select dbms_metadata.get_ddl('OPERATOR','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('PACKAGE','OBJECT_NAME') from dual;

select dbms_metadata.get_ddl('PACKAGE','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('PACKAGE_BODY','OBJECT_NAME','PERFSTAT') from dual;

select dbms_metadata.get_ddl('PROCEDURE','OBJECT_NAME','SYSTEM') from dual;

select dbms_metadata.get_ddl('SEQUENCE','OBJECT_NAME','PERFSTAT') from dual;

select dbms_metadata.get_ddl('SYNONYM','OBJECT_NAME','PUBLIC') from dual;

select dbms_metadata.get_ddl('TABLE','OBJECT_NAME','SYSTEM') from dual;

select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

select dbms_metadata.get_ddl('TRIGGER','OBJECT_NAME','SYSTEM') from dual;

select dbms_metadata.get_ddl('TYPE','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('TYPE_BODY','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('VIEW','OBJECT_NAME','SYS') from dual;

select dbms_metadata.get_ddl('VIEW','OBJECT_NAME') from dual;

select dbms_metadata.get_ddl('PACKAGE','OBJECT_NAME','SYS') from dual;


Extract DDL for Users:

Let’s use DBMS_METADATA.GET_DDL package to extract CREATE USER DDL.
SQL> select dbms_metadata.get_ddl ('USER',username) as col1 from dba_users where username = 'SCOTT';

SYSTEM_GRANT:
select dbms_metadata.get_granted_ddl ('SYSTEM_GRANT',username) as col1 from dba_users where username  = 'SCOTT';

OBJECT_GRANT:
select dbms_metadata.get_granted_ddl('OBJECT_GRANT',username) as col1 from dba_users where username = 'SCOTT';

ROLES_GRANTED_TO_USERS: 
select dbms_metadata.get_granted_ddl('ROLE_GRANT',username) as col1 from dba_users where username  = 'SCOTT';

Tuesday, 7 February 2017

Recreating spfile on ASM storage from pfile

1. try to screw up the db parameters
SQL> show parameter memory
NAME TYPE VALUE
———————————— ———– ——————————
hi_shared_memory_address integer 0
memory_max_target big integer 1520M
memory_target big integer 1520M
shared_memory_address integer 0
SQL> alter system set memory_max_target=0 scope=spfile;
System altered.
2. now bounce the instance, db will complain about the new settings
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-01078: failure in processing system parameters
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
3. in my case the spfile sits on ASM
ASMCMD> ls -l spfile*
Type Redund Striped Time Sys Name
N spfileorcl.ora => +DATA/ORCL/PARAMETERFILE/spfile.267.744731331
4. what we need to do is creating a pfile from spfile then modify parameter back to valid value, then start db from pfile
1). With db not up, we can create pfile from spfile:
SQL> create pfile from spfile=’+DATA/orcl/spfileorcl.ora’;
2). modify the value in pfile ‘initorcl.ora’
$ vi initorcl.ora
*.memory_max_target=1583349760
3). startup db with pfile
SQL>startup mount –now it will use the pfile
5. create the new spfile to ASM storage from “good” pfile
SQL> create spfile=’+DATA/ORCL/spfileorcl.ora’ from pfile;
File created.
6. watch the file name in ASM storage has been changed, which means we just had a new spfile:
ASMCMD> ls -l spfile*
Type Redund Striped Time Sys Name
N spfileorcl.ora => +DATA/ORCL/PARAMETERFILE/spfile.267.744733351
7. now change the pfile back to be the “bootstrap” of correct spfile
$ cat initorcl.ora
spfile=’+DATA/ORCL/spfileorcl.ora’
8. restart the database, it will pickup the correct spfile again
$ sqlplus / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2213736 bytes
Variable Size 973080728 bytes
Database Buffers 603979776 bytes
Redo Buffers 7434240 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
———————————— ———– ——————————
spfile string +DATA/orcl/spfileorcl.ora
SQL> show parameter memory
NAME TYPE VALUE
———————————— ———– ——————————
hi_shared_memory_address integer 0
memory_max_target big integer 1520M
memory_target big integer 1520M
shared_memory_address integer 0