no supported authentication methods available (server sent publickey gssapi-keyex gssapi-with-mic) on google Cloud , AWS, Azure

 no supported authentication methods available (server sent publickey gssapi-keyex gssapi-with-mic)

Hello,

You got an error like this on Google Cloud , AWS, Azure or something like that.

the problem is about your sshd_config file. 

change the PasswordAuthentication yes from no 

 sudo vi /etc/ssh/sshd_config

PasswordAuthentication yes 

:wq!

and restart sshd service

##sudo systemctl restart sshd

thats All.

How to move SQL Server database files (LDF and MDF) to another location (path)

DatabaseName: CND    (example)

NewPathNameData: C:\DATA1\

NewPathNameLog: C:\LOG\

checking to Database PathLocation:

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'CND');

 

use master

go

ALTER DATABASE CND

    MODIFY FILE ( NAME = CND,   

                  FILENAME = 'C:\DATA1\CND.mdf');  

GO

 

ALTER DATABASE CND

    MODIFY FILE ( NAME = CND_log,   

                  FILENAME = 'C:\LOG\CND_Log.ldf');  

GO

 

ALTER DATABASE CND SET OFFLINE;  

GO

move data (*.mdf and *.ldf ) to new location (as phisical) "like copy and past to new location etc."

ALTER DATABASE CND SET ONLINE;  

GO

checking to New Database PathLocation: 

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus

FROM sys.master_files  

WHERE database_id = DB_ID(N'CND')  

GO

How to reset/crack the password of the "dba" account - SQL Anywhere

 

• Trying to login as account "dba/sysadmin" and receiving the error -103 "Invalid user ID or password."
• We have "lost/forget" the password of the "dba/sysadmin" account.
• How  can we reset the password of the "dba"  account?

password_error   

Cause

• Prior to build 17.0.5739
• The default password for the "dba"¹ account is "sql" has been changed.²
• A different password can be defined to the account at creation time or later using the "ALTER USER ..." statement.
• A password change, after the creation of the database, will be logged in the transaction action log, with the password encrypted.
• There is no option to start a database and print a new dba password to console (as in ASE or SQL Server).
  SQL Anywhere has been designed to run on systems where security from the operating system level cannot be guaranteed.
• Passwords are stored encrypted from the moment they are written to the database and there is no option to decrypt them or reset it, 
  unless another administrator account or transaction log with logged password change is available (see below).
• From build 17.0.5739 or above, a new feature has been introduced:
  

  New feature is added to allow to reset the forgotten password.

  1. New role (SYS_OFFLINE_RESET_PASSWORD_ROLE) is added. It can be granted by the role administrator to a user who is authorized to reset password.
  2. New server command line switch is added to reset the password of the specified user by the user whom SYS_OFFLINE_RESET_PASSWORD_ROLE role has been granted.

          -orp "{UserID|UID}=<userid>;{ NewPassword|NEWPWD}=<newpassword>;{ AuthUserID |AUTHUID}=<authuserid>;{ AuthPassword|AUTHPWD}=<authpassword>"

  • <userid>: the user whose password is going to be reset.
  • <newpassword>: new password of the <userid>
  • <authuserid>: the user who has SYS_OFFLINE_RESET_PASSWORD_ROLE granted by the role administrator
  • <authpassword>: password of <authuserid>

  If -orp is used, the server would start a database and attempt to reset the password with given parameters, and immediately stop the database and shutdown. The console output reports the success message or an error message if failed.

  Note:

  - The password reset would not be allowed if the <userid> has no password.

  - Must specify one database with the -orp option

  - No database option is not allowed with -orp server option

  - No other server option (except -o) is allowed with -orp server option

  - minimum password length of <newpassword> is 6.

 Notes:

1. Some applications may have chosen a different account to serve as their dba but you may still be
   able to follow this KBA if you know what the name of that account is supposed to be.
2. There is no predefined default dba or password as of version 17.

Resolution

You do have three options to resolve this situation

• use the -orp database server option added in version 17.0.5739 or above
• reset the password using another DBA account
• rebuild the database using the transaction log files

Use the -orp database server option added in version 17.0.5739 or above

Example:

User ‘DBA’ had password ‘sql123’ and the user ‘reset_user’ has password ‘sql456’. As a role administrator, user ‘DBA’ granted SYS_OFFLINE_RESET_PASSWORD_ROLE to user ‘reset_user’ by the following statement.

grant role sys_offline_reset_password_role to reset_user;

Suppose that user ‘DBA’ has forgotten the password. After shutting down the server, it can be reset to ‘newpassword’ by the following server command line.

dbeng17 -orp "UID=DBA;NEWPWD=newpassword;AUTHUID=reset_user;AUTHPWD=sql456" mydb.db

Reset the password using another DBA account

