Forum Stats

  • 3,784,366 Users
  • 2,254,929 Discussions
  • 7,880,792 Comments

Discussions

Listener Configuration Standby ORA-12514 TNS:listener does not currently know of service requested..

1854985
1854985 Member Posts: 50
edited Jul 14, 2014 11:18PM in Data Guard

Hello all, me again

Now I have a problem with the configuration of the listener for the stnadby database.

Oracle 12c RAC, On node dbb-g60-01, Instance bsrg60_01. I also attached (tail ) the init.ora for the Stanby node.

I made this:

linstener.ora 

SID_LIST_LISTENER_BSRG60  =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = bsrg60)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_bsr )
      (SID_NAME = bsrg60_1)
    )
 (SID_DESC =
     (GLOBAL_DBNAME = bsrg60)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_bsr)
      (SID_NAME = bsrg60_2)
    )

tnsnames.ora:

############################################################  
#                       Prod n15                           #      
############################################################  

bsr =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbb-n15-01)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbb-n15-02)(PORT = 1521))
    (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = bsr)))
)

############################################################  
#                       Standby                            #      
############################################################  

bsrg60 =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbb-g60-01)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbb-g60-02)(PORT = 1521))
    (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = bsrg60)))
)

############################################################  
#                       far_sync                                       #      
############################################################  

far_sync_n15 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbb-n15-03)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = far_sync)
    )
  )

lsnrrctl say:

 lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 08-JUL-2014 11:41:18

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                08-JUL-2014 11:39:11
Uptime                    0 days 0 hr. 2 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/dbb-g60-01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbb-g60-01)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "bsr" has 1 instance(s).
  Instance "bsrg60_1", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

But

[[email protected] ~]$ sqlplus sys/[email protected]  as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Di Jul 8 11:46:29 2014

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

ERROR:
ORA-12514: TNS: Listener kann in Connect-Deskriptor angegebenen Service aktuell
nicht auflösen

Enter user-name:

What is wron ? Any idea?

Thanks 

my init.ora for this standby:

