Archive for the ‘TEChNoLoGY’ Category

ServiceAliasException: Could not initialize Service Alias: TNS-04404: no error

June 21st, 2022, posted in Oracle Queries
Share

I was getting this TNS error while creating Listner with “netca”, here is the screenshot for the same.

Listner,TNS-04404,ServiceAliasException,Service Alias

 

 

D:\app\db193\bin>netca

Oracle Net Services Configuration:
Configuring Listener:LISTENERCBT
ServiceAliasException: Could not initialize Service Alias: TNS-04404: no error
  caused by: oracle.net.config.ConfigException: TNS-04414: File error
  caused by: TNS-04605: Invalid syntax error: Unexpected char or LITERAL "IEPDB1" before or at  IEPDB1 = (
  Error in file D:\TNS_ADMIN\tnsnames.ora

This is my windows machine and creating new listner for my newly created CDB. DB version is 19.3

As above error shows that I have some issue with my tnsnames.ora with some of my TNS Entry with IEPDB1.

In my environment TNS_ADMIN environment variable is set and it is pointing to my D:\TNS_ADMIN\tnsnames.ora

Here I found some junk characters near IEPDB1 TNS entry, I removed it and save the file and ran netca again…
Now, all went good..
It solved my problem !!!

Share

Change Solaris Zones Configurations Online

May 29th, 2022, posted in Solaris
Share
I assume you are aware that Solaris Zones are one of the most valuable features of Solaris since years. In this post I focus on the “Live Zone Reconfiguration” feature available since
Solaris 11.2 for Solaris Zones and since Solaris 11.3 for Kernel Zones. CPU pools, filesystems, network and disk configurations can be changed while Solaris Zones are running.

1. Limit CPU usage of a Solaris Zone using dedicated-cpu

By default Solaris Zones share the CPUs with the global and all other local Zones.
Our sample Zone currently uses 16 virtual CPUs.

# zlogin v0131 psrinfo | wc -l
16

 

We can now assign 4 dedicated virtual CPUs to be used by this Zone only.
# zonecfg -z v0131 -r "add dedicated-cpu; set ncpus=4; end"

zone 'v0131': Checking: Adding dedicated-cpu

zone 'v0131': Applying the changes

# zlogin v0131 psrinfo | wc -l
4

 

The “zonecfg -r” changes the configuration of the running Zone only.
Make sure to run the command once again to make the configuration persistent for the next Zone reboot.
# zonecfg -z v0131 "add dedicated-cpu; set ncpus=4; end"

 

2. Create and mount an additional ZFS filesystem
# zfs create v0131_data/myapp

# zonecfg -z v0131 -r "add fs; set type=zfs; set dir=/myapp; set special=v0131_data/myapp; end"

zone 'v0131': Checking: Mounting fs dir=/myapp

zone 'v0131': Applying the changes

# zlogin v0131 mount | grep myapp

/myapp on /myapp read/write/setuid/devices/rstchown/nonbmand/exec/xattr/atime/zone=v0131/nozonemod/sharezone=4/dev=d50045 on Fri Jun 10 11:56:19 2016

 

And make it persistent
# zonecfg -z v0131 "add fs; set type=zfs; set dir=/myapp; set special=v0131_data/myapp; end"
Adding network interfaces and disk devices are similar to the samples above.
Share

Oracle Multitenant (PDB Level) Initialization Parameters

May 22nd, 2022, posted in Oracle
Share

Oracle Multitenant (PDB Level) Initialization Parameters

I was asked what are those parameters that can be modifiable at PDB level. And my simple answer is to check v$parameters view and look for ISPDB_MODIFIABLE column have TRUE value than it is modifiable otherwise not.

I have just extract the same here, some of you might not have DBA privileges, specially developers don’t have rights to view v$ views.
SrNo. Init Parameters Description Default Value
1 _optimizer_gather_stats_on_conventional_dml optimizer online stats gathering for conventional DML TRUE
2 _optimizer_use_stats_on_conventional_dml use optimizer statistics gathered for conventional DML TRUE
3 _serial_direct_read enable direct read in serial auto
4 adg_account_info_tracking ADG user account info tracked in standby(LOCAL) or in Primary(GLOBAL) LOCAL
5 allow_rowid_column_type Allow creation of rowid column FALSE
6 approx_for_aggregation Replace exact aggregation with approximate aggregation FALSE
7 approx_for_count_distinct Replace count distinct with approx_count_distinct FALSE
8 approx_for_percentile Replace percentile_* with approx_percentile none
9 aq_tm_processes number of AQ Time Managers to start 1
10 asm_diskstring disk set locations for discovery
11 awr_pdb_autoflush_enabled Enable/Disable AWR automatic PDB flushing FALSE
12 bitmap_merge_area_size maximum memory allow for BITMAP MERGE 1048576
13 blank_trimming blank trimming semantics parameter FALSE
14 cell_offload_compaction Cell packet compaction strategy ADAPTIVE
15 cell_offload_decryption enable SQL processing offload of encrypted data to cells TRUE
16 cell_offload_parameters Additional cell offload parameters NONE
17 cell_offload_plan_display Cell offload explain plan display AUTO
18 cell_offload_processing enable SQL processing offload to cells TRUE
19 cell_offloadgroup_name Set the offload group name
20 client_result_cache_lag client result cache maximum lag in milliseconds 3000
21 client_result_cache_size client result cache max size in bytes 0
22 client_statistics_level Client Statistics Level TYPICAL
23 commit_logging transaction commit log write behaviour
24 commit_point_strength Bias this node has toward not preparing in a two-phase commit 1
25 commit_wait transaction commit log wait behaviour
26 commit_write transaction commit log write behaviour
27 common_user_prefix Enforce restriction on a prefix of a Common User/Role/Profile name NONE
28 container_data which containers should data be returned from? ALL
29 containers_parallel_degree Parallel degree for a CONTAINERS() query 65535
30 cpu_count maximum number of CPUs 0
31 cpu_min_count minimum number of CPUs required
32 create_bitmap_area_size size of create bitmap buffer for bitmap index 8388608
33 create_stored_outlines create stored outlines for DML statements
34 cursor_bind_capture_destination Allowed destination for captured bind variables memory+disk
35 cursor_invalidation default for DDL cursor invalidation semantics IMMEDIATE
36 cursor_sharing cursor sharing mode EXACT
37 db_block_checking header checking and data and index block checking FALSE
38 db_cache_size Size of DEFAULT buffer pool for standard block size buffers 0
39 db_create_file_dest default database location NONE
40 db_create_online_log_dest_1 online log/controlfile destination #1 NONE
41 db_create_online_log_dest_2 online log/controlfile destination #2 NONE
42 db_create_online_log_dest_3 online log/controlfile destination #3 NONE
43 db_create_online_log_dest_4 online log/controlfile destination #4 NONE
44 db_create_online_log_dest_5 online log/controlfile destination #5 NONE
45 db_domain directory part of global database name stored with CREATE DATABASE
46 db_file_multiblock_read_count db block to be read each IO 0
47 db_files max allowable # db files 200
48 db_index_compression_inheritance options for table or tablespace level compression inheritance NONE
49 db_performance_profile Database performance category
50 db_securefile permit securefile storage during lob creation PERMITTED
51 db_unrecoverable_scn_tracking Track nologging SCN in controlfile TRUE
52 ddl_lock_timeout timeout to restrict the time that ddls wait for dml lock 0
53 default_sharing Default sharing clause metadata
54 deferred_segment_creation defer segment creation to first insert TRUE
55 dst_upgrade_insert_conv Enables/Disables internal conversions during DST upgrade TRUE
56 enable_automatic_maintenance_pdb Enable/Disable Automated Maintenance for Non-Root PDB TRUE
57 enable_ddl_logging enable ddl logging FALSE
58 encrypt_new_tablespaces whether to encrypt newly created tablespaces CLOUD_ONLY
59 fixed_date fixed SYSDATE value NONE
60 forward_listener forward listener
61 global_names enforce that database links have same name as remote database FALSE
62 heat_map ILM Heatmap Tracking OFF
63 ignore_session_set_param_errors Ignore errors during alter session param set
64 inmemory_automatic_level Enable Automatic In-Memory management OFF
65 inmemory_clause_default Default in-memory clause for new tables NONE
66 inmemory_expressions_usage Controls which In-Memory Expressions are populated in-memory ENABLE
67 inmemory_force Force tables to be in-memory or not DEFAULT
68 inmemory_optimized_arithmetic Controls whether or not DSBs are stored in-memory DISABLE
69 inmemory_prefer_xmem_memcompress Prefer to store tables with given memcompress levels in xmem NONE
70 inmemory_prefer_xmem_priority Prefer to store tables with given priority levels in xmem NONE
71 inmemory_query Specifies whether in-memory queries are allowed ENABLE
72 inmemory_size size in bytes of in-memory area 0
73 inmemory_virtual_columns Controls which user-defined virtual columns are stored in-memory MANUAL
74 inmemory_xmem_size size in bytes of in-memory xmem area 0
75 java_jit_enabled Java VM JIT enabled TRUE
76 job_queue_processes maximum number of job queue slave processes 4000
77 ldap_directory_access RDBMS’s LDAP access option NONE
78 ldap_directory_sysauth OID usage parameter no
79 listener_networks listener registration networks
80 lob_signature_enable enable lob signature FALSE
81 local_listener local listener
82 log_archive_min_succeed_dest minimum number of archive destinations that must succeed 1
83 long_module_action Use longer module and action TRUE
84 max_datapump_jobs_per_pdb maximum number of concurrent Data Pump Jobs per PDB 100
85 max_datapump_parallel_per_job maximum number of parallel processes per Data Pump Job 50
86 max_dump_file_size Maximum size (in bytes) of dump file unlimited
87 max_idle_blocker_time maximum idle time for a blocking session in minutes 0
88 max_idle_time maximum session idle time in minutes 0
89 max_iops MAX IO per second 0
90 max_mbps MAX MB per second 0
91 max_pdbs max number of pdbs allowed in CDB or Application ROOT 4098
92 max_string_size controls maximum size of VARCHAR2, NVARCHAR2, and RAW types in SQL STANDARD
93 multishard_query_data_consistency consistency setting for multishard queries strong
94 multishard_query_partial_results enable partial results for multishard queries not allowed
95 nls_calendar NLS calendar system name NONE
96 nls_comp NLS comparison BINARY
97 nls_currency NLS local currency symbol NONE
98 nls_date_format NLS Oracle date format NONE
99 nls_date_language NLS date language name NONE
100 nls_dual_currency Dual currency symbol NONE
101 nls_iso_currency NLS ISO currency territory name NONE
102 nls_language NLS language name AMERICAN
103 nls_length_semantics create columns using byte or char semantics by default BYTE
104 nls_nchar_conv_excp NLS raise an exception instead of allowing implicit conversion FALSE
105 nls_numeric_characters NLS numeric characters NONE
106 nls_sort NLS linguistic definition name NONE
107 nls_territory NLS territory name AMERICA
108 nls_time_format time format NONE
109 nls_time_tz_format time with timezone format NONE
110 nls_timestamp_format time stamp format NONE
111 nls_timestamp_tz_format timestamp with timezone format NONE
112 object_cache_max_size_percent percentage of maximum size over optimal of the user session’s object cache 10
113 object_cache_optimal_size optimal size of the user session’s object cache in bytes 10240000
114 olap_page_pool_size size of the olap page pool in bytes 0
115 open_cursors max # cursors per session 50
116 open_links max # open links per session 4
117 optimizer_adaptive_plans controls all types of adaptive plans TRUE
118 optimizer_adaptive_reporting_only use reporting-only mode for adaptive optimizations FALSE
119 optimizer_adaptive_statistics controls all types of adaptive statistics FALSE
120 optimizer_capture_sql_plan_baselines automatic capture of SQL plan baselines for repeatable statements FALSE
121 optimizer_dynamic_sampling optimizer dynamic sampling 2
122 optimizer_features_enable optimizer plan compatibility parameter 19.1.0
123 optimizer_ignore_hints enables the embedded hints to be ignored FALSE
124 optimizer_ignore_parallel_hints enables embedded parallel hints to be ignored FALSE
125 optimizer_index_caching optimizer percent index caching 0
126 optimizer_index_cost_adj optimizer index cost adjustment 100
127 optimizer_inmemory_aware optimizer in-memory columnar awareness TRUE
128 optimizer_mode optimizer mode all_rows
129 optimizer_secure_view_merging optimizer secure view merging and predicate pushdown/movearound TRUE
130 optimizer_use_invisible_indexes Usage of invisible indexes (TRUE/FALSE) FALSE
131 optimizer_use_pending_statistics Control whether to use optimizer pending statistics FALSE
132 optimizer_use_sql_plan_baselines use of SQL plan baselines for captured sql statements TRUE
133 parallel_degree_limit limit placed on degree of parallelism CPU
134 parallel_degree_policy policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO/ADAPTIVE) MANUAL
135 parallel_force_local force single instance execution FALSE
136 parallel_instance_group instance group to use for all parallel operations NONE
137 parallel_max_servers maximum parallel query servers per instance
138 parallel_min_degree controls the minimum DOP computed by Auto DOP 1
139 parallel_min_time_threshold threshold above which a plan is a candidate for parallelization (in seconds) AUTO
140 parallel_servers_target instance target in terms of number of parallel servers 0
141 pdb_file_name_convert PDB file name convert patterns and strings for create cdb/pdb
142 pdb_lockdown pluggable database lockdown profile
143 pdb_os_credential pluggable database OS credential to bind
144 pdb_template PDB template
145 pga_aggregate_limit limit of aggregate PGA memory for the instance or PDB 0
146 pga_aggregate_target Target size for the aggregate PGA memory consumed by the instance 0
147 plscope_settings plscope_settings controls the compile time collection, cross reference, and stor age of PL/SQL source code identifier and SQL statement data IDENTIFIERS:NONE
148 plsql_ccflags PL/SQL ccflags NONE
149 plsql_code_type PL/SQL code-type INTERPRETED
150 plsql_debug PL/SQL debug FALSE
151 plsql_optimize_level PL/SQL optimize level 2
152 plsql_v2_compatibility PL/SQL version 2.x compatibility flag FALSE
153 plsql_warnings PL/SQL compiler warnings settings NONE
154 private_temp_table_prefix Private temporary table prefix ORA$PTT_
155 query_rewrite_enabled allow rewrite of queries using materialized views if enabled TRUE
156 query_rewrite_integrity perform rewrite using materialized views with desired integrity enforced
157 recyclebin recyclebin processing on
158 remote_dependencies_mode remote-procedure-call dependencies mode parameter timestamp
159 remote_listener remote listener
160 remote_recovery_file_dest default remote database recovery file location for refresh/relocate NONE
161 resource_limit master switch for resource limit TRUE
162 resource_manager_plan resource mgr top plan
163 result_cache_max_result maximum result size as percent of cache size 5
164 result_cache_max_size maximum amount of memory to be used by the cache 1
165 result_cache_mode result cache operator usage mode MANUAL
166 result_cache_remote_expiration maximum life time (min) for any result using a remote object 0
167 resumable_timeout set resumable_timeout 0
168 rollback_segments undo segment list
169 scheduler_follow_pdbtz Make scheduler objects follow PDB TZ FALSE
170 session_cached_cursors Number of cursors to cache in a session. 50
171 sessions user and system sessions
172 sga_min_size Minimum, guaranteed size of PDB’s SGA 0
173 sga_target Target size of SGA 0
174 shadow_core_dump Core Size for Shadow Processes partial
175 shared_pool_size size in bytes of shared pool 134217728
176 shared_servers number of shared servers to start up 4294967294
177 shrd_dupl_table_refresh_rate duplicated table refresh rate (in seconds) 60
178 skip_unusable_indexes skip unusable indexes if set to TRUE TRUE
179 smtp_out_server utl_smtp server and port configuration parameter
180 sort_area_retained_size size of in-memory sort work area retained between fetch calls 0
181 sort_area_size size of in-memory sort work area 65536
182 spatial_vector_acceleration enable spatial vector acceleration FALSE
183 sql_trace enable SQL trace FALSE
184 sql92_security require select privilege for searched update/delete TRUE
185 sqltune_category Category qualifier for applying hintsets DEFAULT
186 star_transformation_enabled enable the use of star transformation FALSE
187 statistics_level statistics level TYPICAL
188 tde_configuration Per-PDB configuration for Transparent Data Encryption NONE
189 temp_undo_enabled is temporary undo enabled FALSE
190 timed_os_statistics internal os statistic gathering interval in seconds 0
191 timed_statistics maintain internal timing statistics FALSE
192 undo_management instance runs in SMU mode if TRUE, else in RBU mode AUTO
193 undo_retention undo retention in seconds 900
194 undo_tablespace use/switch undo tablespace NONE
195 unified_audit_systemlog Syslog facility and level for Unified Audit
196 workarea_size_policy policy used to size SQL working areas (MANUAL/AUTO) AUTO
197 xml_db_events are XML DB events enabled enable

