#databasesql

[premier-it-training-institute]

Best SQL DBA Training In Marathahalli Bangalore | MNP Technologies

Enroll your seat for the best SQL DBA training at MNP Technologies training institute, Marathahalli, Bangalore. MNP Technologies is one of the leading training institutes that provide the Best SQL DBA Course with real-time training & 100% placement assistance, 50+Hrs, Live Projects, and Mock Interviews.

Call us at +917259912345 or visit our website to schedule a free demo class right away.     

[geekycodes]

Introduction to SQL

SQL is a standard language for accessing and manipulating databases. What is SQL? SQL stands for Structured Query LanguageSQL lets you access and manipulate databasesSQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987 What Can SQL do? SQL can execute queries against a databaseSQL can…

[akubi0w1]

golang database/sqlでnullと戦う

is なに

golangでdatabase/sqlのQueryRowなりQueryを使ってデータを読む。

DBに保存されたレコードでnullのカラムがあると、row.Scan()したときに、読めへんわって言われる。これをどう避けるか。

sql: Scan error on column index 2, name "xxx": unsupported Scan, storing driver.Value type <nil> into type *string

解決策

bioというカラムがNULL許可。エラーを避けてみる。

1. database/sqlパッケージのNULL許可の型を使う

row := DB.QueryRow("SELECT id, name, bio FROM users WHERE id=2") var id int var name string var _bio sql.NullString // null許容の型 if err := row.Scan(&id, &name, &_bio); err != nil { log.Println("scan error: ", err) w.Write([]byte("Scan error")) return } // nullチェック var bio string if _bio.Valid { // _bio.Stringで値の取得 bio = _bio.String } else { // _bioがnull bio = "bio is null" } str := fmt.Sprintf("id: %d, name: %s, bio: %s", id, name, bio) w.Write([]byte(str))

毎回バリデーションすんのかあ...という気持ちになりました。

2. SQLのクエリで対応する

SQLのCOALESCE()を使って対応。コーアレスって読むらしい。

COALESCE(column1, column2, ...)とすると、column1 -> column2 -> ...って見ていき、一番最初にNULLでなかった値を取得するらしい。

つまり、COALESCE(column1, value)とかってしておけば、column1がnullだったときに代替の値(value)を突っ込める。

row := DB.QueryRow(` SELECT id, name, COALESCE(bio, 'bio is null') FROM users WHERE id=2`) var id int var name string var bio string if err := row.Scan(&id, &name, &bio); err != nil { log.Println("scan error: ", err) w.Write([]byte("Scan error")) return } str := fmt.Sprintf("id: %d, name: %s, bio: %s", id, name, bio) w.Write([]byte(str))

まとめ

NULL許可は避けた方が心が安らぐ( ˘ω˘ )

NULL許可対策として、2手法試してみたが、個人的には後者の方が好みだった...。

NULL許可との戦いでした。

参考

https://golang.shop/post/go-databasesql-08-nulls-ja/

https://qiita.com/mikakane/items/1e45c2a798d0c7edffda

[abbymories]

Show HN: Expose Go's database/sql stats as Prometheus metrics https://ift.tt/2ZpM0E7 December 26, 2019 at 05:11AM

[rbbox]

Show HN: Expose Go's database/sql stats as Prometheus metrics https://ift.tt/2ZufpNp

Show HN: Expose Go’s database/sql stats as Prometheus metrics https://ift.tt/2ZufpNp

Show HN: Expose Go’s database/sql stats as Prometheus metrics https://ift.tt/2ZpM0E7 December 26, 2019 at 05:11AM

from Blogger http://rb-box.blogspot.com/2019/12/show-hn-expose-gos-databasesql-stats-as.html

[coast2coastchat]

New Post has been published on Coast 2 Coast Chat

New Post has been published on https://coast2coastchat.com/2019/11/remote-java-developer-11/

REMOTE Java Developer

Unknown Location : Millersville MD US …v2.1JUnit v4.12Log4J v2.1Other TechnologiesOracle OLTP DatabaseSQL Server DatabaseMavenBuild ForgeRationalQuartz Scheduler v2.2JasperSoft Reports v6 – provided

More >>

[notsadrobotxyz]

Plan B: Renovate old Apps Server Hardware

