Search This Blog

Total Pageviews

Sunday 25 November 2012

Oracle 11g single instance archivelog mode on ASM



Oracle 11g- Single instance  archivelog mode on ASM 



Oracle 11gR2 archivelog mode on ASM
Oracle 11gR2 archivelog mode

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576      9216     5657              896            2380              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576      9216     8988                0            8988              0             N  FRA/


ASMCMD> cd FRA

ASMCMD> mkdir Archive

ASMCMD> ls -lt
Type       Redund  Striped  Time             Sys  Name
                                             Y    ONLINELOG/
                                             N    Archive/
                                             Y    AUTOBACKUP/
                                             N    redo03b.log => +FRA/ORCL/ONLINELOG/group_3.257.741266861
                                             N    redo02b.log => +FRA/ORCL/ONLINELOG/group_2.258.741267209
                                             N    redo01b.log => +FRA/ORCL/ONLINELOG/group_1.256.741266959

ASMCMD> pwd
+FRA/ORCL/Archive


[oracle@oel5u4-orcl ~] . oraenv
ORACLE_SID = [orcl] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 24 19:43:11 2012

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


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


SQL> alter system set LOG_ARCHIVE_FORMAT ='Log%s_%t_%r.Arc' scope=spfile;
System altered.

SQL> alter system set log_archive_dest_1='location=+FRA/ORCL/Archive' SCOPE=SPFILE;
System altered.

SQL> alter system set log_archive_start=TRUE SCOPE=SPFILE;  --- no need in Oracle 11gr2
System altered.

SQL> alter system reset log_archive_start scope=spfile sid='*';
System altered.

SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  472887296 bytes
Fixed Size                  1337296 bytes
Variable Size             360712240 bytes
Database Buffers          104857600 bytes
Redo Buffers                5980160 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA/orcl/archive
Oldest online log sequence     32
Next log sequence to archive   34
Current log sequence           34

SQL> alter database open ;
Database altered.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.
SQL> /
System altered.


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

[oracle@oel5u4-orcl ~] . oraenv
ORACLE_SID = [orcl] ? +ASM
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle

[oracle@oel5u4-+ASM ~] asmcmd -p
ASMCMD [+] > cd FRA
ASMCMD [+FRA] > ls -lt
Type  Redund  Striped  Time             Sys  Name
                                        Y    ORCL/
ASMCMD [+FRA] > cd ORCL
ASMCMD [+FRA/ORCL] > cd Archive
ASMCMD [+FRA/ORCL/Archive] > ls -lt
Type        Redund  Striped  Time             Sys  Name
                                              N    log36_1_731227289.arc => +FRA/ORCL/ARCHIVELOG/2012_11_24/thread_1_seq_36.262.800222675
                                              N    log35_1_731227289.arc => +FRA/ORCL/ARCHIVELOG/2012_11_24/thread_1_seq_35.261.800222653
                                              N    log34_1_731227289.arc => +FRA/ORCL/ARCHIVELOG/2012_11_24/thread_1_seq_34.260.800222627
ASMCMD [+FRA/ORCL/Archive] >


Oracle 11g Duplicating Database without connecting to Target Database .....




Oracle 11g Duplicating Database without connecting to Target Database .....


Backup script 
take backup

# rman nocatalog target / <<-eof div="div">
   configure retention policy to recovery window of 3 days;
   configure backup optimization on;
   configure controlfile autobackup on;
   configure default device type to disk;
   configure device type disk parallelism 1 backup type to compressed backupset;
   configure datafile backup copies for device type disk to 1;
   configure maxsetsize to unlimited;
   configure snapshot controlfile name to '/u01/app/Backup/snapshot_controlfile';
   show all;
   run {
     allocate channel ch1 type Disk maxpiecesize = 1900M;
     backup full database noexclude
     include current controlfile
     format '/u01/app/Backup/datafile_%s_%p.bak'
     tag 'datafile_daily';
   }
   run {
     allocate channel ch1 type Disk maxpiecesize = 1900M;
     backup archivelog all
     delete all input
     format '/u01/app/Backup/archivelog_%s_%p.arch'
     tag 'archivelog_daily';
   }
   run {
      allocate channel ch1 type Disk maxpiecesize = 1900M;
      backup format '/u01/app/Backup/controlfile_%s.bak' current controlfile;
   }
   crosscheck backup;
   list backup of database;
   report unrecoverable;
   report schema;
   report need backup;
   report obsolete;
   delete noprompt expired backup of database;
   delete noprompt expired backup of controlfile;
   delete noprompt expired backup of archivelog all;
   delete noprompt obsolete recovery window of 3 days;
   quit