The account "dba" is the default super-user of a database. Additional super-user accounts can exist in a database. If such an additional super-user exists and the credentials are known, this account can be used to reset the password of the "dba" account. Please refer to KBA 2207121  for more information about super-user account creation.

1. In Sybase Central use the credentials of another DBA account to log in to the database (If you do not have Sybase Central please refer to KBA 2040854)
2. In the left hand Folders Double Click Users
3. Right Click the user credentials you want to reset > Click Properties
4. Change the password of the account, confirm it and click OK
1. 
   

Rebuild the database using the transaction log files

A rebuild can be done, if one the following conditions is meet.

• All Transaction log files do exist, since the creation of the database.
• A database file backup is available and all transaction log files, created after the backup, do exist.

The important aspect is that the database can only be rebuild, using one or more transaction log files, if these do contain all data definition language (DDL) and data manipulation language (DML) statements since the creation of the database.

Using all transaction log files, since the database was created

1. Create a new database, with the same parameters as the original database was built with.
• For example: dbinit -dba DBA,sql -p 4k demo.db
  You can find the original parameters of a database from a console/server log. In the console log file, on the top it should say something like the following, describing how much memory/cache size/ page size are you using
  
  
2. Translate the transaction log files into .sql script files, using the SQL Anywhere tool "dbtran".
• For example: dbtran demo.log demo.sql
3. If the password of the "dba" account was changed after the creation of the database, the "ALTER USER" statement needs to be located in the created .sql files and commented out.
   OR
   If you cannot find the ALTER USER statement, go to the bottom of .sql file and add the following statement:
   ALTER USER dba IDENTIFIED BY sql;
   Please replace sql with the new password you want for your database.
   Adding this statement will override the previous password change.
4. In the same order as the corresponding transaction files were created, apply each single .sql script file to the database, using e.g. Interactive SQL (dbisql)
• For example: dbisql -c "DBF=demo;UID=DBA;PWD=sql" demo.sql

Using all transaction log files, since the backup of the database was created

1. Translate the transaction log files into .sql script files, using the SQL Anywhere tool "dbtran".
• For example: dbtran demo.log demo.sql
2. If the password of the "dba" account was changed after the creation of the database, the "ALTER USER" statement needs to be located in the created .sql files and commented out.
   OR
   If you cannot find the ALTER USER statement, go to the bottom of .sql file and add the following statement:
   ALTER USER dba IDENTIFIED BY sql;
   Please replace sql with the new password you want for your database.
   Adding this statement will override the previous password change.
3. Get the backup of the database
4. In the same order as the corresponding transaction files were created, apply each single .sql script file to the database, using e.g. Interactive SQL (dbisql)
• For example: dbisql -c "DBF=demo;UID=DBA;PWD=sql" demo.sql

Caveats:

Rebuilding a database has in general some side effects you should be aware of.

• This will break any MobiLink or SQL Remote synchronization that is ongoing.
• This should only be done in a development environment.
• For a database of an OEM Authenticated edition, further steps might be required to re-authenticate the database.
• If there is an event that created and scheduled in the past, error "Start date/time for non-recurring event %1 is in the past" might return. You need to open the translated sql file, and comment out all the event that schedule in the past or change the schedule time to future time.

Notes: The tool "dbtran" prints the start & end offset of the translated transaction log file. This way you can verify that no transaction log is missing. The end offset of the first transaction log should be identical to the start offset of the second transaction, and so on.

Source:Sybaseinfocenter

attempt to fetch logical page (1:XXXXXX) in database XX failed. It belongs to allocation unit XXX not to XXX failed

Merhabalar VeriTabanlarında index yada Non-Cluster index bozulmalarından dolayı aşağıdaki gibi bir hata alabilirsiniz? Bu index bakımlarının zamanında yapılmaması, yanlış index oluşturulması, tabloların datalarının bulunduğu disklerin corrupt olması yada sistem disklerinin crush olması gibi birçok sebebten kaynaklı olabilir. Peki bu hatayı aldık sonra herşey bitti mi? hayır. data kaybında önce veriyi kurtarmak için birçok yöntem vardır.

Örnek olarak aşağıdaki bir hatayı ele alalım.

attempt to fetch logical page (1:438927981) in database 14 failed. It belongs to allocation unit 72060409138315264 not to 72060409197035520

öncelikle tablomuzun yedeğini alalım. ve satır satır row row tüm sağlam veriyi ayrı  bir tabloya alalım.

bunun için aşağıdaki scripti çalıştırabilirsiniz?

 CODE:

set nocount on;

DECLARE @Counter INT = 1;

DECLARE @LastRecord INT = 10000000; --your table_count

 WHILE @Counter < @LastRecord 

BEGIN 

BEGIN TRY  

    BEGIN

        insert into your_table_new SELECT * FROM your_table  WHERE your_column= @Counter --dont forget! create your_table_new before

    END 

END TRY

BEGIN CATCH    

     BEGIN

        insert into error_code select @Counter,'error_number' --dont forget the create error_code table before.

     END

 END CATCH