bsrg601.__data_transfer_cache_size=0
*.__data_transfer_cache_size=0
bsrg60_1.__data_transfer_cache_size=0
bsrg601.__db_cache_size=103347650560
*.__db_cache_size=78G
bsrg60_1.__db_cache_size=103347650560
bsrg601.__java_pool_size=1879048192
*.__java_pool_size=1792M
bsrg60_1.__java_pool_size=1879048192
bsrg601.__large_pool_size=1879048192
*.__large_pool_size=1792M
bsrg60_1.__large_pool_size=1879048192
*.__oracle_base='/u01/app/oracle'# ORACLE_BASE set from environment
bsrg601.__pga_aggregate_target=31675383808
*.__pga_aggregate_target=30208M
bsrg60_1.__pga_aggregate_target=31675383808
bsrg601.__sga_target=115964116992
*.__sga_target=90368M
bsrg60_1.__sga_target=115964116992
bsrg601.__shared_io_pool_size=536870912
*.__shared_io_pool_size=512M
bsrg60_1.__shared_io_pool_size=536870912
bsrg601.__shared_pool_size=7516192768
*.__shared_pool_size=5888M
bsrg60_1.__shared_pool_size=7516192768
bsrg601.__streams_pool_size=0
*.__streams_pool_size=0
bsrg60_1.__streams_pool_size=0
*._adaptive_window_consolidator_enabled=TRUE
*._aggregation_optimization_settings=0
*._always_anti_join='CHOOSE'
*._always_semi_join='CHOOSE'
*._and_pruning_enabled=TRUE
*._array_cdb_view_enabled=TRUE
*._b_tree_bitmap_plans=TRUE
*._bloom_filter_enabled=TRUE
*._bloom_folding_enabled=TRUE
*._bloom_pruning_enabled=TRUE
*._common_data_view_enabled=TRUE
*._complex_view_merging=TRUE
*._compression_compatibility='12.1.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
*._high_priority_processes='LMS*|VKTM|LM*|LCK0|GCR*|DIAG'
*._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_adaptive_plans=TRUE
*._optimizer_ansi_join_lateral_enhance=TRUE
*._optimizer_ansi_rearchitecture=TRUE
*._optimizer_batch_table_access_by_rowid=TRUE
*._optimizer_better_inlist_costing='ALL'
*._optimizer_cbqt_no_size_restriction=TRUE
*._optimizer_cluster_by_rowid=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_cube_join_enabled=TRUE
*._optimizer_dim_subq_join_sel=TRUE
*._optimizer_distinct_agg_transform=TRUE
*._optimizer_distinct_elimination=TRUE
*._optimizer_distinct_placement=TRUE
*._optimizer_dsdir_usage_control=126
*._optimizer_eliminate_filtering_join=TRUE
*._optimizer_enable_density_improvements=TRUE
*._optimizer_enable_extended_stats=TRUE
*._optimizer_enable_table_lookup_by_nl=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=192
*._optimizer_false_filter_pred_pullup=TRUE
*._optimizer_fast_access_pred_analysis=TRUE
*._optimizer_fast_pred_transitivity=TRUE
*._optimizer_filter_pred_pullup=TRUE
*._optimizer_fkr_index_cost_bias=10
*._optimizer_full_outer_join_to_outer=TRUE
*._optimizer_gather_feedback=TRUE
*._optimizer_gather_stats_on_load=TRUE
*._optimizer_group_by_placement=TRUE
*._optimizer_hybrid_fpwj_enabled=TRUE
*._optimizer_improve_selectivity=TRUE
*._optimizer_interleave_jppd=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_multi_table_outerjoin=TRUE
*._optimizer_native_full_outer_join='FORCE'
*._optimizer_new_join_card_computation=TRUE
*._optimizer_null_accepting_semijoin=TRUE
*._optimizer_null_aware_antijoin=TRUE
*._optimizer_or_expansion='DEPTH'
*._optimizer_order_by_elimination_enabled=TRUE
*._optimizer_outer_join_to_inner=TRUE
*._optimizer_outer_to_anti_enabled=TRUE
*._optimizer_partial_join_eval=TRUE
*._optimizer_proc_rate_level='BASIC'
*._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_strans_adaptive_pruning=TRUE
*._optimizer_system_stats_usage=TRUE
*._optimizer_table_expansion=TRUE
*._optimizer_transitivity_retain=TRUE
*._optimizer_try_st_before_jppd=TRUE
*._optimizer_undo_cost_change='12.1.0.1'
*._optimizer_unnest_corr_set_subq=TRUE
*._optimizer_unnest_disjunctive_subq=TRUE
*._optimizer_unnest_scalar_sq=TRUE
*._optimizer_use_cbqt_star_transformation=TRUE
*._optimizer_use_feedback=TRUE
*._optimizer_use_gtt_session_stats=TRUE
*._optimizer_use_histograms=TRUE
*._or_expand_nvl_predicate=TRUE
*._ordered_nested_loop=TRUE
*._parallel_broadcast_enabled=TRUE
*._partition_cdb_view_enabled=TRUE
*._partition_view_enabled=TRUE
*._pga_max_size=2G
*._pivot_implementation_method='CHOOSE'
*._pre_rewrite_push_pred=TRUE
*._pred_move_around=TRUE
*._pred_push_cdb_view_enabled=TRUE
*._push_join_predicate=TRUE
*._push_join_union_view=TRUE
*._push_join_union_view2=TRUE
*._px_adaptive_dist_method='CHOOSE'
*._px_cdb_view_enabled=TRUE
*._px_concurrent=TRUE
*._px_cpu_autodop_enabled=TRUE
*._px_filter_parallelized=TRUE
*._px_filter_skew_handling=TRUE
*._px_groupby_pushdown='FORCE'
*._px_join_skew_handling=TRUE
*._px_minus_intersect=TRUE
*._px_object_sampling_enabled=TRUE
*._px_parallelize_expression=TRUE
*._px_partial_rollup_pushdown='ADAPTIVE'
*._px_partition_scan_enabled=TRUE
*._px_pwg_enabled=TRUE
*._px_replication_enabled=TRUE
*._px_single_server_enabled=TRUE
*._px_ual_serial_input=TRUE
*._px_wif_dfo_declumping='CHOOSE'
*._px_wif_extend_distribution_keys=TRUE
*._query_rewrite_setopgrw_enable=TRUE
*._remove_aggr_subquery=TRUE
*._replace_virtual_columns=TRUE
*._right_outer_hash_enable=TRUE
*._rowsets_cdb_view_enabled=TRUE
*._selfjoin_mv_duplicates=TRUE
*._smm_max_size=1048576
*._smm_min_size=1024
*._smm_px_max_size=15415296
*._sql_model_unfold_forloops='RUN_TIME'
*._sqltune_category_parsed='DEFAULT'# parsed sqltune_category
*._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/bsrg60/adump'
*.audit_trail='DB'
*.cluster_database=TRUE
*.compatible='12.1.0.0.0'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bsrg60'
*.log_archive_dest_2='SERVICE=bsr ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=bsr'
*.fal_server='bsr','far_sync'
*.log_file_name_convert='+DATA/BSR','+DATA/BSRG60'
*.db_file_name_convert='+DATA/BSR','+DATA/BSRG60'
bsrg601.core_dump_dest='/u01/app/oracle/diag/rdbms/bsrg60/bsr1/cdump'
bsrg602.core_dump_dest='/u01/app/oracle/diag/rdbms/bsrg60/bsr2/cdump'
*.db_block_size=16384
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='bsr'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=50565M
*.db_writer_processes=8
*.diagnostic_dest='/u01/app/oracle/diag/rdbms/bsrg60/bsr1/trace'
*.enable_pluggable_database=TRUE
bsrg601.instance_number=1
bsrg602.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=105766912# log buffer update
*.nls_language='GERMAN'
*.nls_territory='GERMANY'
*.open_cursors=3000
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=30108M
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=2000
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan='SCHEDULER[0x4211]:DEFAULT_MAINTENANCE_PLAN'
*.result_cache_max_size=462688K
*.sessions=3040
*.sga_max_size=128849018880
bsrg601.sga_target=115964116992
*.sga_target=115964116992
*.shared_servers=5
*.skip_unusable_indexes=TRUE
bsrg601.thread=1
bsrg602.thread=2
bsrg601.undo_tablespace='UNDOTBS1'
bsrg602.undo_tablespace='UNDOTBS2'
Tagged:
«1