I hope, above will help you to understand what you can change at PDB level..

Share

Last Command Examples For Linux And Unix

May 8th, 2022, posted in Solaris
Share

How to find out last logins of users and times informations on Linux/Unix-like operating systems ?

You need to use the last command to show who has recently used the server and logged in and out date/time.

 

The last command reads listing of last logged in users from the system file called /var/log/wtmp or the file designated by the -f options.

Purpose

To find out when a particular user last logged in to the Linux or Unix server.

Syntax

The basic syntax is:

last
last [userNameHere] last [tty] last [options] [userNameHere]

If no options provided last command displays a list of all users logged in (and out) since /var/log/wtmp file was created. You can filter out results by supplying names of users and tty’s to show only those entries matching the username/tty.

last command examples

To find out who has recently logged in and out on your server, type:
$ last
Sample outputs:

root     pts/1        10.1.6.120       Tue Jan 28 05:59   still logged in   
root     pts/0        10.1.6.120       Tue Jan 28 04:08   still logged in   
root     pts/0        10.1.6.120       Sat Jan 25 06:33 - 08:55  (02:22)    
root     pts/1        10.1.6.120       Thu Jan 23 14:47 - 14:51  (00:03)    
root     pts/0        10.1.6.120       Thu Jan 23 13:02 - 14:51  (01:48)    
root     pts/0        10.1.6.120       Tue Jan  7 12:02 - 12:38  (00:35)    
 