Overview: I had a situation to renovate the hardware of very old server and we do not have idea about some of the application running, It may be fresh installation of application can create performance issue and also we are facing the complexity of same patched version oracle binaries. I planned to restore the whole system through the system restore policy and restore and recover the database with the RMAN cold backup and after performance tuning and testing the different aspect of oracle will provide the same IP configuration to the new server.Scenario:This scenario assumes that we have two networked Windows 2003 hosts, DBSERVER and DBSERVER1. A target database named SADHAN is on DBSERVER and is registered in Recovery catalog RMAN. We want to Restore & Recovery of SADHAN on DBSERVER1, while original database server DBSERVER is completely offline. In my case the O/S is Windows 2003 and the Oracle Database Product is 9i R2 (9.2.0.7) Standard Edition patched oracle binaries are restored through system restore procedure. Click for More info: Backup & Restoring System using AcronisTheory:If our goal is to perform a Test run of our disaster recovery procedures, or to permanently move a database to a new host, then we can use the Restore & Recovery Procedure.If we use Restore & Recovery procedure, then the DBID for the restored database will be the same as the DBID for the original database, so after the restore & recovery procedure is completed, if we connect to the Test database and the recovery catalog DB, the recovery catalog will be updated with information about the Test DB that can interfere with RMAN's ability to restore and recover the source database (due to same DBID)If our goal is to create a new copy of our target database for ongoing use on a new host, then use the RMAN DUPLICATE command instead of Restore & Recovery procedure. The DUPLICATE command assigns a new DBID to the database it creates and enabling it to be registered in the same recovery catalog as the original database.Descriptions:In the night we planned to take the system restore by shutting down the original database and in the early morning we opened the original database for end user. In the day time we done the system restore to new server using Acronis system restore policy. Finally we are able to get the same oracle binaries. In the next Day I have taken the sufficient downtime for RMAN backup and restore policy synchronies the database. To do a consistent" backup with RMAN, the database must be mounted as RMAN needs to access and update the control files. With an OS scripted backup pre-RMAN, the database was truly "COLD" -- there would be no Oracle processes running.At the moment I had to restore the database on the new server the old server was not available anymore.STEPS:Taking RMAN COLD backup including  Current Controlfile Backup.Shutdown the Old Database to restrict any modification.Copy the backup on other host on the same location as per the DBserver.Create PFILE from current server and Copy the Destination server location.Copy or Create tnsname.ora, listener.ora and password file to the destination host folder.Testing and Validating Listener and RMAN Repository Connection.Create Oracle Service for windows (in our case just check it is started) and check all other folder is already restored through system restore.Start the database on new host with the Pfile.Restore Control fileRestore whole databaseRecover whole database.Validate the Restore & Recovery procedure Correctness.Enter change the IP address, name of Old server to New server.Check the Application is running successfully.After successful testing make the server available for end users.I have already password file and server parameter file into the new $ORACLE_HOME/dbsDue to system restore. I also have same directory structure on the new server.I create a pfile from spfile and copied it into the new server/C:>SET ORACLE_SID = SADHANC:SQLPLUS /NOLOGSQL> CONNECT / AS SYSDBAConnected to an idle instance. SQL>STARTUP FORCE NOMOUNT PFILE='D:oracleora92dbssadhan.sql';SQL>CREATE SPFILE FROM PFILE='D:oracleora92dbssadhan.sql';File created.SQL> shutdown immediate; --bounce back the databaseSQL> Startup nomount;Caution: Make sure RMAN is not connected to recovery catalog, otherwise RMAN records information about restored database in recovery catalog as the DBID is same. Thus you are not able to restore and recover the database in future with this RMAN. C:> rman target /connected to target database: SADHAN (not mounted)RMAN:>SET DBID 63198018;RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;RMAN> restore controlfile from "H:ORABACKC-1103102985-20100216-02";RMAN> alter database mount;RMAN> sql ‘alter database mount’;Note: The Blue mark command you can choose optionally. RMAN> list backup;RMAN> Restore database;RMAN> ALTER DATABASE OPEN RESETLOGS;Method: Restore database (when CRD file is Ok) through RMANWhen Database is all ready running only need to synchronies database file. C:>rman RMAN> connect target / connected to target database (not started) RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;RMAN> ALTER DATABASE MOUNT;

[coast2coastchat]

New Post has been published on Coast 2 Coast Chat