SET @Counter += 1;

END;

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

tablomuzun yedeği alındı. şimdi tablo üzerinde hertürlü kurtarma senaryolarımızı uygulayabiliriz.

Öncelikle tablodaki cluster mı noncluster indexlerde mi bozluma var tespit etmeye çalışıyoruz. Eğer noncluster ise işimiz kolay tablomuzu kurtarabiliriz. Ancak cluster ise fiziki olarak diskimizde arıza var demek anlamına gelir ve onarma yöntemlerinin diğer adımlarına geçerek onarma yoluna gideceğiz.

 

DBCC TRACEON(3604)

DBCC PAGE('table_name',1, 438927981,3) WITH TABLERESULTS

DBCC TRACEOFF(3604)

Metadata: IndexId 

 0 (heap) yada 1 (clustered index) ise backupdan dönmeniz gerekir.

 0 yada 1 den farklı bir değerse ise noncluster indexi rebuilt etmeniz yeterlidir.

 

 bunların hiç biri işe yaramadı diyelim. tabloyu onarmayı deneyeceğiz.

 

 DBCC CHECKTABLE(TabloAdi, REPAIR_REBUILD )

GO

bu da çzümolmazsa. Son bir seçenek Veri Kaybını da göze alarak onarma yoluna gideceğiz.

DBCC CHECKTABLE(TabloAdi, REPAIR_ALLOW_DATA_LOSS )

GO

 

 şu sitede index datalarına göz atmak için bakabilirsiniz.

 KAYNAK: https://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/

ASSERT: [025]: working path 'X' is not the same path as 'X'

Merhabalar Eğer loglarınızda aşağıdaki hatayı alıyorsanız muhtemel pgbackrest config dosyanız da uyuşmayan bir yer var demektir.

 ASSERT: [025]: working path 'X'  is not the same path as 'X'

primary de pgbackrest için tanımlı pgbackrest.conf dosyasında ayarlama yapıcağız. master instance da aşağıdaki path lerin doğru olduğuna emin olunuz.

checking the pgbackrest.conf of your primary instance

 [root_primary_instance]# cat /etc/pgbackrest.conf

[global]
repo1-path=/pgdata/backrest
repo1-retention-full=2
repo1-host=10.150.153.169
repo1-host-user=postgres

archive-async=y
log-level-file=detail

[global:archive-get]
process-max=8

[global:archive-push]
process-max=8

[pgtest1]
#pg1-path=/var/lib/pgsql/12/data
pg1-path=/pgdata/data
pg1-port=543

servisimizi restart edip hatanın kaybolduğunu göreceksiniz..

 

[root_primary_instance]# systemctl restart postgres-12

The transaction has already committed locally, but might not have been replicated to the standby

 Merhabalar Postgres de eğer aşağıdaki hatayı alıyorsanız;  muhtemelen bu sorun sizin Replication name'iniz yanlış olduğu anlamına geliyor.

DETAIL: The transaction has already committed locally, but might not have been replicated to the standby

 

Öncelikle check edelim.

primary DBde;

>postgres=# show synchronous_commit;

 synchronous_commit
--------------------
 on
(1 row)

senkronizasyonumuz açık görünüyor.. değilse off durumunda ise  postgres.conf dosyasından off kısmı on yapalım.

postgres=#
postgres=# select * from pg_stat_replication ;

 pid     usesysid     usename     application_name     client_addr     client_hostname     client_port     backend_start                  backend_xmin     state      sent_lsn     write_lsn     flush_lsn     replay_lsn     write_lag     flush_lag     replay_lag     sync_priority     sync_state     reply_time                    

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

 24330   16388        replikaci   walreceiver          10.150.153.167  (null)              42554           2020-08-27 11:43:52.374787+03  (null)           streaming  0/19010C88   0/19010C88    0/19010C88    0/19010C88     (null)        (null)        (null)         1                 sync           2020-08-27 13:43:42.137884+03 

postgres=# select application_name, sync_state from pg_stat_replication;
 application_name | sync_state
------------------+------------
 walreceiver      | async
(1 row)

postgres=# show synchronous_standby_names;
 synchronous_standby_names
---------------------------
 pgdbtest1

replication application name walreceiver  ise synchronous_standby_names inde aynı isim olması gerekir.  pgdbtest1 ismini değiştireceğiz.

Standby da 

postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 local

olmasını bekleriz.

değişiklik için 

[root@pg_primary_instance]# vi postgresql.conf

#synchronous_standby_names = 'pgdbtest1'

synchronous_standby_names = 'walreceiver'

kayıt edip çıkıyoruz.

[root@pg_primary_instance]# systemctl restart postgresql-12

şimdi işlemlerinize devam edebilirsiniz.. /pgdata/log/ yada İnstance ınızı nereye kurduysanız oradan da log ları kontrol edebilirsiniz?