wtmp begins Tue Jan  7 12:02:54 2014

You can specifies a file to search other than /var/log/wtmp using -f option. For example, search /nas/server/webserver/.log/wtmp:
$ last -f /nas/server/webserver/.log/wtmp
last -f /nas/server/webserver/.log/wtmp userNameHere

List all users last logged in/out time

last command searches back through the file /var/log/wtmp file and the output may go back to several months. Just use the less command or more command as follows to display output one screen at a time:
$ last | more
last | less

List a particular user last logged in

To find out when user vivek last logged in, type:
$ last vivek
$ last vivek | less
$ last vivek | grep 'Thu Jan 23'


Sample outputs:

Fig. 01 Displaying out when user vivek last logged in on server

Fig. 01 Displaying out when user vivek last logged in on server

Share

Duplicate Database from RMAN Backup – Oracle

April 10th, 2022, posted in Oracle Queries
Share

Duplicate Database from RMAN Backup


Seven Steps for Duplicate Database :

Step 1) RMNA Backup (Prerequisites)

Step 2) Create Password File

Step 3) Create Directory Structure

Step 4) Create Pfile

Step 5) Start Database in nomount Stage

Step 6) Run Duplicate Database Command

Step 7) Verify Duplicate Database

 

Step 1 RMAN Full database backup required (Prerequisites)