New Post has been published on http://coast2coastchat.com/2019/11/remote-java-developer-10/

REMOTE Java Developer

Unknown Location : Millersville MD US …v2.1JUnit v4.12Log4J v2.1Other TechnologiesOracle OLTP DatabaseSQL Server DatabaseMavenBuild ForgeRationalQuartz Scheduler v2.2JasperSoft Reports v6 – provided

More >>

[coast2coastchat]

New Post has been published on Coast 2 Coast Chat

New Post has been published on https://coast2coastchat.com/2019/11/remote-java-developer-9/

REMOTE Java Developer

Unknown Location : Millersville MD US …v2.1JUnit v4.12Log4J v2.1Other TechnologiesOracle OLTP DatabaseSQL Server DatabaseMavenBuild ForgeRationalQuartz Scheduler v2.2JasperSoft Reports v6 – provided

More >>

[coast2coastchat]

New Post has been published on Coast 2 Coast Chat

New Post has been published on http://coast2coastchat.com/2019/11/remote-java-developer-8/

REMOTE Java Developer

Unknown Location : Millersville MD US …v2.1JUnit v4.12Log4J v2.1Other TechnologiesOracle OLTP DatabaseSQL Server DatabaseMavenBuild ForgeRationalQuartz Scheduler v2.2JasperSoft Reports v6 – provided

More >>

[notsadrobotxyz]

RMAN Different errors and their Solution

