MERHABA

Uzun zamandır düşünüpte hayata geçiremediğim blog dünyasına bu yazı ile merhaba diyorum.  Tasarlarken, düşünürken faydalı,güzel yazılar yazarak, karşılaştığım durumları paylaşarak,  özellikle Türkçe kaynak arayanlara faydalı olmayı planlıyordum. umarım düşündüğüm şeyleri burada hayata geçirebilirim.

Faydalı,sürekli yeni bilgilerin paylaşıldığı bir blog olması dileğiyle,

Merhaba Dünya 🙂

Advertisements

ORA-17628: Oracle error 19505 returned by remote Oracle server

when creatin standby with rman duplicate


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/02/2017 17:20:15
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on t1 channel at 11/02/2017 17:20:15
ORA-17628: Oracle error 19505 returned by remote Oracle serve

erros occured.

durinf duplicate if CONTROL_FILE parameter is empty this error can be trigger.  check for control file parameter on primary side.

 

other reason is convert parameters can be unset.  check on primary side:


SQL> show parameter convert

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string

SQL> alter system set db_file_name_convert='/ats','+ATSPREDATA' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/ats','+ATSPREDATA' scope=spfile;

System altered

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

Total System Global Area 6463787008 bytes
Fixed Size 2233600 bytes
Variable Size 3657435904 bytes
Database Buffers 2785017856 bytes
Redo Buffers 19099648 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameter convert

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /ats, +ATSPREDATA
log_file_name_convert string /ats, +ATSPREDATA

single physical standby install (non ASM, datafile on fileystem)

 

 

0-on primary side
Primary db force logging mode’da olmalı.

 SQL> select force_logging from v$database;
 FOR
 ---
 NO

SQL> alter database force logging;

Database altered.

SQL>

1-on stb side
Sadece oracle software kur.
2- on stb and primary side
Her iki sunucu arasında scp ile dosya kopyalanabildiğini, ssh yapılabildiğini, listener portlarının açık olduğunu test et.
3-on stb side
Db asm için profile dosyası oluştur.
Db profile örneği;


export ORACLE_BASE=/oracle/product
export ORACLE_HOSTNAME=serv01
export ORACLE_UNQNAME=ATSB_ST
export ORACLE_SID=ATSB_N
export ORACLE_HOME=/oracle/product/11.2.0/db
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/bin:/usr/bin:/etc:/opt/bin:/usr/ccs/bin:/usr/local/bin:$PATH; export PATH

 

4-on primary side

Db ORACLE_HOME altındaki Tnsnames.ora dosyasına stb’nin tnsini ekle.


ATSB_ST_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.41.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSB_ST)
)
)

5-on stb side
Db ORACLE_HOME altındaki Tnsnames.ora dosyasına primary’ın tnsini ekle.

ATSBAUY_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.155.6)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSBAUY)
)
)

6-on primary side
ORACLE_HOME/dbs altındaki orapwd dosyasını stb’de aynı path’e kopyala. Orapwd dosya isim formatı orapw[INSTABCE_NAME]


scp 10.6.155.6:/oracle/product/11.2.0/db/dbs/orapwATSBAUY /oracle/product/11.2.0/db/dbs
mv orapwATSBAUY orapwATSB_N

7-on stb side
$ORACLE_HOME/network/admin/listener.ora dosyası düzenlenir. netca ile oluşturulmamışsa aşağıdaki şekilde manule olarak listener oluşturlabilir.

 

listsner.ora için Örnek içerik;

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/product/11.2.0.3/db)
(SID_NAME = ATSB_N)
)
)

 

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.41.2)(PORT = 1521))
)
)

listener’ı kontrol edelim.


{oramigtest:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 25-OCT-2017 17:34:22

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Starting /oracle/product/11.2.0.3/db/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /oracle/product/11.2.0.3/db/network/admin/listener.ora
Log messages written to /oracle/product/diag/tnslsnr/oramigtest/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.6.41.2)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.6.41.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 25-OCT-2017 17:34:22
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/oramigtest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.6.41.2)(PORT=1521)))
Services Summary...
Service "ATSB_N" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
{oramigtest:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 25-OCT-2017 17:34:29

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.6.41.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 25-OCT-2017 17:34:22
Uptime 0 days 0 hr. 0 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/oramigtest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.6.41.2)(PORT=1521)))
Services Summary...
Service "ATSB_N" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
{oramigtest:oracle}/oracle/product/11.2.0.3/db/network/admin/>

 

8-on stb side
ORACLE_HOME/dbs altında pfile oluşturulur. pfile dosyası init[instance_name].ora ile oluşturulur.

 