If database is in non archivelog mode then Shutdown the database and open in mount stage then take database backup i.e.RMAN > backup database;

If database is in archivelog mode use command i.e.  RMAN > backup database plus archivelog;

Backup Database using following command

Backup Database

-bash-4.1$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Mar 11 12:19:23 2016

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

RMAN> connect target sys/[email protected]

connected to target database: ORCL (DBID=1611723298)

RMAN>

RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN> backup database plus archivelog;



Step 2 Create a password file for the duplicate instance.

Copy Source database password file and rename it to new database password file

Go to directory: /mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/

$ cp orapworcl orapwduporcl





Step 3 Create directory structure for Duplicate Database

Login with user db1212

Following directories need to be created

–          DupOrcl

–          Oradata

–          Fast_recovery_area

–          Diag

–          Adump

Following are sample commands

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/adump

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/oradata

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/fast_recovery_area

-bash-4.1$ mkdir /mnt/devops_0/DupDB/DupOrcl/diag

-bash-4.1$





Step 4 Create Pfile with few parameters.

Login with db1212

Create Pfile with following parameters

Go to directory: /mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/

Parameters as below for initduporcl.ora,

*.audit_file_dest='/mnt/devops_0/DupDB/DupOrcl/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.db_block_size=8192

*.db_create_file_dest='/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl'