#EOF




Main Database                    : orcl
Auxiliary Database Server : dupli

for testing ...We are doing this on same server and on same +ASM group 

[oracle@oel5u4-dpuli dbs] cat initdpuli.ora
dpuli.__db_cache_size=58720256
dpuli.__java_pool_size=8388608
dpuli.__large_pool_size=4194304
dpuli.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dpuli.__pga_aggregate_target=209715200
dpuli.__sga_target=264241152
dpuli.__shared_io_pool_size=0
dpuli.__shared_pool_size=180355072
dpuli.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/dpuli/adump'  #<<<< create this dir  
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA','+DATA'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='us.oracle.com'
*.db_name='dpuli'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4259315712
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='location=+FRA/dpuli/Archive'  # <<<<---- archive="archive" create="create" dir="dir" div="div" fail="fail" for="for" in="in" it="it" log="log" other="other" this="this" will="will" wise="wise">
*.log_archive_format='Log%s_%t_%r.Arc'
*.memory_target=471859200
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


[oracle@oel5u4-orcl dbs] pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs


Create password file 

[oracle@oel5u4-orcl dbs] orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdpuli password=oracle
[oracle@oel5u4-orcl dbs] ls -ltr
total 9572
-rw-r--r-- 1 oracle oinstall    2851 May 15  2009 init.ora
drwx------ 2 oracle oinstall    4096 Oct  1  2010 peshm_DBUA0_0
-rw-rw---- 1 oracle oinstall    1544 Oct  1  2010 hc_DBUA0.dat
drwx------ 2 oracle dba         4096 Oct  1  2010 peshm_orcl_0
-rw-rw---- 1 oracle dba         1544 Oct  1  2010 hc_orcl.dat
-rw-r----- 1 oracle dba           24 Oct  1  2010 lkORCL
-rw-r----- 1 oracle oinstall      35 Oct  1  2010 initorcl.ora
-rw-r----- 1 oracle oinstall    1536 Oct  1  2010 orapworcl
-rw-r----- 1 oracle dba      9748480 Nov 24 18:57 snapcf_orcl.f
-rw-r----- 1 oracle oinstall    1536 Nov 24 22:02 orapwdpuli

cd /u01/app/oracle/admin
mkdir dpuli
mkdir dpdump adump pfile   --- <<<<<<<<<<<<<<<

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=dpuli


sqlplus sys/oracle as sysdba


[oracle@oel5u4-dpuli dbs] sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 24 22:05:02 2012

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

Connected to an idle instance.

startup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdpuli.ora



[oracle@oel5u4-dpuli dpuli] !sql
sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 24 22:20:02 2012

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

Connected to an idle instance.

SQL> def
DEFINE _DATE           = "24-NOV-12" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "dpuli" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "" (CHAR)
DEFINE _O_RELEASE      = "" (CHAR)


SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdpuli.ora

ORACLE instance started.

Total System Global Area  472887296 bytes
Fixed Size                  1337296 bytes
Variable Size             406849584 bytes
Database Buffers           58720256 bytes
Redo Buffers                5980160 bytes

[oracle@oel5u4-dpuli dpuli]  rman auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Nov 24 22:27:36 2012

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

connected to auxiliary database: DPULI (not mounted)


duplicate database to 'dpuli' backup location '/u01/app/Backup';

RMAN> duplicate database to 'dpuli' backup location '/u01/app/Backup';


[oracle@oel5u4-orcl ~] export ORACLE_SID=dpuli
[oracle@oel5u4-dpuli ~] !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 25 07:52:32 2012

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

Connected to an idle instance.

SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdpuli.ora

ORACLE instance started.

Total System Global Area  472887296 bytes
Fixed Size                  1337296 bytes
Variable Size             406849584 bytes
Database Buffers           58720256 bytes
Redo Buffers                5980160 bytes


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

after nomount ....

[oracle@oel5u4-dpuli ~] ps -ef|grep -i smon
oracle    6666     1  0 07:32 ?        00:00:02 asm_smon_+ASM
oracle    7870     1  1 07:56 ?        00:00:02 ora_smon_dpuli
oracle    7988  7602 38 07:59 pts/4    00:00:00 grep -i smon