db_name=ATSBAUY
db_unique_name=ATSB_ST
instance_name=ATSB_N

 

pfile içinde olması tavsiye edilen parametreler;
diagnostic_dest
audit_file_dest
core_dump_dest
db_create_file_dest
db_create_online_log_dest_1
db_create_online_log_dest_2
db_recovery_file_dest
db_recovery_file_dest_size
db_file_name_convert
log_file_name_convert
db_domain
log_archive_config
log_archive_dest_1
log_archive_dest_2
log_archive_dest_state_1
log_archive_dest_state_2
log_archive_format
fal_client
fal_server
sga_target
pga_aggregate_target
(memory_target)
standby_file_management
undo_tablespace

 

9-on primary side
Aşağıdaki parametrelerden olmayanları ekleyin, olanları stb için yeniden düzenleyin
LOG_ARCHIVE_CONFIG
STANDBY_FILE_MANAGEMENT


SQL> alter system set log_archive_config='DG_CONFIG=(ATSBAUY, ATSB_ST)';

System altered.

SQL> alter system set standby_file_management='AUTO';

System altered.

10-on primary side
Primary db de standby log file yoksa ekleyin. Standby log file size online redolog ile aynı olmalı. Standby log files group/sayısı online redolog sayısı/group’tan bir adet fazla olmalı.

SQL>select * from SYS.V_$STANDBY_LOG;
No rows selected.

SQL>select count(*) from v$log;
Count(*)
------
3

SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.
SQL> alter database add standby logfile size 50M;
Database altered.

11-on stb side
Önceki adımlarda hazırlanan pfile ile db nomount modda açılır.

. /home/oracle/profile_new_standby

sqlplus / as sysdba;
startup nomount pifle=’/tmp/pifle.ora’

startup nomount pfile=’$ORACLE_HOME/dbs/pfileATSB_N.ora’

12-on stb side
Listener’ın ilgili db servisini unknown olarak dinlediği görülmelidir.


{oramigtest:oracle}/home/oracle/>lsnrctl stat listener

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 02-NOV-2017 15:00:39

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.6.41.2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 14:11:48
Uptime 7 days 0 hr. 48 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/oramigtest/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.6.41.2)(PORT=1521)))
Services Summary...
Service "ATSB_ST" has 2 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
Instance "ATSB_N", status BLOCKED, has 1 handler(s) for this service...

13-on stb side
Primary db’ye başarılı tnsping atıldığı görülmeli.
Sqlplus’dan sys ile primary’e bağlantı sağlanıp, primary db!den sorgu çekilmeli.
Sys ile bağlanırken sqplus sys/password@STB as sysdb şeklinde parolaile bağlanmak iyi olacakır.


chevron20:/home/oracle#tnsping ATSB_ST_TNS

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 25-OCT-2017 17:42:57

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:

 

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.41.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ATSB_ST)))
OK (0 msec)

14-on primary side
STB db’ye başarılı tnsping atıldığı görülmeli.
Sqlplus’dan sys ile stb’ye bağlantı sağlanıp, stb db’den sorgu çekilmeli.
Sys ile bağlanırken sqplus sys/password@STANDBY as sysdb şeklinde parolaile bağlanmak iyi olacakır.


chevron20:/home/oracle#tnsping ATSB_ST_TNS

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 02-NOV-2017 15:03:15

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:

 

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6.41.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ATSB_ST)))
OK (0 msec)

 

chevron20:/home/oracle#sqlplus sys/syspass@ATSB_ST_TNS as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 2 15:04:23 2017

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

 

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

SQL>

SQL> select INSTANCE_NAME, HOST_NAME from v$instance

INSTANCE_NAME HOST_NAME
---------------- ----------------------------------------------------------------
ATSB_N oramigtest

15-on primary side
Rman duplicate scripti çalıştırılır.


more stb_rman_duplicate.rman
connect target /;
connect auxiliary sys/sysATSBAUYdb@ATSB_ST_TNS;
RUN
{
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux4 DEVICE TYPE DISK;
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
duplicate target database for standby from active database
nofilenamecheck;
release channel aux1;
release channel aux2;
release channel aux3;
release channel aux4;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}

nohup rman cmdfile=stb_duplicate.rman log=stb_duplicate.log &

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

oracle version : 11.2.0.3
OS :AIX

db_name=ATSBAUY
db_unique_name=ATSB_ST
instance_name=ATSB_N
db_domain=”

tns entry in tnsnames.ora;

ATSB_ST_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSB_ST)
)
)