*.db_name='duporcl'

*.db_recovery_file_dest='/mnt/devops_0/DupDB/DupOrcl/fast_recovery_area'

*.db_recovery_file_dest_size=4560m

*.diagnostic_dest='/mnt/devops_0/DupDB/DupOrcl/diag'

*.undo_tablespace='UNDOTBS1'





Step 5 Start Database in nomount stage.

-bash-4.1$ export ORACLE_SID=duporcl

-bash-4.1$

-bash-4.1$ echo $ORACLE_SID

duporcl

-bash-4.1$

SQL> startup nomount

ORACLE instance started.

Total System Global Area  222298112 bytes

Fixed Size                  2922760 bytes

Variable Size             163579640 bytes

Database Buffers           50331648 bytes

Redo Buffers                5464064 bytes

SQL>

SQL> exit




Step 6 Run Duplicate Database command.
$ rman auxiliary /

$ DUPLICATE DATABASE TO duporcl

SPFILE

set control_files='/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'

set DB_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'

set LOG_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'

BACKUP LOCATION '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW'

;






Following is the log for above command for future references.

-bash-4.1$ rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Mar 11 12:04:18 2016

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

connected to auxiliary database: DUPORCL (not mounted)

RMAN> DUPLICATE DATABASE TO duporcl

SPFILE