Answers

  • yasinyazici
    yasinyazici Member Posts: 121

    Hi

    your configuration is little confused for me

    note that lisneter does not listen bsrg60 service.If this is standby db as we mentioned before posts standby has to have db unique name and this unique name identify service name. I suggest you.

    you identify db_unique_name in the pfile. and start againd standby db with pfile.

    Service "bsr" has 1 instance(s).

      Instance "bsrg60_1", status BLOCKED, has 1 handler(s) for this service...

    The command completed successfully

    And I assume db nomount state. At this situtation listener status seems  blocked.so you can not connect db to be using "sqlplus sys/[email protected]  as sysdba" at this state. this is ignore any connection that coming with bsrg60.After you correct above statement you can connect like following


    [email protected] ~]$ sqlplus / as sysdba

    good luck

    yasin

  • 1854985
    1854985 Member Posts: 50

    Hello yasin

    You are also rightagain  in  the point with db_unique_name, I changed it.

    But it is a littel bit curious, in a lot of Howtos there are samples conencting

    like

    rman target sys/[email protected] auxiliary sys/[email protected]
    

    http://www.dba-blog.de/oracle/hochverfuegbarkeit/49-data-guard-far-sync-mit-oracle-12c-release-1

    Oracle 12c Active Data Guard Database - VST

    that I can also connect via rman with a tnsnames.ora

    Now I made the changes, but now I get

    [[email protected] ~]$ rman target sys/[email protected] auxiliary sys/[email protected]
    
    Recovery Manager: Release 12.1.0.1.0 - Production on Di Jul 8 14:06:45 2014
    
    Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: BSR (DBID=4185955372)
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00554: initialization of internal recovery manager package failed
    RMAN-04006: error from auxiliary database: ORA-01017: Benutzername/Kennwort ungultig; Anmeldung abgelehnt
    [[email protected] ~]$
    
    
  • mseberg
    mseberg Member Posts: 7,004 Silver Crown

    Hello;

    ORA-01017: invalid username/password


    I generally scp the production password to the aux site and rename.


    Then startup nomount


    and then


    Start RMAN and issue duplicate command

    $ORACLE_HOME/bin/rman target=sys/@primary auxiliary=sys/@standby

    RMAN>duplicate target database for standby from active database NOFILENAMECHECK;

    Best Regards

    mseberg



  • yasinyazici
    yasinyazici Member Posts: 121

    Hi

    Ok. You can connect to primary as target. But you can not connect to standby as auxiliary.İt seems password problem.

    could you send me "lsnrctl stat" command result on the standby?

    and

    tnsnames.ora content

    Yasin.

  • 1854985
    1854985 Member Posts: 50

    Hi yasin,

    you must be one of the few working in Europe ...

    The listener shows, that the status is blocked, but this is ok, cause startup in nomount.

    maybe mseberg right, that I have to copy the  passwd again .. I can try it ...

    [[email protected] ~]$ lsnrctl stat
    
    LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 08-JUL-2014 14:52:14
    
    Copyright (c) 1991, 2013, Oracle.  All rights reserved.
    
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
    Start Date                08-JUL-2014 13:16:05
    Uptime                    0 days 1 hr. 36 min. 8 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/12.1.0/grid/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/dbb-g60-01/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.40.25.110)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.40.25.193)(PORT=1521)))
    Services Summary...
    Service "+ASM" has 1 instance(s).
      Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Service "bsrg60" has 1 instance(s).
      Instance "bsrg60_1", status BLOCKED, has 1 handler(s) for this service...
    The command completed successfully
    
    
  • yasinyazici
    yasinyazici Member Posts: 121

    Hi

    you must be one of the few working in Europe ...   why?

    Did not you already copy passwordfile from primary database before?

    yasin

  • 1854985
    1854985 Member Posts: 50

    Hi

    I thought.. mostly I get answers in the ( night ) , not when I am working in the office 

    Hmm, I thought that I have copied the password file, but now I see that I do not have a password file

    for copy. I have a 12c RAC with asm, no password file in /dbs.

    Micha

  • yasinyazici
    yasinyazici Member Posts: 121

    Hi Micha

    I am working at the office at the moment

    ok now you have copied password file  and rename it . what is the result when you trying to rman ?

    Yasin.

  • 1854985
    1854985 Member Posts: 50

    Hi ,

    ah ok   What I want to say is:

    I made the copy, but  there wasn't  a file copied and I do not check the result.

    Why? Cause there is no password file on the  RAC nodes (production)  in /dbs I can copy.

    Micha

  • yasinyazici
    yasinyazici Member Posts: 121

    Hi

    Opps.t is not there password file on the production.then you must create that and copy to standby.

    $>orapwd file=orapw"instancename" password="syspassword" entries=5 ignorecase=Y

    yasin

This discussion has been closed.