I am creating standby db instance started with nomount mode.

there is just one service created automatically from db_unique_name

SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ATSB_ST

listener.ora:


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/product/11.2.0.3/db)
(SID_NAME = ATSB_N)
)
)

 

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521))
)
)
)

lets start listener


{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 13:55:13

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Starting /oracle/product/11.2.0.3/db/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /oracle/product/11.2.0.3/db/network/admin/listener.ora
Log messages written to /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 13:55:13
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_N" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

listener is not listening for ATSB_ST service despite listenig for instance_name (ATSB_N)

lest open instance (nomount database)


SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size 2230912 bytes
Variable Size 5435819392 bytes
Database Buffers 5234491392 bytes
Redo Buffers 16932864 bytes
SQL>

now lets check for listener status


{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 13:59:36

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 13:55:13
Uptime 0 days 0 hr. 4 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_N" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
Service "ATSB_ST" has 1 instance(s).
Instance "ATSB_N", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

the right service(ATSB_ST) is blocked. when tring to connect with TNS ORA-12528 error occured.


{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>sqlplus /@ATSB_ST_TNS as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 26 14:00:55 2017

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

ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

 

Enter user-name:

try connect via sqlplus after change tnsnames.ora as;

ATSB_ST_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSB_N)
)
)


{arcl_host:oracle}/oracle/product/11.2.0.3/db/dbs/>sqlplus sys/syspass@ATSB_ST_TNS as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 26 14:04:32 2017

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

 

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

we can coonnect to db that listner service is unknown. if we add GLOBAL_DBNAME to listener.ora, listner will listen on ATSB_ST service.

in listner.ora:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ATSB_ST)
(ORACLE_HOME = /oracle/product/11.2.0.3/db)
(SID_NAME = ATSB_N)
)
)

 

change tnsnames.ora for right service name:

ATSB_ST_TNS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ATSB_ST)
)
)

now stop and start listener:


{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl stop

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 14:11:39

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
The command completed successfully
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>
{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 14:11:48

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Starting /oracle/product/11.2.0.3/db/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /oracle/product/11.2.0.3/db/network/admin/listener.ora
Log messages written to /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 14:11:48
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_ST" has 1 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

after a while the listner start to listen on ATSB_ST service;


{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 26-OCT-2017 14:13:02

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date 26-OCT-2017 14:11:48
Uptime 0 days 0 hr. 1 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0.3/db/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/arcl_host/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.100)(PORT=1521)))
Services Summary...
Service "ATSB_ST" has 2 instance(s).
Instance "ATSB_N", status UNKNOWN, has 1 handler(s) for this service...
Instance "ATSB_N", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

now we can connect with new TNS;

{arcl_host:oracle}/oracle/product/11.2.0.3/db/network/admin/>sqlplus sys/syspass@ATSB_ST_TNS as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 26 14:14:30 2017

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

 

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

SQL>

Block Change Tracking BCT

BCT 10.1 ile gelen bir özellik. incremental backuplar sırasında full scan sebebiyle veritabanına yük binmekte. .BCT ile sadece değişen blockların bilgisi tutularak incremental backup sırasında full db scan engellenmiş olur.
BCT  her 4 blok için bir bit kullanır. dolayısıyıla db size 1TB ise yaklaşık 30MB bct file oluşur.backup sırasında BCT file’dan değişen blocklar atanmış bitlerden anlaşılabilir. bu şekilde sadece değişen blocklar okunur.

  • BCT açıldığında CTWR background process’i başlatılır.
  • inc backuplar sırasında BCT file otomatik olarak kullanılır.
  • BCT file size’ı V$BLOCK_CHANGE_TRACKING view’ıdan görülebilir.
  • BCT file size’i  <pre><size of change tracking file> = <# of redo threads> * (# of old backups + 2) * (size of db/250000) <pre/> formülüyle hesaplanabilir.  pratik hesap olarak her 1TB db boyutu için 30MB tarce file oluşur. BCT file default olarak ~11MB civarında oluşur.
  • BCT file db’nin 32 KB’lık datası için 1 bit oluşturur. her bir 32 kb’ye dedike olarak değişimi tutar.
  • BCT file 8 backup geriye dönük değişiklieri tutar. her yeni inc backup sonrasında kendini yedeklemiş olur. en eski 9. backup silinerek size’ın büyümesi engellenir.

ENABLE BCT

1-) TCT’nin disable olduğunu görelim

SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
STATUS      FILENAME
---------   --------- 
DISABLED