set control_files='/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'

set DB_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'

set LOG_FILE_NAME_CONVERT='/mnt/devops_0/OrclNew/oradata/orclnew/','/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'

BACKUP LOCATION '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW'

;

Starting Duplicate Db at 11-MAR-16

contents of Memory Script:

{

   restore clone spfile to  '/mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/spfileduporcl.ora' from

 '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/autobackup/2016_03_10/o1_mf_s_906121024_cg2639ch_.bkp';

   sql clone "alter system set spfile= ''/mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/spfileduporcl.ora''";

}

executing Memory Script

Starting restore at 11-MAR-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=13 device type=DISK

channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/autobackup/2016_03_10/o1_mf_s_906121024_cg2639ch_.bkp

channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 11-MAR-16

sql statement: alter system set spfile= ''/mnt/devops_0/Oracle/db1212/product/12.1.0.2/db_1/dbs/spfileduporcl.ora''

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''DUPORCL'' comment=

 ''duplicate'' scope=spfile";

   sql clone "alter system set  control_files =

 ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'' comment=

 '''' scope=spfile";

   sql clone "alter system set  db_file_name_convert =

 ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment=

 '''' scope=spfile";

   sql clone "alter system set  LOG_FILE_NAME_CONVERT =

 ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment=

 '''' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

sql statement: alter system set  db_name =  ''DUPORCL'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  control_files =  ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment= '''' scope=spfile

