Collection of oracle basic commands
Oracle snooping configuration A, snooping (D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ NETWORK\ ADMIN\ listener.ora.ora)
1. Dynamic registration: do not need to display the configuration listener.ora file, when the instance MOUNT, the PMON process will be based on the
The instance_name,service_name parameter registers the instance and service dynamically with listerer.
2. Static registration: read the listener.ora configuration file when the instance is started, and register the instance and service with the listener. B, TNS (D:\ app\ Administrator\ product\ 11.2.0\ dbhome_1\ NETWORK\ ADMIN\ tnsnames.ora)
1 、 CMD
2. LSNRCTL runs to LSNRCTL
3. Check the status of LSNRCTL > status
4. LSNRCTL > stop snooping stops
5. LSNRCTL > start start listening
6. Restart LSNRCTL > reload
= =
Log in 1. Log in to SYSDBY under CMD
Sqlplus / as sysdba; Super user (sysdba)
Sqlplus system/admin as sysdba
Sqlplus scott/tiger@orcl
Sqlplus / nolog; enters sql mode
Conn / as sysdba; connects to the database as DBA
Create user unary identified by 123; create unary user with password: 123
Alter user system identified by unary123; / / change the password of the account
Grant dba,sysdba to unary; gives DBA permission to unary users
GRANT SELECT ON SCOTT.EMP TO unary; / / assign the ownership of the table EMP to unary, and the user gives the table query permission
Select distinct owner from all_objects; to view current system users
Select username from dba_users; to view all current users
3. Log in to CONN
SQL > conn sys (username) / manager (password) @ orcl (instance name) as sysdba; connects to the database
SQL > conn system/2008Bc1223@orcl as sysdba
4. RMAN login (right mouse button CMD.EXE to "login as administrator" without semicolon)
Rman > rman target sys/2008Bc1223@orcl
=
III. Set up automatic archiving for Oracle
1. Connect to the database as sqlplus system/123@orcl as sysdba / / administrator
2. SQL > archive log list; / / View the current archive mode, whether it is archived or non-archived
3. SQL > shutdown immediate; / / close the database
4. SQL > startup mount; / / start the database to mount status
5. SQL > alter database archivelog/noarchivelog; / / enable / disable automatic archiving mode
6. SQL > Alter system archive log current; / / switch the current log and archive it automatically
7. SQL > alter database open; / / start the database, open the database and load it
8. SQL > Select status from vault instance; / / check whether the database is open-open
Note: Select instance_name from vault instance; / / check the oracle database name, that is, SID_NAME = ORCL
=
IV. Check several statuses of ORACLE database
SQL > show parameter db_name; / / View oracle database name command orcl
SQL > select status from vault instance; / / View the status of oracle's current database open
SQL > show parameter db_recover; / / View oracle to view the information of the flashback recovery area (default: the archive log is stored in the flashback recovery area)
SQL > select name from vault datafile; / / query database DBF file location
SQL > select open_mode from vault database; / / query database status, open and writable mode open/write
SQL > select file#,name,status,enabled,checkpoint_change# from vault datafile; / / query the status of data files
SQL > select sum (bytes) / 1024 as GB from 1024 bytes; / / query the size of the data file (tablespace)
SQL > select sum (bytes) / 1024 as GB from dba_segments; / / query valid data size
SQL > select * from version; / / query database version information
SQL > select instance_name from instance; / / query the name of the current database instance
SQL > select * from global_name; / / query global database name
SQL > select name from vault datafile; / / query the data file location
=
SQL > show parameter db_recover; / / View the path and capacity of the flashback area of oracle database
SQL > alter system set db_recovery_file_dest_size=50G; / / change the archive directory capacity target to 50G, with a default of 4G
SQL > alter system set log_archive_dest_1='location=c:\ archivelog'; / / change the archive path, and restart the database will take effect. Alter database open
SQL > alter system archive log current; / / generate archive logs manually
SQL > alter system switch logfile; switch the archive log group; use it in conjunction with the following command.
SQL > alter system set log_archive_format='ARC%S_%R.%T_%D.log' scope=spfile; generates archive log files
=
If you can log in to any user, you can know which users are currently available through the following methods
SQL > select distinct owner from all_objects to view the current user
Oracle database new table command:
SQL > sql > create table unary (ItemNO number (2), ItemName varchar2 (20)); create unary table
SQL > sql > drop table unary delete unary delete table command
SQL > select TABLE_NAME from all_tables; to view all the data tables of the current library
=
Data file relocation
1. Offline Alter database datafile'c:\ oracle\ data02.dbf' offline
two。 Copy Host copy c:\ oracle\ data02.dbf c:\ oracle\ oradata\ ora10\ data02.dbf
3. Modify the file name Alter database rename file c:\ oracle\ data02.dbf to c:\ oracle\ oradata\ ora10\ data02.dbf in the control file
4. Synchronize the file header with the database Recover datafile'c:\ oracle\ oradata\ ora10\ data02.dbf'
5. Bring it back online to make it available for use with Alter database datafile'c:\ oracle\ oradata\ ora10\ data02.dbf' online
=
V. RMAN backup and recovery
1. RMAN login (right-click CMD.EXE to "log in as an administrator" without semicolon)
Rman > rman target sys/2008Bc1223@orcl
Or: cmd: > Rman target / nocatalog
Delete obsolete;// delete backup set
Crosscheck backup / / verify backup set
Scheduled tasks:
Start-enter Plan, right-click microsoft
C:\ Users\ Administrator > rman target sys/2008Bc1223@orcl cmdfile='D:\ app\ full.sql' log='D:\ app\ full.log'
-
RMAN > change archivelog all crosscheck; / / when the database cannot be backed up, cross-check to ensure that the backup set is valid
RMAN > delete expired archivelog all; / / Delete expired log expired (expired) when the database cannot be backed up
-
=
If ORACLE fails to start, the database recovery may not be completed, as follows:
RMAN > recover database
RMAN > alter database open resetlogs;// reopen the database
The database is open
SQL > select open_mode from v$database
=
Full backup database specified path:
RMAN > backup incremental level 0 database format'c:\ bakup\ database-%s-%T-%t-%U.
Bak'
=
/ / RMAN, "View backup set information and delete expired and invalid backup sets"
RMAN > List incarnation; / / View Lifecycle
RMAN > list backup; / / View backup set information
RMAN > report obsolete; / / lists expired backup sets
RMAN > delete noprompt obsolete; / / Delete expired backup sets
RMAN > crosscheck backup; / / verify backup set validity
RMAN > delete expired backup; / / Delete invalid backup sets
# RMAN recovers to a specified point in time
Run {
Set until time "to_date ('2017-01-14 21V 1015 00mm hh34:mi:ss')"
Restore database
Recover database
}
Alter database open resetlogs
6. RMAN backup and recovery A: concept: 1. Full backup:
Make a full backup of the backup object
2. Incremental backup:
The data of each backup is only relative to the newly added and modified data since the last backup.
3. Differential backup:
The data of each backup is relative to the data added and modified since the last full backup.
4. Log backup:
Changes made to the database since the last backup.
B: backup: 1. Full backup:
Run {
Allocate channel ch2 device type disk
Backup incremental level 0 database format'd:\ app\ Administrator\ BACKUP\ database-%s-%T-%t-%U-%d.bak'
Sql 'alter system archive log current';\\
Backup archivelog all format'D:\ app\ Administrator\ BACKUP\ archlog-%s-%T-%t-%U-.bak' delete all input
Backup current controlfile format'd:\ app\ Administrator\ BACKUP\ ctrlfile-%s-%T-%t-%U-.bak';\\ backup control file command
Release channel ch2;\\ release channel
Report obsolete
Delete noprompt obsolete
Crosscheck backup
Delete noprompt expired backup
}
2. Differential backup
Run {
Allocate channel ch2 device type disk
Backup incremental level 1 database format'D:\ app\ Administrator\ BACKUP\ database-%s-%T-%t-%U-.bak'
Sql 'alter system archive log current'
Backup archivelog all format'D:\ app\ Administrator\ BACKUP\ archlog-%s-%T-%t-%U-%d.bak' delete all input
Backup current controlfile format'D:\ app\ Administrator\ BACKUP\ ctrlfile-%s-%T-%t-%U-%d.bak'
Release channel ch2
}
3. Incremental backup
Run {
Allocate channel ch2 device type disk
Backup incremental level 1 cumulative database format'D:\ app\ Administrator\ BACKUP\ database-%s-%T-%t-%U-%d.bak'
Sql 'alter system archive log current'
Backup archivelog all format'D:\ app\ Administrator\ BACKUP\ archlog-%s-%T-%t-%U-%d.bak' delete all input
Backup current controlfile format'D:\ app\ Administrator\ BACKUP\ ctrlfile-%s-%T-%t-%U-%d.bak'
Release channel ch2
}
4. Archive log backup
Run {
Allocate channel ch2 device type disk
Sql 'alter system archive log current'
Backup archivelog from time "to_date ('2017-01-14 18 yyyy-mm-dd hh34:mi:ss' 07 yyyy-mm-dd hh34:mi:ss')"
Format'D:\ app\ Administrator\ BACKUP\ archlog-%s-%T-%t-%U-%d.bak' delete all input
Backup current controlfile format'D:\ app\ Administrator\ BACKUP\ ctrlfile-%s-%T-%t-%U-%d.bak'
Release channel ch2
}
7. How to turn on or off the ARCHIVELOG mode of oracle A: enable the archive mode
1. CMD > sqlplus sys/sys@orcl as sysdba
2. SQL > archive log list
3. SQL > shutdown immediate; close the database
4. SQL > startup mount; starts the database to mount status
5. Start the archive mode with SQL > alter database archivelog;
6. SQL > alter database open; start the database
B: turn off archive mode
1. SQL > shutdown immediate; 2, SQL > startup mount
3. SQL > alter database noarchivelog; 4, SQL > alter database open
C: start | shut down the database instance: there are three steps
1. Start the instance, load the database and open the database. STARTUP [nomount | mount | open]
2. Close the database, uninstall the database, and close the Oracle instance. SHUTDOWN [normal | transactional | immediate]
