Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Collection of oracle basic commands

2025-02-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

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]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 260

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report