[oracle@oel5u4-dpuli ~] rman auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Nov 25 08:00:16 2012

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

connected to auxiliary database: DPULI (not mounted)

RMAN> duplicate database to 'dpuli' backup location '/u01/app/Backup'; 

Starting Duplicate Db at 25-NOV-12

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     472887296 bytes

Fixed Size                     1337296 bytes
Variable Size                411043888 bytes
Database Buffers              54525952 bytes
Redo Buffers                   5980160 bytes

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/dpuli/controlfile/current.274.800266185'', ''+DATA/dpuli/controlfile/current.273.800266189'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DPULI'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  '/u01/app/Backup/controlfile_16.bak';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/dpuli/controlfile/current.274.800266185'', ''+DATA/dpuli/controlfile/current.273.800266189'' 

comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DPULI'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     472887296 bytes

Fixed Size                     1337296 bytes
Variable Size                411043888 bytes
Database Buffers              54525952 bytes
Redo Buffers                   5980160 bytes

Starting restore at 25-NOV-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
output file name=+DATA/dpuli/controlfile/current.274.800266185
output file name=+DATA/dpuli/controlfile/current.273.800266189
Finished restore at 25-NOV-12

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK

contents of Memory Script:
{
   set until scn  1427605;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 25-NOV-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/Backup/datafile_11_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/app/Backup/datafile_11_1.bak tag=DATAFILE_DAILY
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:25:08
Finished restore at 25-NOV-12

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=800267909 file name=+DATA/dpuli/datafile/system.272.800266397
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=800267911 file name=+DATA/dpuli/datafile/sysaux.271.800266407
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=800267912 file name=+DATA/dpuli/datafile/undotbs1.269.800266419
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=800267913 file name=+DATA/dpuli/datafile/users.268.800266427
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=800267914 file name=+DATA/dpuli/datafile/example.270.800266415

contents of Memory Script:
{
   set until scn  1427605;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 25-NOV-12
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=41
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/Backup/archivelog_14_1.arch
channel ORA_AUX_DISK_1: piece handle=/u01/app/Backup/archivelog_14_1.arch tag=ARCHIVELOG_DAILY
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
archived log file name=+FRA/dpuli/archive/log41_1_731227289.arc thread=1 sequence=41
channel clone_default: deleting archived log(s)
archived log file name=+FRA/dpuli/archive/log41_1_731227289.arc RECID=1 STAMP=800268038
media recovery complete, elapsed time: 00:00:54
Finished recover at 25-NOV-12

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''DPULI'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     472887296 bytes

Fixed Size                     1337296 bytes
Variable Size                411043888 bytes
Database Buffers              54525952 bytes
Redo Buffers                   5980160 bytes

sql statement: alter system set  db_name =  ''DPULI'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     472887296 bytes

Fixed Size                     1337296 bytes
Variable Size                411043888 bytes
Database Buffers              54525952 bytes
Redo Buffers                   5980160 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DPULI" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M ,
  GROUP  3  SIZE 50 M
 DATAFILE
  '+DATA/dpuli/datafile/system.272.800266397'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/dpuli/datafile/sysaux.271.800266407",
 "+DATA/dpuli/datafile/undotbs1.269.800266419",
 "+DATA/dpuli/datafile/users.268.800266427",
 "+DATA/dpuli/datafile/example.270.800266415";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy
datafile copy file name=+DATA/dpuli/datafile/sysaux.271.800266407 RECID=1 STAMP=800268593
cataloged datafile copy
datafile copy file name=+DATA/dpuli/datafile/undotbs1.269.800266419 RECID=2 STAMP=800268594
cataloged datafile copy
datafile copy file name=+DATA/dpuli/datafile/users.268.800266427 RECID=3 STAMP=800268594
cataloged datafile copy
datafile copy file name=+DATA/dpuli/datafile/example.270.800266415 RECID=4 STAMP=800268595

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=800268593 file name=+DATA/dpuli/datafile/sysaux.271.800266407
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=800268594 file name=+DATA/dpuli/datafile/undotbs1.269.800266419
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=800268594 file name=+DATA/dpuli/datafile/users.268.800266427
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=800268595 file name=+DATA/dpuli/datafile/example.270.800266415

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 25-NOV-12

RMAN>



SQL> select OPEN_MODE from v$database ;

OPEN_MODE
--------------------
READ WRITE



select INSTANCE_NAME,DATABASE_STATUS,HOST_NAME,STATUS from v$instance

INSTANCE_NAME    DATABASE_STATUS   HOST_NAME                                                        STATUS
---------------- ----------------- ---------------------------------------------------------------- ------------
dpuli            ACTIVE            oel5u4                                                           OPEN



SQL> show parameter spfile ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfiledpuli.ora

SQL> create pfile='/tmp/initdupli.txt' from spfile ;

File created.


Now New spfile <<<<<<<<<<<<<<<<<<<

SQL> !cat /tmp/initdupli.txt
*.__db_cache_size=56M
dpuli.__db_cache_size=54525952
*.__java_pool_size=8M
dpuli.__java_pool_size=8388608
*.__large_pool_size=4M
dpuli.__large_pool_size=4194304
*.__oracle_base='/u01/app/oracle'# ORACLE_BASE set from environment
*.__pga_aggregate_target=200M
dpuli.__pga_aggregate_target=209715200
*.__sga_target=252M
dpuli.__sga_target=264241152
*.__shared_io_pool_size=0
dpuli.__shared_io_pool_size=0
*.__shared_pool_size=172M
dpuli.__shared_pool_size=180355072
*.__streams_pool_size=4M
dpuli.__streams_pool_size=4194304
*._aggregation_optimization_settings=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._and_pruning_enabled=TRUE
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_folding_enabled=TRUE
*._bloom_pruning_enabled=TRUE
*._complex_view_merging=TRUE
*._compression_compatibility='11.2.0.0.0'
*._connect_by_use_union_all='TRUE'
*._convert_set_to_join=FALSE
*._cost_equality_semi_join=TRUE
*._cpu_to_io=0
*._dimension_skip_null=TRUE
*._eliminate_common_subexpr=TRUE
*._enable_type_dep_selectivity=TRUE
*._fast_full_scan_enabled=TRUE
*._first_k_rows_dynamic_proration=TRUE
*._gby_hash_aggregation_enabled=TRUE
*._generalized_pruning_enabled=TRUE
*._globalindex_pnum_filter_enabled=TRUE
*._gs_anti_semi_join_allowed=TRUE
*._improved_outerjoin_card=TRUE
*._improved_row_length_enabled=TRUE
*._index_join_enabled=TRUE
*._ksb_restart_policy_times='0','60','120','240'# internal update to set default
*._left_nested_loops_random=TRUE
*._local_communication_costing_enabled=TRUE
*._minimal_stats_aggregation=TRUE
*._mmv_query_rewrite_enabled=TRUE
*._new_initial_join_orders=TRUE
*._new_sort_cost_estimate=TRUE
*._nlj_batching_enabled=1
*._optim_adjust_for_part_skews=TRUE
*._optim_enhance_nnull_detection=TRUE
*._optim_new_default_join_sel=TRUE
*._optim_peek_user_binds=TRUE
*._optimizer_adaptive_cursor_sharing=TRUE
*._optimizer_better_inlist_costing='ALL'
*._optimizer_cbqt_no_size_restriction=TRUE
*._optimizer_coalesce_subqueries=TRUE
*._optimizer_complex_pred_selectivity=TRUE
*._optimizer_compute_index_stats=TRUE
*._optimizer_connect_by_combine_sw=TRUE
*._optimizer_connect_by_cost_based=TRUE
*._optimizer_connect_by_elim_dups=TRUE
*._optimizer_correct_sq_selectivity=TRUE
*._optimizer_cost_based_transformation='LINEAR'
*._optimizer_cost_hjsmj_multimatch=TRUE
*._optimizer_cost_model='CHOOSE'
*._optimizer_dim_subq_join_sel=TRUE
*._optimizer_distinct_agg_transform=TRUE
*._optimizer_distinct_elimination=TRUE
*._optimizer_distinct_placement=TRUE
*._optimizer_eliminate_filtering_join=TRUE
*._optimizer_enable_density_improvements=TRUE
*._optimizer_enable_extended_stats=TRUE
*._optimizer_enhanced_filter_push=TRUE
*._optimizer_extend_jppd_view_types=TRUE
*._optimizer_extended_cursor_sharing='UDO'
*._optimizer_extended_cursor_sharing_rel='SIMPLE'
*._optimizer_extended_stats_usage_control=224
*._optimizer_fast_access_pred_analysis=TRUE
*._optimizer_fast_pred_transitivity=TRUE
*._optimizer_filter_pred_pullup=TRUE
*._optimizer_fkr_index_cost_bias=10
*._optimizer_group_by_placement=TRUE
*._optimizer_improve_selectivity=TRUE
*._optimizer_join_elimination_enabled=TRUE
*._optimizer_join_factorization=TRUE
*._optimizer_join_order_control=3
*._optimizer_join_sel_sanity_check=TRUE
*._optimizer_max_permutations=2000
*._optimizer_mode_force=TRUE
*._optimizer_multi_level_push_pred=TRUE
*._optimizer_native_full_outer_join='FORCE'
*._optimizer_new_join_card_computation=TRUE
*._optimizer_null_aware_antijoin=TRUE
*._optimizer_or_expansion='DEPTH'
*._optimizer_order_by_elimination_enabled=TRUE
*._optimizer_outer_to_anti_enabled=TRUE
*._optimizer_push_down_distinct=0
*._optimizer_push_pred_cost_based=TRUE
*._optimizer_rownum_bind_default=10
*._optimizer_rownum_pred_based_fkr=TRUE
*._optimizer_skip_scan_enabled=TRUE
*._optimizer_sortmerge_join_inequality=TRUE
*._optimizer_squ_bottomup=TRUE
*._optimizer_star_tran_in_with_clause=TRUE
*._optimizer_system_stats_usage=TRUE
*._optimizer_table_expansion=TRUE
*._optimizer_transitivity_retain=TRUE
*._optimizer_try_st_before_jppd=TRUE
*._optimizer_undo_cost_change='11.2.0.1'
*._optimizer_unnest_corr_set_subq=TRUE
*._optimizer_unnest_disjunctive_subq=TRUE
*._optimizer_use_cbqt_star_transformation=TRUE
*._optimizer_use_feedback=TRUE
*._or_expand_nvl_predicate=TRUE
*._ordered_nested_loop=TRUE
*._parallel_broadcast_enabled=TRUE
*._partition_view_enabled=TRUE
*._pivot_implementation_method='CHOOSE'
*._pre_rewrite_push_pred=TRUE
*._pred_move_around=TRUE
*._push_join_predicate=TRUE
*._push_join_union_view=TRUE
*._push_join_union_view2=TRUE
*._px_minus_intersect=TRUE
*._px_pwg_enabled=TRUE
*._px_ual_serial_input=TRUE
*._query_rewrite_setopgrw_enable=TRUE
*._remove_aggr_subquery=TRUE
*._replace_virtual_columns=TRUE
*._right_outer_hash_enable=TRUE
*._selfjoin_mv_duplicates=TRUE
*._sql_model_unfold_forloops='RUN_TIME'
*._subquery_pruning_enabled=TRUE
*._subquery_pruning_mv_enabled=FALSE
*._table_scan_cost_plus_one=TRUE
*._union_rewrite_for_gs='YES_GSET_MVS'
*._unnest_subquery=TRUE
*._use_column_stats_for_function=TRUE
*.audit_file_dest='/u01/app/oracle/admin/dpuli/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/dpuli/controlfile/current.274.800266185','+DATA/dpuli/controlfile/current.273.800266189'#Restore Controlfile
*.core_dump_dest='/u01/app/oracle/diag/rdbms/dpuli/dpuli/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='us.oracle.com'
*.db_name='DPULI'#Reset to original value by RMAN
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4062M
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.log_archive_dest_1='location=+FRA/dpuli/Archive'
*.log_archive_format='Log%s_%t_%r.Arc'
*.log_buffer=5783552# log buffer update
*.memory_target=452M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=1184K
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTBS1'





SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup ;
ORACLE instance started.

Total System Global Area  472887296 bytes
Fixed Size                  1337296 bytes
Variable Size             411043888 bytes
Database Buffers           54525952 bytes
Redo Buffers                5980160 bytes


Database mounted.

create pfile='/tmp/initdupli1.txt' from spfile ;


SQL> archive log list ;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA/dpuli/archive
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1

Oracle DBA

anuj blog Archive