sql statement: alter system set  LOG_FILE_NAME_CONVERT =  ''/mnt/devops_0/OrclNew/oradata/orclnew/'', ''/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes

Variable Size                713031784 bytes

Database Buffers             352321536 bytes

Redo Buffers                   5455872 bytes

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''ORCLNEW'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''DUPORCL'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   restore clone primary controlfile from  '/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/autobackup/2016_03_10/o1_mf_s_906121024_cg2639ch_.bkp';

   alter clone database mount;

}

executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes

Variable Size                713031784 bytes

Database Buffers             352321536 bytes

Redo Buffers                   5455872 bytes

Starting restore at 11-MAR-16

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:01

output file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/control01.ctl

Finished restore at 11-MAR-16

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  1804148;

   set newname for datafile  1 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf";

   set newname for datafile  3 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf";

   set newname for datafile  4 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf";

   set newname for datafile  5 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf";

   set newname for datafile  6 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf";

   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 11-MAR-16

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 /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_nnndf_TAG20160310T121557_cg2615wf_.bkp

channel ORA_AUX_DISK_1: piece handle=/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_nnndf_TAG20160310T121557_cg2615wf_.bkp tag=TAG20160310T121557

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05

Finished restore at 11-MAR-16

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=9 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=10 STAMP=906206847 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf

contents of Memory Script:

{

   set until scn  1804148;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 11-MAR-16

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=11

channel ORA_AUX_DISK_1: reading from backup piece /mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_annnn_TAG20160310T121703_cg2637go_.bkp

channel ORA_AUX_DISK_1: piece handle=/mnt/devops_0/OrclNew/fast_recovery_area/ORCLNEW/backupset/2016_03_10/o1_mf_annnn_TAG20160310T121703_cg2637go_.bkp tag=TAG20160310T121703

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/mnt/devops_0/OrclNew/Archives/1_11_906113933.dbf thread=1 sequence=11

channel clone_default: deleting archived log(s)

archived log file name=/mnt/devops_0/OrclNew/Archives/1_11_906113933.dbf RECID=1 STAMP=906206849

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

Finished recover at 11-MAR-16

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes

Variable Size                713031784 bytes

Database Buffers             352321536 bytes

Redo Buffers                   5455872 bytes

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''DUPORCL'' comment=

 ''Reset to original value by RMAN'' scope=spfile";

   sql clone "alter system reset  db_unique_name scope=spfile";

}

executing Memory Script

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

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes

Variable Size                713031784 bytes

Database Buffers             352321536 bytes

Redo Buffers                   5455872 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPORCL" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP   1 ( '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/redo01.log' ) SIZE 50 M  REUSE,

  GROUP   2 ( '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/redo02.log' ) SIZE 50 M  REUSE,

  GROUP   3 ( '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/redo03.log' ) SIZE 50 M  REUSE

 DATAFILE

  '/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/system01.dbf'

 CHARACTER SET AL32UTF8

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/temp01.dbf";

   switch clone tempfile all;

   catalog clone datafilecopy  "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf",

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf",

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf",

 "/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf RECID=1 STAMP=906206886

cataloged datafile copy

datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf RECID=2 STAMP=906206886

cataloged datafile copy

datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf RECID=3 STAMP=906206886

cataloged datafile copy

datafile copy file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf RECID=4 STAMP=906206886

datafile 3 switched to datafile copy

input datafile copy RECID=1 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=2 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/undotbs01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=3 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=4 STAMP=906206886 file name=/mnt/devops_0/DupDB/DupOrcl/oradata/duporcl/users01.dbf

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 11-MAR-16




Step 7 Check Duplicate Database Status

-bash-4.1$ ps -ef | grep pmon

db1212    8550     1  0 Mar09 ?        00:00:12 ora_pmon_orcl12c

db1212   32132     1  0 12:07 ?        00:00:00 ora_pmon_duporcl

 -bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 11 12:09:06 2016

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, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

——— ——————–

DUPORCL   READ WRITE

Share