2-)BCT file default olarak DB_CREATE_FILE_DEST parametresini gösterdiği lokasyona oluşturulur. DB_CREATE_FILE_DEST ‘nin değeri boş ise $ORACLE_HOME/dbs altına oluşturabilir. $ORACLE_HOME dizinin dolmasını engellemek için DB_CREATE_FILE_DEST parametresini setlkeyip BCT’yi ondan sonra enable etmek gerekir.
!!BCT file RAC veritabanlarında bütün node’ların yazma-okuma yetkisinin olduğu bir lokasyona oluştuulmalıdır. bu nedenle özellikle RAC sistemlerde DB_CREATE_FILE_DEST parametresi ASM’de bir lokasyon olarak seçilmelidir.

SQL> show parameter DB_CREATE_FILE_DEST
NAME                 TYPE         VALUE
 ------------------- ----------- ------------------------------ 
db_create_file_dest  string      +DATA

dilersek BCT file’ın yerini manuel belirleyebiliriz.

SQL> alter database enable block change tracking using file '/u01/app/oracvle/mysid/data/block_change_tracking.dbf';

3-)şimdi BCT’yi enable edebiliriz.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

4-) BCT’nin enable edildiğini kontrol edelim

SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
STATUS     FILENAME 
---------- --------------------------------------------------------------------------------
ENABLED    +DATA/blggtw/changetracking/ctf.425.951492573

!!BCT enable edildikten sonra bct’nin devreye girmesi enable işleminden sonra alınan ilk full(L0) backup ile balşlar. L0’dan sonraki L1 backuplarda süre kısalmaları görülebilir.

5-) backuplar sırasında BCT’nin kullanıldığını v$backup_datafile viewında USED_CHANGE_TRACKING kolonu bakarak görebiliriz. kolon ‘YES’ değerini alımışsa BCT kullanılmaya başlamıştır.

select *
from v$backup_datafile
where USED_CHANGE_TRACKING = 'YES'

Emre BARANSEL’e katkılarından dolayı teşekkürler.

driving_site() hint

remote guery’lerde data join işleminin yapılacağı tarafı belirtmek için driving_site hinti kullanılır.

select /*+ driving_site (r) */
   l.name,
   r.id
from local_table l,  remote_table@rmt_bdlnk r
where l.id=r.id
and l.name like 'A%';

özellikle remote tarftaki tablo büyük ise network trafiğini azaltmak için localdeki az data network ile remote tarafa iletilir ve join işlemi remote tarafta yapılır. özellikle remote taraftaki tablonun büyük olduğu durumlarda performans artışı sağlar.

Uyarı: “CTAS” ve “Insert Into select” sırasında driving_site hinti devre dışı
bırakılır.

database id görüntüleme

find db id
a-)production veritabanında rman bağlantısı yapıldığında görülebilir.

RMAN> connect target /;
connected to target database: RMAN (DBID=1234567890)

b-)rman catalog veritabanında,production veritabnının catalog user’ı ile görülebilir.

		 
[oracle@rman2 ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 5 09:49:06 2016

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


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

SQL> alter session set current_schema=PROD_RMAN_CATALOG;

Session altered.

SQL> select dbid from PROD_RMAN_CATALOG.rc_database;

DBID
----------
1234567890

c-)production veritabanında backup tool’unun loglarında görülebilir.

{host:oracle}/home/oracle/>cd /nsr/aoologs
{host:oracle}/home/oracle/>more daily_orcl1_archive.log

d-)production veritabanında control file auto backup listelendiğinde görüleblir.
conrolfile autobackup on yapılmışsa ve format %F şeklinde ise handle alanında c-dbid-date şeklinde görülebilir.

		
RMAN> list backup of controlfile;

List of Backup Sets
===================
			  
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
65243   Full    82.50M     SBT_TAPE    00:00:06     14-APR-16      
BP Key: 65243   Status: AVAILABLE  Compressed: NO  Tag: TAG20160414T085716
Handle: c-1234567890-20160414-01   Media: UDD08.BST.STG.All.001
Control File Included: Ckp SCN: 12593941618236   Ckp time: 14-APR-16

EXPDP No space left on device

Hello,

During export if error below appears you have to expand or delete files from directory path to extend space.

version:11.2

ORA-19502: write error on file "/oracle/export/table_X_partition_201507.dmp", block number 31475048 (block size=4096)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 31475048
Additional information: -1
ORA-31694: master table "SYS"."SYS_EXPORT_TABLE_09" failed to load/unload
ORA-31644: unable to position to block number 31475304 in dump file "/oracle/export/table_X_partition_201507.dmp"
ORA-19502: write error on file "/oracle/export/table_X_partition_201507.dmp", block number 31475304 (block size=4096)
ORA-27072: File I/O error
Additional information: 4
Additional information: 31475304
Additional information: 131072