Backup Fails with Invalid RECID Error: RMAN-20035, RMAN-6038 When you attempt a backup and receive the following error messages:RMAN-3014: Implicit resync of recovery catalog failed RMAN-6038: Recovery catalog package detected an error RMAN-20035: Invalid high RECID errorIt indicates the control file and the recovery catalog is not synchronized. RMAN detects that the control file currently in use is older than the control file previously used to resynchronize.Cause:This due to any of the scenario you are restore a backup controlfile through a non-oracle mechanism and then open the database through Resetlogs option or making a copy of control file through operating system utility and trying the restore on new system through RNAN. You do not use catalog so RMAN does not get any information regarding this process.  The recovery catalog indicates that the highest RECID is 100, but the control file indicates that the highest RECID is 90. The control file RECID should always be greater than or equal to the recovery catalogRECID, so RMAN issues RMAN-20035Solution:Login with sysdba:sys/oracle@orcl3Mount the databaseSQL> shutdown immediate;SQL> startup mount;1.      Perform cancel based recovery to open the database with Resetlogs option.Alter Database Recover Database until Cancel Using Backup Controlfile;Alter Database Recover Cancel;2.      Then connect to the rman and open the database using Resetlogs optionCONNECT rsys/oracle@target catalog catalog/catalog@rmanRMAN> Alter database open resetlogs;3.      Do not forget to take the fresh backup after resetlogs option;Backup Fails Because of Control File Enqueue: ORA-00230In this scenario, a backup job fails because RMAN cannot make a snapshot control file. The message stack is as follows:RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 08/30/2001 22:48:44 ORA-00230: operation disallowed: snapshot control file enqueue unavailable Cause: When RMAN needs to back up or resynchronize from the control file, it first creates a snapshot or consistent image of the control file. If one RMAN job is already backing up the control file while another needs to create a new snapshot control file, then you may see the following message:Waiting for snapshot control file enqueueUnder normal circumstances, a job that must wait for the control file enqueue waits for a brief interval and then successfully obtains the enqueue. RMAN makes up to five attempts to get the enqueue and then fails the job. The conflict is usually caused when two jobs are both backing up the control file, and the job that first starts backing up the control file waits for service from the media manager.Solution:To determine which job is holding the conflicting enqueue:1.      Start a new SQL*Plus session and login with the sysdba:2.      Execute the query to check the waiting cause:SELECT s.SID, USERNAME AS "User", PROGRAM, MODULE, ACTION, LOGON_TIME "Logon", l.* FROM V$SESSION s, V$ENQUEUE_LOCK l WHERE l.SID = s.SID AND l.TYPE = 'CF' AND l.ID1 = 0 AND l.ID2 = 2; The output is look like as: (if there is lock) SID User Program Module Action Logon --- ---- -------------------- ------------------- ---------------- --------- 4 SYS rman@rman (TNS V1-V3) backup full datafile: c10000210 STARTED 16-NOV-12This situation generally came across when a job is writing to a tape drive, but the tape drive is waiting for new tape to be inserted. In the mean time if you start new job then you will probably receive the enqueue message because the first job cannot complete until the new tape is loaded.After you have determined which job is creating the enqueue, you can do one of the following:§         Wait until the job holding the enqueue completes§         Cancel the current job and restart it after the job holding the enqueue completes§         Cancel the job creating the enqueueRMAN Does Not Recognize Character Set Name: PLS-00553 While working with Sadhan I configure the rman backup server and start taking different production DB backup. In one fine morning one of our database break down needs media recovery. I connected the rman to target database and try to restore the database but I receive the following error while using the “restore database” command.RMAN-03002: failure during compilation of commandRMAN-03013: command type: sendRMAN-06003: ORACLE error from target database: ORA-06550: line 1, column 51:PLS-00553: character set name is not recognizedCause:After searching about this error I found RMAN failed to compile the SEND statement because the NS_LANG of the target database differed from the NS_LANG of the recovery catalog.  In the environment or shell script, set and export NS_LANG to the correct value for the recovery catalog so that RMAN receives the correct value.Solution:1. Query the target database to determine NLS_CHARACTERSET parameter.SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';VALUE ------------- AR8MSWIN1256Try same Query with the rman catalog database I found the database character set is same as the production database here also.2. Set the character environment variable in the rman catalog system same as the server. % setenv NLS_LANG american_america.we8dec % setenv NLS_DATE_FORMAT "MON DD YYYY HH24:MI:SS"In windows you can go through the registry to setup environment variable:HKEY_LOCAL_MACHINE –> SOFTWARE –> ORACLE –> NLS_LANGalso check from the below path: My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)Note: If the connection is made through a listener, then the listener must be started with the correct Globalization Support settings. Otherwise, the spawned connections inherit the incorrect Globalization Support settings from the listener.Database Duplication Fails with error: RMAN-06053, RMAN-06025When I try to duplicate database first time using RMAN the duplicate command is failed with following error:RMAN-03002: failure of Duplicate Db command at 07/28/2010 23:11:44RMAN-03015: error occurred in stored script Memory ScriptRMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of log thread 1 seq 12 lowscn 487272 found to restore Cause:RMAN unable to start archive log restore to default destination. The problem is that rman is not able to apply the entire archive log needed for complete recovery. For example, if you only backed up logs through sequence 15, but the most recent archived log is sequence 16, then DUPLICATE fails.Solution:When creating the duplication script either, use the SET UNTIL command to specify a log sequence number for incomplete recovery or include NOFILENAMECHECK with the duplicate command. For example, to terminate recovery after applying log sequence 15, enter:RUN{  SET UNTIL SEQUENCE 16 THREAD 1; #recovers up to but not including log 16  DUPLICATE TARGET DATABASE TO 'dupdb';}-or- add NOFILENAMECHECK with duplicate database command:DUPLICATE TARGET DATABASE TO "clone" NOFILENAMECHECK;For Example:RMAN> DUPLICATE TARGET DATABASE TO "clone";When NOFILENAMECHECK is used with the DUPLICATE command, RMAN does not validate the filenames during restoration.Note: If the primary database and the standby database are on the same host, The DUPLICATE NOFILENAMECHECK option should not be used.Write error on file: ORA-19502: While trying to take the backup through the RMAN some times we are facing the below error. RMAN> backup database plus archivelog;channel ORA_DISK_1: starting piece 1 at 03-OCT-12 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup command at 10/03/2012 12:05:04 ORA-19502: write error on file "4crcv3st_1_1", blockno 5354 (blocksize=8192)Solution:As the name it is clear. It is a write error on output file. Check the file then try again.Check the location parameter default setting by issuing a command like: Configure channel 1 device type disk format 'H:orabackbackup_%U';It is also possible the backup will be attempting to write to a larger disk space than is available; there is not enough disk space on the drive to complete the backup.Failed to identify file in RMAN: ORA-19505 After configuring the target database with the RMAN first time. I try to take database backup through RMAN and found the following error:RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03009: failure of backup command on ch00 channel at 03/26/2012 13:01:03ORA-19505: failed to identify file “/oraback/arch/1_1100721_664058960.dbf”ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryIn this case nothing happened with your backup command. If you read the error, it is saying particular archive logfile is not available. This may be due to file deleted at OS level due to some reason. In this situation, do the following1. Run crosscheck command against archivelogsRMAN>Crosscheck archivelog all;2. If you find any archives marked as EXPIRED, then delete those expired archivesRMAN>Delete expired archivelog all;This will prompt you YES or NO. If you don’t want a promptRMAN>Delete noprompt archivelog all;3. Then take a fresh archivelog backupRMAN>Backup archivelog all; RMAN-06172: no autobackup found tips Recently I got the issue from one of the user. He is getting the RMAN-06172 error with the restore spfile command in case of disaster recovery. He is using oracle 10g on windows 2003. He states he has not tested his backup before RMAN> restore spfile from autobackup; RMAN-03002: failure of restore command at 04/01/2013 04:32:11 RMAN-06172: no autobackup found or specified handle is not a valid copy or piece Cause: A restore could not proceed because no autobackup was found or the specified handle is not a valid copy or backup piece.1.      In case of restore from autobackup, it may be the case that a backup exists, but it does not find the location of backup due to not specify the real backup location. 2.      In case of restore from backup, it may be the backup is not a backup piece or controlfile copy. It may be that it does not exist.Solution: The above error occurs because RMAN does not find the controlfile or spfile location. Normally Oracle looks the backup by default in the ORACLE_HOME/dbs directory if you are not proving the destination with the spfile restore command. You need to change your RMAN command to point to the real spfile location with the restore command.RMAN> Restore spfile from autobackup db_recovery_file_dest='D:oracleflash_recovery_area' db_name=’ORCL3’;In the case when FRA is not configured and autobackup control file location is not setRMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;RMAN> Configure controlfile autobackup on;The backupset and autobackup controlfile is created in default windows location: "C:windowssystem32", with DBID. For Example:C:WINDOWSSYSTEM32C-1681257132-20130102-02 comment=NONE RMAN-06207, RMAN-06208, RMAN-06210 errors Sometimes we are facing errors from RMAN for Oracle backup as following. RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due to mismatched status.   RMAN-06208: Use CROSSCHECK command to fix status Solution: To solve  RMAN-06207, RMAN-06208, RMAN-06210 errors perform crosscheck command execution from rman prompt. This command will update RMAN’s metadata and catalog. RMAN>crosscheck archivelog all; If this command error doesn’t resolved then delete command can be helpful in this situation. RMAN>DELETE FORCE OBSOLETE; If above all commands do not working then kindly check from where are you executing above commands. Because standby archives doesn’t register in primary database. For this situation execute above commands in standby and primary both with “crosscheck archivelog all” command also. RMAN-05542: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections While trying to use RMAN DUPLICATE DATABASE command without connecting to target or catalog database, we are getting the following error: DUPLICATE DATABASE to TEST1 until SCN 3587956 SPFILE SET CONTROL_FILES='D:ORACLEoradataSADHANcontrol01.ctl' set db_file_name_convert='D:ORACLEORADATASADHAN','D:ORACLEORADATATEST' set log_file_name_convert='D:ORACLEORADATASADHAN ','D:ORACLEORADATATEST' backup location 'H:ORABACKSADHAN';Cause: Only UNTIL TIME can be used with DUPLICATE without TARGET and CATALOG connections or the situation if you specify UNTIL clause but not putting the time based.Action: Retry the command with an UNTIL TIME clause or without an UNTIL clause.Solution: When we change UNTIL SCN to UNTIL TIME with specified parameter details, duplication process was successful.DUPLICATE DATABASE to TEST1 UNTIL TIME "To_Date('12-FEB-2013 07:45:28','DD-MON-YYYY HH24:MI:SS')" SPFILE SET CONTROL_FILES='D:ORACLEoradataSADHANcontrol01.ctl' set db_file_name_convert='D:ORACLEORADATASADHAN','D:ORACLEORADATATEST' set log_file_name_convert='D:ORACLEORADATASADHAN ','D:ORACLEORADATATEST' backup location 'H:ORABACKSADHAN'; Reference:For More RMAN related errors and their solution, please search on this blog with the error code or follow the below link.RMAN-04005: error from target database: ORA-01031: insufficient privilegesRMAN-06059: expected archived log not found RMAN-06035: Wrong version version recover.bsq RMAN-06091: no channel allocated for maintenance (of an appropriate type)