EXPDP No space left on device

Merhaba,

export sırasında aşağıdaki hata alındığında ilgili directory dizininde alan açlıması gerekiyor.

version:11.2

ORA-19502: write error on file "/oracle/export/table_X_partition_201507.dmp", block number 31475048 (block size=4096)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 31475048
Additional information: -1
ORA-31694: master table "SYS"."SYS_EXPORT_TABLE_09" failed to load/unload
ORA-31644: unable to position to block number 31475304 in dump file "/oracle/export/table_X_partition_201507.dmp"
ORA-19502: write error on file "/oracle/export/table_X_partition_201507.dmp", block number 31475304 (block size=4096)
ORA-27072: File I/O error
Additional information: 4
Additional information: 31475304
Additional information: 131072

ORA-00338: log 3 of thread 2 is more recent than control file

–on alert.log ORA-00338 alerts appears.

Wed Apr 19 00:53:12 2017
Errors in file /dblog/diag/rdbms/oyh/OYH2/trace/OYH2_nsa2_12586.trc:
ORA-00338: log 3 of thread 2 is more recent than control file
ORA-00312: online log 3 thread 2: '+OYHDATA/oyh/onlinelog/group_3.301.852327331'
Thread 2 advanced to log sequence 314189 (LGWR switch)
Current log# 3 seq# 314189 mem# 0: +OYHDATA/oyh/onlinelog/group_3.301.852327331
ORA-00338 log string of thread string is more recent than controlfile


Cause: The control file change sequence number in the log file is greater than the number in the control file. This implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop happening without correcting the real problem. Every attempt to open the database will advance the control file change sequence number until it is great enough.

Action: Use the current control file or do backup control file recovery to make the control file current. Be sure to follow all restrictions on doing a backup control file recovery.

–as above description,  ORA-00338 error occur also after frequently log switch. advised log switch frequency is 3 or 4 times per an hour. if average database’s redolog switch is more than 3-4 times per an hour, size of redologs have to increase. redo log switch frequency can check with this sql:

SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
count(1) "Total",
SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23",
decode(trunc(first_time),
trunc(sysdate), round(count(1) / (24 * to_number(to_char(sysdate, 'sssss')+1) / 86400),2),
round(count(1) / 24, 2)) "Avg"
from GV$log_history
where inst_id = 1
and thread# = inst_id
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1

 

 

ORA-00338: log 3 of thread 2 is more recent than control file

–alert.log’da ORA-00338, ORA-00312 hatalarının oluşması

Wed Apr 19 00:53:12 2017
Errors in file /dblog/diag/rdbms/oyh/OYH2/trace/OYH2_nsa2_12586.trc:
ORA-00338: log 3 of thread 2 is more recent than control file
ORA-00312: online log 3 thread 2: '+OYHDATA/oyh/onlinelog/group_3.301.852327331'
Thread 2 advanced to log sequence 314189 (LGWR switch)
Current log# 3 seq# 314189 mem# 0: +OYHDATA/oyh/onlinelog/group_3.301.852327331
ORA-00338 log string of thread string is more recent than controlfile


Cause: The control file change sequence number in the log file is greater than the number in the control file. This implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop happening without correcting the real problem. Every attempt to open the database will advance the control file change sequence number until it is great enough.


Action: Use the current control file or do backup control file recovery to make the control file current. Be sure to follow all restrictions on doing a backup control file recovery.

–bu sorun redolog switch sayısı çok yüksek olduğu durumnlarda da alınır.
veritabanında yaşanan sorun redolog switch sayısının çok fazla olması.
tavsiye edilen redolog switch sayısı saatte 3-4 adet.
son 1 haftalık redolog switch sayısını saatlik değişimini görmek için aşağıdak sql kullanılabilir.

SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
count(1) "Total",
SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0",
SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1",
SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2",
SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3",
SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4",
SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5",
SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6",
SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23",
decode(trunc(first_time),
trunc(sysdate), round(count(1) / (24 * to_number(to_char(sysdate, 'sssss')+1) / 86400),2),
round(count(1) / 24, 2)) "Avg"
from GV$log_history
where inst_id = 1
and thread# = inst_id
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1

–redolog switch sayısının saatlik ortalaması 3-4 ‘ün üzerindeyse redolog boyutunu arttırmak ORA-00338 hatasını engelleyecektir.